Unable to open connection to Excel: External table is not in the expected format.

Question

Hi Guys,

Im trying to extract rows from excel using Oledb. For some excel files from certain people is throwing error while trying to open the connection. The error says "External table is not in the expected format."

Here is my connection string

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.xls;Extended Properties="Excel 8.0;HDR=NO;IMEX=1"

One important thing which I noted is, everything works fine if I kept the file open. Since the files are received from third party vendors, so there is no option to do a 'Save As'.

Thanks in advance! Manoj

Answer 1

Try to use the following connection string:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\test.xls;Extended Properties=Extended Properties=Excel 12.0;

External table is not in the expected format." typically occurs when trying to use an Excel 2007 file with a connection string that uses: Microsoft.Jet.OLEDB.4.0 and Extended Properties=Excel 8.0

Answer 2

It is possible to stick an xls extension on several formats and have Excel open the file with little complaint. Try opening it in a text editor and checking whether or not it is a simple .csv, for example. This would explain why your connection string would work when the file was open.