Solved

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

Posted on 2013-12-03
7
2,140 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

910 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