?
Solved

How Do You Load a Pipe Delimited Text File into a SQL Server Table?

Posted on 2013-12-03
7
Medium Priority
?
2,608 Views
Last Modified: 2013-12-04
I have a pipe delimited text file and I need to load it into a SQL Server table.  How can this be done?

Thanks,

Dan
0
Comment
Question by:danielolorenz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 500 total points
ID: 39692920
In SSMS right-click on your database, then Tasks... > Import Data, then follow the prompts, making sure to select pipe delimeter.
0
 

Author Comment

by:danielolorenz
ID: 39692996
I need to write the code using C# in Visual Studio.
0
 
LVL 9

Assisted Solution

by:dustock
dustock earned 500 total points
ID: 39693037
You can read in the text file and use the split function on each line to get each item in a list.  Then use a loop to go through each import that data into the database.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 9

Expert Comment

by:dustock
ID: 39693178
If you need help with any of the code let me know.
0
 
LVL 5

Accepted Solution

by:
allanau20 earned 500 total points
ID: 39694286
Putting bits and pieces of things I know; here's a working solution for you.

Sorry the solution is not in vb.net, but it should be easy to convert from C# with the Experts here to help you.

Not sure how BIG your pipe file is, and making a call to the DB for each record might not be a good solution, so with FernandoSoto idea I've used this:

SqlBulkCopy for Generic List<T> (useful for Entity Framework & NHibernate)

For the code to work you'll need to use EntityFramework; my example is with 4.1. It should work with 5.0.

You'll need to create this DomainClass (I just put it in a file called Data.cs):
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel.DataAnnotations;

namespace Misellaneous.DomainClass
{
    public class Data
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

}

Open in new window

Here's a console app using Split method and List<T>:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Entity;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using Misellaneous.DomainClass;

namespace Misellaneous
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                LoadFileIntoDB();
            }
            catch (System.Exception excpt)
            {
                Console.WriteLine(excpt.Message);
            }
        }

        static void LoadFileIntoDB()
        {
            //GET FILE
            string[] lines = System.IO.File.ReadAllLines(@"C:\SomeWhere\SomeFile.txt");

            List<Data> MyData = new List<Data>();

            // READ THROUGH FILE  FOR EACH LINE
            for (int i = 0; i < lines.Length; ++i)
            {
                string[] arr = lines[i].Split('|');
                //ADD TO COLLECTION
                MyData.Add(new Data { FirstName = arr[0], LastName = arr[1] });
            }

            BulkInsertTable(MyData);
        }

        private static void BulkInsertTable<T>(IList<T> list)
        {
            //THIS METHOD USES THE SQLBULKCOPY TO UPDATE INSERT INTO THE DATABASE
            string strconnection = @"Data Source=.\localhost;Initial Catalog=Contorso;Integrated Security=SSPI" ;
            string strtableName = "Pipe_Tbl"; 

            try
            {
                using (var bulkCopy = new SqlBulkCopy(strconnection))
                {
                    bulkCopy.BatchSize = list.Count;
                    bulkCopy.DestinationTableName = strtableName;

                    var table = new DataTable();
                    var props = TypeDescriptor.GetProperties(typeof(T))
                        //Dirty hack to make sure we only have system data types 
                        //i.e. filter out the relationships/collections
                                               .Cast<PropertyDescriptor>()
                                               .Where(propertyInfo => propertyInfo.PropertyType.Namespace.Equals("System"))
                                               .ToArray();

                    foreach (var propertyInfo in props)
                    {
                        bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
                        table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType);
                    }

                    var values = new object[props.Length];
                    foreach (var item in list)
                    {
                        for (var i = 0; i < values.Length; i++)
                        {
                            values[i] = props[i].GetValue(item);
                        }
                        table.Rows.Add(values);
                    }
                    bulkCopy.WriteToServer(table);
                }
            }
            catch (System.Exception ex)
            {
                string errmsg = ex.Message;   
            }
        }
    }
}

Open in new window

0
 
LVL 11

Assisted Solution

by:SAMIR BHOGAYTA
SAMIR BHOGAYTA earned 500 total points
ID: 39694637
hi..
You could use the Import Data feature by right mouse clicking the database, and then clicking Tasks then Import Data. This will give you a wizard which you can specify the delimiters etc. for your file and preview the output before you've inserted any data.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question