ACE OLEDB “External table is not in the expected format” with large text in Excel cell

Question

I'm trying to read an .xls file that happens to have a very large text cell (around 8900 chars) using System.Data.OleDb and the Microsoft ACE OLEDB provider. I have no control over the content of the .xls file.

I get the following exception when trying to .Open() the OleDbConnection:

Exception thrown: 'System.Data.OleDb.OleDbException' in System.Data.dll

Additional information: External table is not in the expected format.

I have minimized the .xls file and it seems that the text cell is what is causing the exception. I have MS Office 2010 x86 installed on an x64 OS.

I have tried all of the following, none of which solved the problem:

ACE 12.0 x86
ACE 12.0 x64
ACE 15.0 x32
registry tweak to set TypeGuessRows = 0
connection string IMEX=1
connection string Extended Properties="Excel 8.0;"
connection string Extended Properties="Excel 12.0;"

From my research it seems that the old JET provider used to truncate fields to 255 chars. I can't get ACE to read the file at all without throwing the exception.

The exception suggests that you are trying to do this from a .NET application. If so, then you might get better help if you tag the question with C#, VB.NET, or whatever. That said, I am unable to reproduce your issue from C#. Can you provide a link to a small sample .xls file that will demonstrate the error? — Gord Thompson
In Excel you can try Data tab > From Access > select the excel file > etc. and then check the generated connection string in the properties. Otherwise, you can try some of the ODBC drivers connectionstrings.com/excel-2007-odbc — Slai

Gord Thompson Gord Thompson · Accepted Answer · 2016-11-24T15:59:16

You seem to have encountered an issue with the Access Database Engine ("ACE") handling of older .xls files. I could reproduce the issue using

myConnectionString =
        "Provider=Microsoft.ACE.OLEDB.12.0;" +
        @"Data Source=C:\Users\Public\test\sample.xls;" +
        "Extended Properties=\"Excel 8.0;HDR=YES;\";" +
        "";

but no error occurred when I simply switched to the older "Jet" OLEDB provider (32-bit) ...

myConnectionString =
        "Provider=Microsoft.Jet.OLEDB.4.0;" +
        @"Data Source=C:\Users\Public\test\sample.xls;" +
        "Extended Properties=\"Excel 8.0;HDR=YES;\";" +
        "";

... and it did read all 8927 characters in the "Legal" column (i.e., it did not truncate it to 255 characters).

If you really did need to use the ACE OLEDB provider then I found that saving the .xls file as .xlsx and using

myConnectionString =
        "Provider=Microsoft.ACE.OLEDB.12.0;" +
        @"Data Source=C:\Users\Public\test\sample2.xlsx;" +
        "Extended Properties=\"Excel 12.0;HDR=YES;\";" +
        "";

also worked. (The re-save could presumably be accomplished using COM automation of Excel from within the C# application.)

ACE OLEDB “External table is not in the expected format” with large text in Excel cell

1 Answers