External table is not in the expected format c#

asp.net c# oledbconnection sqlbulkcopy sql-server-2008

Question

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.

Accepted Answer

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.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why