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.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
XLSX format not just renaming it. You can use
Online tools to convert