External table is not in the expected format.

Question

I am creating a small application to import excel data into my database, when i click the button it crashes with the error

External table is not in the expected format.

I tried googling and changing the codes here and there but the problem still occurs. I tried saving the file as a .xls and when i run the code the page went offline with google chrome's This webpage is not available (Cannot even enter debugging)

Here is my code:

    string strConnection = ConfigurationManager.ConnectionStrings["---"].ConnectionString;

    String excelConnString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0\"", filePath);
    //Create Connection to Excel work book 
    using (OleDbConnection excelConnection = new OleDbConnection(excelConnString))
    {
        //Create OleDbCommand to fetch data from Excel 
        using (OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]", excelConnection))
        {
            excelConnection.Open();
            using (OleDbDataReader dReader = cmd.ExecuteReader())
            {
                using (SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
                {
                    //Give your Destination table name 
                    sqlBulk.DestinationTableName = "TableName";
                    sqlBulk.WriteToServer(dReader);
                }
            }
        }
    }

http://stackoverflow.com/questions/1139390/excel-external-table-is-not-in-the-expected-format — Rohit, Mar 08 '15 at 05:06
I have already visited both links, the connection strings in the links are the same as mine which uses ACE.OLEDB but it still doesn't fix my problem — LuxuryWaffles, Mar 08 '15 at 06:02
And I assume you have the Microsoft Access 2010 Runtime installed already! http://www.microsoft.com/en-us/download/details.aspx?id=10910 — Mehrad, Mar 08 '15 at 22:53

score 4 · Accepted Answer · edited Mar 03 '17 at 21:44

Assuming you have done all the preparations including installing Microsoft Access 2010 Runtime to acquire the relevant drivers try the following as I had success with it in the past.

If your input file is of a type *.xlsx trying replacing

Extended Properties="Excel 12.0"

in your connection string with

Extended Properties="Excel 12.0 Xml"

This issue normally happens with the problematic or mismatching format of Excel file so as a rule of thumb the first thing you would want to try is to open Excel and create a new .xlsx file with couple of sample data in it which you typed yourself (rather than copy/paste). You don't need much typing to test your code snippet. Couple of cells with the right info you're expecting will do. This approach eliminates having any problems with the actual Excel file rather than your C# code.

Let us know how you went.

External table is not in the expected format.

1 Answers1

Linked