Import Excel Spreadsheet Data into SQL Server Database Table

Let's take an Excel Workbook with a worksheet, called Data, that contains 1000 rows of nonsense data broken into 2 columns, ID and Data.

I want to copy this data into a SQL Server Database Table, called ExcelData, with the same schema.

Just a little bit of code transfers the data from the Excel Spreadsheet into the SQL Server Database Table:

// 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);

        }

    }

}