Import Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy
by David Hayden ( Florida ASP.NET C# Developer )
Per another question I received, the last thing I wanted to mention on the subject of Excel is that you can use SqlBulkCopy to transfer Excel Spreadsheet data at high speeds to a SQL Server Database Table. I talked about SqlBulkCopy in a previous ADO.NET Tutorial:
You can view my other posts on Excel for more information about using ADO.NET to read and write to Excel Workbooks and Worksheets:
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.
Source: David Hayden ( Florida ASP.NET C# Developer )
Filed: ADO.NET Tutorials