I have seen more answers while googling it, but couldn't solved my problem. This is the code I have written to insert excel sheet into sql server table.
string strConnection = ConfigurationManager.ConnectionStrings[0].ToString();
string path = @"D:/Projects/sample.xlsx";
string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\";";
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand("Select [Name], [City], [Address], [Designation] from [Sheet1$]", excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "temp1";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
While executing the line "excelConnection.Open();". I am getting an error "External table is not in the expected format."
And "sample.xlsx" is actually an ods format file. I changed its format as xlsx.
What would be wrong in this code. Please suggest me.
As per the conversation in the comments, you have to convert the ODS
file into XLS
or XLSX
format not just renaming it. You can use Excel
or Online tools
to convert ODS
files.