Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

by David Hayden ( Florida ASP.NET C# Developer )

Using SqlBulkCopy to Import Excel Spreadsheet Data into SQL Server

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

Conclusion

SqlBulkCopy will import / export your Excel Spreadsheet information into a SQL Server Database Table at very high speeds.(asp.net excel import or export, C# source code to import excel,import excel into SQL dataBase,import excel)