locked
FAQ Item: How to import data from Excel file to SQL Server database? RRS feed

  • Question

  • How to import data from Excel file to SQL Server database?
    Sunday, June 20, 2010 1:52 PM

Answers

  • First we can use OleDBConnection to connect to Excel file as a data source. After that, we use DbDataReader to access Excel data we want. Then, we can use the SqlBulkCopy to copy the data from the DataReader to the target SQL Server table. All we need are just the following codes,

    // Connection String to Excel Workbook

    string excelConnectionString = @"Provider=Microsoft    .Jet.OLEDB.4.0;Data Source=Book1.xls;Extended    Properties=""Excel 8.0;HDR=YES;""";

     

    // Create Connection to Excel Workbook

    using (OleDbConnection connection =             new OleDbConnection(excelConnectionString))

    {

        OleDbCommand command = new OleDbCommand            ("Select ID,Data FROM [Data$]", connection);

     

        connection.Open();

       

        // Create DbDataReader to Data Worksheet

        using (DbDataReader dr = command.ExecuteReader())

        {

            // SQL Server Connection String

            string sqlConnectionString = "Data Source=.;           Initial Catalog=Test;Integrated Security=True";

     

            // Bulk Copy to SQL Server

            using (SqlBulkCopy bulkCopy =                   new SqlBulkCopy(sqlConnectionString))

            {

                bulkCopy.DestinationTableName = "ExcelData";

                bulkCopy.WriteToServer(dr);

            }

        }

    }

     

    The SqlBulk will make the importing process at highest performance. The idea and codes are from David Hayden’s blog article,

    http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx

     

    Related Threads:

    http://social.msdn.microsoft.com/Forums/en-US/vbide/thread/15e9e292-247c-45dd-8510-6f1325fbc404/

    http://social.msdn.microsoft.com/Forums/en-US/vbinterop/thread/3e9e8441-36be-4f70-826b-51badb9a9bb3/
    • Marked as answer by MSDN FAQ Sunday, June 20, 2010 1:53 PM
    Sunday, June 20, 2010 1:52 PM