• SQL Bulk Copy text file (flat text, csv) data to SQL Server

    Posted on April 2, 2012 by in C#, Database, Dotnet, SQL Server

    In my previous blogs, I have outlined about copying data from one sql server database to other and from a excel file to sql server database. In this article, I would be discussing about importing data from a flat file (with headers and delimited data) to SQL Server.

    Overview
    SqlBulkCopy is a class that was added to the .NET Framework version 2.0 (in the System.Data.SqlClient namespace). It provides an efficient means to import data into a Microsoft SQL Server database. The import data can come from virtually any data source, as SqlBulkCopy can import data from a DataReader, DataRow, or DataTable. Once the import data is ready, you create an instance of the SqlBulkCopy class and specify the destination database and the table to import the data into. You can optionally specify column mappings – indicating what columns in the source data are mapped to what columns in the destination table – along with other options. Finally, to perform the import, call the SqlBulkCopyobject’s WriteToServer method, passing in the data to import.

    For our example, I have created a simple text file “Products.txt” as shown below.

    sql bulk copy text file 1

    Created a connection string as shown below using Microsoft.Jet.OLEDB.4.0 provider in order to connect to this excel sheet. 

    //connection string changes depending on the operation system you are running 
    string sourceConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;
                                         Data Source=C:\Temp\;
                                         Extended Properties=text;";

    Used ADO.NET OledbConnection , OleDbCommand & OleDbAdapter objects to connect to fetch data from text file as shown below: 

    DataTable sourceData = new DataTable();
    using (OleDbConnection conn =
               new OleDbConnection(sourceConnString))
    {
        
        conn.Open();
    
        // Get the data from the source table as a SqlDataReader.
        OleDbCommand command = new OleDbCommand(
                            @"SELECT * from Products.txt", conn);
    
        OleDbDataAdapter adapter = new OleDbDataAdapter(command);
        adapter.Fill(sourceData);
    
        conn.Close();
    }  
    

    Now that we have our source data, we could move onto destination table.  Used the following connection string to connect to the destination SQL Server database.

    string destConnString = @"Password=testuser;Persist Security Info=True;
                              User ID=testuser;Initial Catalog=TEST;
                              Data Source=<SERVER>\SQLEXPRESS";

    Setup the SQL Bulk Copy object with the above connection string. All we have to do is to specify the destination table and call WriteToServer function with source data. 

    using (SqlBulkCopy bulkCopy =new SqlBulkCopy(destConnString))
    {
    bulkCopy.DestinationTableName ="dbo.tblProduct";
    // Write from the source to the destination.
    bulkCopy.WriteToServer(sourceData);
    }
    

    You could also specify column mappings if the order of columns in your excel sheet is different in your destination table.

    bulkCopy.ColumnMappings.Add("Name", "Name");
    bulkCopy.ColumnMappings.Add("ProductNumber", "ProductNumber");
    bulkCopy.ColumnMappings.Add("Price", "ListPrice");
    

    I have created a tblProduct table and loaded it with some test data. If you would like to use the same table, you could use below scripts.

    CREATE TABLE [dbo].[tblProduct](
     [ProductID] [int] IDENTITY(1,1) NOT NULL,
     [Name] [varchar](255) NOT NULL,
     [ProductNumber] [varchar](255) NOT NULL,
     [ListPrice] [numeric](9, 2) NOT NULL,
     CONSTRAINT [PK_tblProduct_ProductID] PRIMARY KEY CLUSTERED 
    (
     [ProductID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
              ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    DECLARE @COUNTER INT, @productname VARCHAR(255), 
     @productnumber VARCHAR(255), @listprice DECIMAL
    SET @COUNTER = 0
    SET @productname = 'product name '
    SET @productnumber = 'product number '
    SET @listprice = 500
    WHILE(@COUNTER <= 20)
    BEGIN
     SET @listprice= @listprice + 100
     INSERT INTO [dbo].[tblProduct]
         VALUES
               (@productname + convert(VARCHAR, @COUNTER)
               ,@productnumber + convert(VARCHAR, @COUNTER)
               ,@listprice)
     SET @COUNTER = @COUNTER + 1           
    END

    Complete Example:

    public class CsvFileReader
    {
        public static void Main(string[] args)
        {
            DataTable data = RetrieveSourceData();            
            CopyData(data);
        }
    
        public static DataTable RetrieveSourceData()
        {
            //connection string changes depending on the operation 
      //system you are running 
            string sourceConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;
                                            Data Source=C:\Temp\;
                                            Extended Properties=text;";
            DataTable sourceData = new DataTable();
            using (OleDbConnection conn =
                       new OleDbConnection(sourceConnString))
            {
                conn.Open();
                // Get the data from the source table as a SqlDataReader.
                OleDbCommand command = new OleDbCommand(
                                    @"SELECT * from Products.txt", conn);
    
                OleDbDataAdapter adapter = new OleDbDataAdapter(command);
                adapter.Fill(sourceData);
                conn.Close();
            }
            return sourceData;
        }
    
        public static void CopyData(DataTable sourceData)
        {
            string destConnString = @"Password=testuser; 
        Persist Security Info=True;
                                User ID=testuser;Initial Catalog=TEST;
                                Data Source=<SERVER>\SQLEXPRESS";
            // Set up the bulk copy object. 
            using (SqlBulkCopy bulkCopy =
                              new SqlBulkCopy(destConnString))
            {
                bulkCopy.DestinationTableName = "dbo.tblProduct";
                // Guarantee that columns are mapped correctly by
                // defining the column mappings for the order.
                bulkCopy.ColumnMappings.Add("Name", "Name");
                bulkCopy.ColumnMappings.Add("ProductNumber", "ProductNumber");
                bulkCopy.ColumnMappings.Add("Price", "ListPrice");
                // Write from the source to the destination.
                bulkCopy.WriteToServer(sourceData);
            }
        }
    }

    Output:

    sql bulk copy text file 2

    Be Sociable, Share!
      Post Tagged with , ,

    Written by

    Software architect with over 10 years of proven experience in designing & developing n-tier and web based software applications, for Finance, Telecommunication, Manufacturing, Internet and other Commercial industries. He believes that success depends on one's ability to integrate multiple technologies to solve a simple as well as complicated problem.

    View all articles by

    Email : [email protected]

    One Responseso far.

    1. Matt says:

      Thank you Venu for the article. Can you advise for or against this method when the source text files are very large? For example, I have delimited files that are 20 to 40GB each that I have to regularly import to SQL Server. The table schemas of the text files regularly change (not relational tables) and I want to build an application that will import these files to SQL Server rather than going through the non-dynamic process of building SSIS packages with source and destination connection managers.
      Do you think problems would arise from loading large file 40GB files in to memory as it appears this step does:

      OleDbCommand command = new OleDbCommand(
      @”SELECT * from Products.txt”, conn);

      Forgive me if my concern is completely incorrect and thank you for any advise.

    Leave a Reply