• Export data from Excel to SQL Server using SQL Bulk Copy

    Posted on March 27, 2012 by in ADO.NET, C#, Dotnet

    We have so many clients who still store or transport data in excel/flat files. When upgrading legacy applications, one of the basic and most important task would be migration of data from these files to SQL Server database. Even though we could achieve this using DTS packages, I like using SQL Bulk Copy class for these purpose. It gives more flexibility and control on the behavior.

    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 SqlBulkCopy object’s WriteToServer method, passing in the data to import

    For our example, I have created a simple excel sheet “Departments.xls” as shown below.
    export data from excel to sql server 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 
    // and on the version of excel you have on your system
    string sourceConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;
                             Data Source=C:\Temp\Departments.xls;
                             Extended Properties=Excel 8.0";
    

    Used ADO.NET OledbConnection , OleDbCommand & OleDbAdapter objects to connect to excel data source 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 DepartmentID, DepartmentName, 
                                        DepartmentCode, IsActive, CreatedDate FROM [Departments$]", 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>";

    Initialized the SQL Bulk Copy object with the above connection string. All that is left to do is to specify the destination table and call WriteToServer function with source data. 

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

    In my test case, order of column mappings between source & destination are same. However just to illustrate the procedure, specified column mappings .

    bulkCopy.ColumnMappings.Add("DepartmentID", "DepartmentID");
    bulkCopy.ColumnMappings.Add("DepartmentName", "DepartmentName");
    bulkCopy.ColumnMappings.Add("DepartmentCode", "DepartmentCode");
    bulkCopy.ColumnMappings.Add("IsActive", "IsActive");
    bulkCopy.ColumnMappings.Add("CreatedDate", "CreatedDate");

    Ran below SQL statements to set up the destination table.

    CREATE TABLE [dbo].[tblDepartments](
     [DepartmentID] [int] NOT NULL,
     [DepartmentName] [varchar](255) NOT NULL,
     [DepartmentCode] [varchar](255) NOT NULL,
     [IsActive] [bit] NOT NULL,
     [CreatedDate] [datetime] NOT NULL
    ) ON [PRIMARY]
    GO

    Complete Example:

    public class Driver
        {
            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 
                // and on the version of excel you have on your system
                string sourceConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;
                                    Data Source=C:\Temp\Departments.xls;
                                    Extended Properties=Excel 8.0";
    
                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 DepartmentID, DepartmentName, 
                                        DepartmentCode, IsActive, 
                                        CreatedDate FROM [Departments$]", 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>";
    
                // Set up the bulk copy object. 
                using (SqlBulkCopy bulkCopy =new SqlBulkCopy(destConnString))
                {
                    bulkCopy.DestinationTableName ="dbo.tblDepartments";
    
                    // Guarantee that columns are mapped correctly by
                    // defining the column mappings for the order.
                    bulkCopy.ColumnMappings.Add("DepartmentID", "DepartmentID");
                    bulkCopy.ColumnMappings.Add("DepartmentName", "DepartmentName");
                    bulkCopy.ColumnMappings.Add("DepartmentCode", "DepartmentCode");
                    bulkCopy.ColumnMappings.Add("IsActive", "IsActive");
                    bulkCopy.ColumnMappings.Add("CreatedDate", "CreatedDate");
    
                    // Write from the source to the destination.
                    bulkCopy.WriteToServer(sourceData);
                }
            }
        }

    Output:
    export data from excel to sql server 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]

    2 Responsesso far.

    1. Mansour says:

      I have an issue while uploading the excel sheet, if the excel column name has dot for example [Val.Type], i got an error saying ” The given ColumnMapping does not match up with any column in the source or destination. “

    Leave a Reply