Solved

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

Posted on 2013-12-03
7
2,038 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
7 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 125 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 125 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 125 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 125 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now