string ExcelConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + strImportFileName + “;Extended Properties=Excel 8.0;”;

using (OleDbConnection connection = new OleDbConnection(ExcelConnectionString))
{
connection.Open();

OleDbDataAdapter oleda = new OleDbDataAdapter(“Select * FROM [Master Sheet$]”, ExcelConnectionString);
DataTable dtExcelData = new DataTable();
oleda.Fill(dtExcelData);
//Remove the unwanted rows from the Datatable
dtExcelData.Rows.RemoveAt(0);

//Replace the default headers of the datatable with the new named headers
foreach (DataColumn dtColumn in dtExcelData.Columns)
{
dtColumn.ColumnName = dtExcelData.Rows[0].ItemArray[dtExcelData.Columns.IndexOf(dtColumn)].ToString();
}
//Remove the header row from the datatable
dtExcelData.Rows.RemoveAt(0);
SqlBulkCopy bulkCopy = new SqlBulkCopy(“ConnectionString”);

bulkCopy.ColumnMappings.Clear();

bulkCopy.DestinationTableName = “my_sql_table”;
bulkCopy.WriteToServer(dtExcelData);
bulkCopy.Close();
}

Advertisements