Excel “External table is not in the expected forma

Question

I'm trying to read an Excel (xlsx) file using the code shown below. I get an "External table is not in the expected format." error unless I have the file already open in Excel. In other words, I have to open the file in Excel first before I can read if from my C# program. The xlsx file is on a share on our network. How can I read the file without having to open it first? Thanks

string sql = "SELECT * FROM [Sheet1$]";
string excelConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathname + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";

using (OleDbDataAdapter adaptor = new OleDbDataAdapter(sql, excelConnection)) {
    DataSet ds = new DataSet();
    adaptor.Fill(ds);
}

Answer 1

I have also seen this error when trying to use complex INDIRECT() formulas on the sheet that is being imported. I noticed this because this was the only difference between two workbooks where one was importing and the other wasn't. Both were 2007+ .XLSX files, and the 12.0 engine was installed.

I confirmed this was the issue by:

• Making a copy of the file (still had the issue, so it wasn't some save-as difference)
• Selecting all cells in the sheet with the Indirect formulas
• Pasting as Values only

and the error disappeared.

Answer 2

This can occur when the workbook is password-protected. There are some workarounds to remove this protection but most of the examples you'll find online are outdated. Either way, the simple solution is to unprotect the workbook manually, otherwise use something like OpenXML to remove the protection programmatically.

Answer 3

ACE has Superceded JET

Ace Supports all Previous versions of Office

This Code works well!

        OleDbConnection MyConnection;
        DataSet DtSet;
        OleDbDataAdapter MyCommand;

        MyConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\\Book.xlsx;Extended Properties=Excel 12.0;");
        MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
        DtSet = new System.Data.DataSet();

        MyCommand.Fill(DtSet);
        dataGridView1.DataSource = DtSet.Tables[0];
        MyConnection.Close();

Answer 4

Instead of OleDb, you could use the Excel Interop and open the worksheet as read-only.

https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.workbooks.open(v=office.15).aspx

Answer 5

This can also be a file that contains images or charts, see this: http://kb.tableausoftware.com/articles/knowledgebase/resolving-error-external-table-is-not-in-expected-format

The recommendation is to save as Excel 2003

Answer 6

If you still have this problem, then check your permissions, I tried many of these suggestions and my concrete problem was that the file that I wanted to process was under source control and the thread had no permissions, I had to change the entire folder permissions and it started to work (I was processing many files in there)... It also matches many suggestions like change the name of the file or check that the file is not loicked by another process.

I hope it helps you.