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 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); } } }
0 comments: