Bulk Insert to load text data file to SQl Server with Format File


You can use BULK INSERT command to load data into Sql Server like BCP command.
The simple form T-SQL sytax is:
 
BULK INSERT dbo.yourtable
FROM ‘C:\myData.txt’
 
You can find detailed BULK INSERT syntax from this link:
http://msdn.microsoft.com/en-us/library/ms188365.aspx
 
You can use a Format File to control your table columns to match up with your data file columns. You can use this bcp command to generate a format file for your table:
Format file in XML format:

bcp mydb..Book1SkipOneColumn format nul f Default.xml c x T

Format file in non-XML format:

bcp mydb..Book1 format nul f Default2.fmt c T

You can find more information about format file here: http://technet.microsoft.com/en-us/library/ms190393.aspx

By using a format file, you can skip a table column or skip a data column or mapping columns between data file and the table. You can find some sample from this link: http://technet.microsoft.com/en-us/library/ms190396.aspx

It seems the syntax in the sample will not work with file extension .csv file but I need to get a confirmation on this later.

I have included a sample ASP.NET page through a button click to upload a text data file to SQL Server 2005:

protected void Button2_Click(object sender, EventArgs e)

{

string ConnectionString = ConfigurationManager.ConnectionStrings["forumConnectionString"].ToString();

SqlConnection con = new SqlConnection(ConnectionString);

using (SqlCommand cmdInsert = new SqlCommand(@"BULK INSERT myTestSkipCol FROM ‘C:\datatest\Book1Noheader2.txt’ WITH (FORMATFILE = ‘C:\datatest\Default2.fmt’)", con))

{

con.Open();

cmdInsert.ExecuteNonQuery();

con.Close();

}

}

Another one without format file:

protected void Button1_Click(object sender, EventArgs e)

{

string ConnectionString = ConfigurationManager.ConnectionStrings["test_for_forumConnectionString"].ToString();

SqlConnection con = new SqlConnection(ConnectionString);

using (SqlCommand cmdInsert = new SqlCommand(@"BULK INSERT Book1 FROM ‘C:\datatest\Book1.csv’ WITH (FIRSTROW = 2, FIELDTERMINATOR = ‘,’, ROWTERMINATOR = ‘\n’)", con))

{

con.Open();

cmdInsert.ExecuteNonQuery();

con.Close();

}

}

Advertisements

One Comment on “Bulk Insert to load text data file to SQl Server with Format File”

  1. Uday Satardekar says:

    Hi I AM UDAY

    I AM INSERTING BULK DATA USING XML IN SQL SERVER 2005.i AM USING C#.NET.

    fOR SINGLE TABLE IT WORKS NICE BUT MY PROBLEM IS.I AM INSERTING IN TWO TABLES WITH RELATIONSHIP.MY TABLES ARE COMPANY AND EMAIL.

    AND I AM TRYING TO SAVE COMPANY ID IN EMAIL TABLE.BUT IT RETURN ONLY LAST ID WHEN I AM INSERTING USING XML.HOW I CAN FIND @@IDENTITY FOR EACH ROW DURING XML INSERT.

    set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go

    ALTER PROCEDURE [dbo].[insertDataUsngXml] @XML_TRANSACTIONDETAILS XML

    as DECLARE @i_Doc INT, @DataID int

    BEGIN transaction EXEC SP_XML_PREPAREDOCUMENT @i_Doc OUTPUT,@XML_TRANSACTIONDETAILS INSERT INTO tempdatainfo ( companyname,website,country,contactperson,telphone,mobile,fax,region,status,username,date,category,urlorcatalog ) SELECT companyname,website,country,contactperson,telphone,mobile,fax,region,status,username,date,category,urlorcatalog

    FROM OPENXML(@i_Doc,N’master/TRANSACTION’,2)

    WITH
    (
    companyname varchar(100),
    website varchar(100),
    country varchar(100),
    contactperson varchar(100),
    telphone varchar(100),
    mobile varchar(100),
    fax varchar(100),
    region varchar(100),
    status varchar(100),
    username varchar(100),
    date datetime,
    category varchar(100),
    urlorcatalog varchar(100),
    email varchar(100),
    email1 varchar(100)

    )

    SELECT @DataID=@@IDENTITY

    IF @@Error0 GOTO PROBLEM

    insert into tempemailinfo
    (
    DataID,email,category,status,region,username,date

    )
    SELECT
    @DataID, email,category,status,region,username,date

    FROM OPENXML(@i_Doc,N’master/TRANSACTION’,2)

    WITH
    (

    status varchar(100),
    username varchar(100),
    date datetime,
    category varchar(100),
    urlorcatalog varchar(100),
    email varchar(100),
    email1 varchar(100),
    region varchar(100)

    )

    IF @@Error 0 GOTO PROBLEM
    COMMIT TRANSACTION RETURN 0

    PROBLEM:
    ROLLBACK TRANSACTION
    RETURN 1
    THANKS.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s