Reading .xls (97-2003) files with ACE after oppening them with Excel 2013

1.2k views Asked by At

We have been using Access Database Engine 2010 (V.14.0.4763.1000) for reading Excel (97-2003) .xls files from c# (.Net framework 4.0) with succesful results for a really long time. But now, after opening the files with Excel 2013, and saving them again in the same format (97-2003 .xls) we can't read the files anymore.

It gives us the following error:

External table is not in the expected format.

This doesn't happen when we open and save the files with Excel 2007 or 2010, only with 2013. We use the following connection string for reading the file:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0;HDR=YES

where {0} is replaced by the file name and path. It seems Excel 2013 corrupts the 97-2003 file structure in some way that ACE doesn't understand. Is there any way we can fix this issue?

Just to be clear, the file format we need to read is always .xls (97-2003), we don't want to read .xlsx files (we can do this with no problem), but we need to use (97-2003) for compatibility with other systems.

Edit:

I made a very simple test program to illustrate, you can get it here: https://www.dropbox.com/s/1ftw3emilnbuw6t/ExcelTest.rar

The .rar file contains:

  • ExcelReadingTest.exe (The Program)
  • Program.cs (The source code)
  • SavedFromExcel2007.xls (An example Excel file saved from Excel 2007 in 97-2003 format)
  • SavedFromExcel2013.xls (The same Excel file saved from Excel 2013 in 97-2003 format)

To run the example save all files in c:\temp and type on a cmd console:

C:\>cd c:\temp (Enter)
C:\temp>ExcelReadingTest SavedFromExcel2007.xls (Enter)

You´ll get:

1       Hello
2       World
3       Reading
4       Excel
Press Enter...

And if you type:

C:\temp>ExcelReadingTest SavedFromExcel2013.xls (Enter)

You´ll get this with Access Database Engine 2007 (12.0.6612.1000) installed:

System.Data.OleDb.OleDbException (0x80040E37): The Microsoft Access database eng
ine could not find the object 'Sheet1$'. Make sure the object exists and that yo
u spell its name and the path name correctly. If 'Sheet1$' is not a local object
, check your network connection or contact the server administrator.
   en System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResul
t hr)
   en System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARA
MS dbParams, Object& executeResult)
   en System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   en System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Ob
ject& executeResult)
   en System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behav
ior, String method)
   en System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
   en System.Data.OleDb.OleDbCommand.ExecuteReader()
   en ExcelReadingTest.Program.Main(String[] args)
Press Enter...

Or you´ll get this with Access Database Engine 2010 (14.0.7015.1000) installed:

System.Data.OleDb.OleDbException (0x80004005): External table is not in the expe
cted format.
   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString cons
tr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOpti
ons options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection o
wningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbC
onnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection ow
ningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection ou
terConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at ExcelReadingTest.Program.Main(String[] args) in c:\users\ivanm\documents\v
isual studio 2010\Projects\ExcelReadingTest\ExcelReadingTest\Program.cs:line 41
Press Enter...
0

There are 0 answers