Microsoft.ACE.OLEDB.12.0 'External table is not in the expected format' error on Windows 2003 server

Question

_{Wednesday, September 26, 2012 9:41 AM}

Hi,
in a web page I'm developing I must read an xlsx file using the .NET Framework Data Provider for OLE DB. The file has just one column and a few rows, and the first row is the header.
The connection string reads like this:

strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=test.xlsx;Extended Properties=\Excel 12.0 Xml;HDR=Yes;IMEX=1\";

There is no problem when executing the code on a Vista or Win7 PC, but when deploying the web site on a Windows 2003 server SP2 and trying to open the same xlsx file, the code fails on opening the connection with an 'External table is not in the expected format' error. The same on a Windows 2003 server R2.

I built a test console program that just opens the connection to the file to exclude any possible issue with IIS and run the test program as administrator of the Win 2003 server, but the problem remains.

The Microsoft Access Database Engine 2010 is correctly installed on the server, and I can successfully read a .xls file using the same provider with "Extended Properties=Excel 8.0". I also installed the engine's SP1 and the KB2553281 and KB2553310 updates, but to no avail.

The xlsx file seems ok, I created it with Excel 2010 with no special formatting or unusual data types.

All systems are 32-bit. The web site and the test program are written in c#. The framework is .NET 3.5.

I've tried any combination of extended properties (and the code correctly fails when they don't match the file format), now I'm at my wits'end.

Any ideas?
Thanks

All replies (16)

_{Friday, September 28, 2012 6:40 PM ✅Answered}

SOLVED!!!!

Just to compare what was happening on my development machine and on the test server, I ran SysInternal's Process Monitor on both machines while running my test app. What I noticed was that, after reading the xlsx file, the app queries the registry for the Msxml2.SAXXMLReader.6.0 component: guess what, it was installed on my dev machine (probably installed as a Visual Studio pre-requisite), but not on the test server. On my dev machine, the app went on reading the msxml6.dll that allowed the provider to correctly loading the xml inside the xlsx file, while on the test server the component could not be found so that, being unable to read the xml, the provider failed with this... let's say shamefully misleading error 'External table is not in the expected format'.

I installed the MSXML 6.0 package on the Win 2003 server and that did the trick.

Hope this will be helpful for other people stumbling on this problem, and also hope MS will at least document this in a KB article.

So long pals :)

_{Wednesday, September 26, 2012 9:25 PM}

Did you try copying the file from the Windows 2003 server back to a machine where it worked previously, just to make sure that the file didn't become corrupted when it was copied?

Paul ~~~~ Microsoft MVP (Visual Basic)

_{Thursday, September 27, 2012 7:18 AM}

Hi Paul,

thanks for replying.

I tried that, and also opened the xlsx file on the server with the Office viewer, and run the test program on my development machine having it point directly to the file on the server in a shared folder without error. The file seems ok.

_{Thursday, September 27, 2012 11:35 AM | 2 votes}

Hello, you might want to try the following modified version of your connection string which exclude xml in extended properties as this is not needed for a normal xlsx file.

strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=test.xlsx;Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";

Also, not sure about the backslashes in extended properties as the following

OleDb.OleDbConnectionStringBuilder Builder = new OleDb.OleDbConnectionStringBuilder();Builder.DataSource = "test.xlsx";Builder.Provider = "Microsoft.ACE.OLEDB.12.0";Builder.Add("Extended Properties", "Excel 12.0;HDR=Yes;IMEX=1");Console.WriteLine(Builder.ConnectionString);

Creates

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=test.xlsx;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"

These things are easy to try, whether the make a difference I don't know but worth trying.

KSG

_{Thursday, September 27, 2012 12:18 PM}

Hi Kevin,

already tried that. No change.

As for the backslashes, they are needed to escape the quotes inside the connection string (I copied the instruction above straight from my C# code). The resulting connection string is identical to what the connection string builder returns, and as a matter of fact the same code works on my dev machines which are not Win 2003.

Thanks anyhow

_{Thursday, September 27, 2012 12:41 PM}

Does the account the web app runs under have full permissions to the folder where the Excel file is located? I'm assuming it's a local path (on the machine).

Paul ~~~~ Microsoft MVP (Visual Basic)

_{Thursday, September 27, 2012 12:45 PM}

Yes. As I mentioned in my post, I also wrote a test console application that I run from a command prompt as a local admin.
I'm not using the web app anymore for my tests. The xlsx file is in the same folder as the exe.

_{Thursday, September 27, 2012 1:12 PM}

OK, so if you're testing with a console app and running as Administrator then permissions shouldn't be an issue. Try removing the HDR and IMEX arguments from the connection string - just to see if you can open the file.

Paul ~~~~ Microsoft MVP (Visual Basic)

_{Friday, September 28, 2012 7:27 AM}

Already done that, no change. I also opened the file using a FileStream and read a few bytes without any problem.

_{Friday, September 28, 2012 7:23 PM}

Yeah, that's a new one on me. I have to file that away. Thanks for posting your solution.

Paul ~~~~ Microsoft MVP (Visual Basic)

_{Thursday, October 4, 2012 2:17 PM}

I faced the same problem. It was saying that "external table is not in the expected format". I found that it's a problem with our security software. It's always change the excel sheet in encrypted format. So Microsoft.ACE.OLEDB.12.0 can't understand the input excel sheet.

_{Wednesday, November 28, 2012 9:15 AM}

Hi,

   I am facing same type of problem solution.

Just go to 

1) Click on File.

2) Click on Save As .

3) Show a Box As follow.

4) Change File Name & Save As Type to Excel 97-2003 Workbook (*.xls) & click save then it work.

Also this type of problem occurs in .csv apply same procedure & solve it.

_{Thursday, December 20, 2012 10:52 AM | 1 vote}

This is the best solution I've found, for I have the same problem.

Our server now has the lovely MSXML6 installed and registry looks good.

But sometimes the error still occurs with .xlsx files. We have customers who upload their xlsx files, so it may be certain ones that cause the issue, although I haven't been able to determine what is common between them.

The server is a 64bit Windows 2003 R2 server, so the MSXML6 I installed was for 64bit. We have a second 64bit server and I can replicate the issue on that one too.

My local 32bit PC loads the same xlsx files no problem.

Of course I can re-save the file as xls and load fine, but would like it to work for our customers first time!

Some xlsx files work fine on the 64bit server, so I will continue to try and discover what makes these other ones 'special'

Any help or ideas gratefully received!

_{Sunday, July 21, 2013 3:04 PM}

Thanks. Changing my connection string helped. I was using Microsoft Jet OLEDB.4.0 as my connection string and it had the VERY ODD feature that it worked... as long as the file was open in Excel. If I closed the file, excel through the "External Table is not ..." error.

_{Thursday, June 2, 2016 9:06 AM}

Thanks. It works.

_{Monday, November 27, 2017 11:06 AM}

Hi <g class="gr_ gr_18 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" data-gr-id="18" id="18">kareninstructor</g>,

Thanks for the solution, it works.