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

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
danielolorenzAsked:
Who is Participating?
 
allanau20Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
In SSMS right-click on your database, then Tasks... > Import Data, then follow the prompts, making sure to select pipe delimeter.
0
 
danielolorenzAuthor Commented:
I need to write the code using C# in Visual Studio.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
dustockCommented:
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
 
dustockCommented:
If you need help with any of the code let me know.
0
 
SAMIR BHOGAYTAFreelancer and IT ConsultantCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.