I have code
string conn = @"Provider=Microsoft.ACE.OLEDB.12.0;DSN=Excel Files;DBQ=" + strSheetPath + @";DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;PageTimeout=5;";
//string conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strSheetPath +";Extended Properties=Excel 12.0 Xml;HDR=YES;IMEX=1";
string destinationConnectionString = ConfigurationManager.ConnectionStrings["KGD6DBConnectionString"].ConnectionString;
string query = "Select * From [Sheet1$]";
using (var myConnection = new OleDbConnection(conn))
using (var destinationConnection = new SqlConnection(destinationConnectionString))
using (var bulkCopy = new SqlBulkCopy(destinationConnection))
{
//Map first column in source to second column in sql table (skipping the ID column).
//Excel schema[CompanyName,Phone] Table schema[ShipperID, CompanyName, Phone]
bulkCopy.ColumnMappings.Add(0, 1);
bulkCopy.ColumnMappings.Add(1, 2);
bulkCopy.ColumnMappings.Add(2, 3);
bulkCopy.ColumnMappings.Add(3, 4);
bulkCopy.ColumnMappings.Add(4, 5);
bulkCopy.ColumnMappings.Add(5, 6);
bulkCopy.ColumnMappings.Add(6, 7);
bulkCopy.ColumnMappings.Add(7, 8);
bulkCopy.ColumnMappings.Add(8, 9);
bulkCopy.ColumnMappings.Add(9, 10);
bulkCopy.ColumnMappings.Add(10, 11);
bulkCopy.ColumnMappings.Add(11, 12);
bulkCopy.ColumnMappings.Add(12, 13);
bulkCopy.ColumnMappings.Add(13, 14);
bulkCopy.ColumnMappings.Add(14, 15);
bulkCopy.ColumnMappings.Add(15, 16);
bulkCopy.ColumnMappings.Add(16, 17);
bulkCopy.ColumnMappings.Add(17, 18);
bulkCopy.ColumnMappings.Add(18, 19);
bulkCopy.ColumnMappings.Add(19, 20);
bulkCopy.ColumnMappings.Add(20, 21);
bulkCopy.ColumnMappings.Add(21, 22);
bulkCopy.ColumnMappings.Add(22, 23);
bulkCopy.ColumnMappings.Add(23, 24);
bulkCopy.DestinationTableName = "dbo.sampleInventory";
using (var myCommand = new OleDbCommand(query, myConnection))
{
myConnection.Open();
destinationConnection.Open();
var myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
bulkCopy.WriteToServer(myReader);
}
}
}
return (new DataTable());
getting error on connection opening line.
Error:
Could not find installable ISAM.
