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.

Answer 1

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.)