Solved

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

Posted on 2013-12-03
7
2,330 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 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

733 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