Reading from Excel: Microsoft.Jet.OLEDB.4.0 error on 64-bit systems

Question

I am reading the contents of an excel sheet in my application using:

OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0"); _myDataSet = new DataSet(); OleDbDataAdapter myCommand = new OleDbDataAdapter(" SELECT * FROM [" + "My Sheet" + "$]", con); myCommand.Fill(_myDataSet); con.Close(); 

This does not work on 64-bit systems with an error:

Microsoft.Jet.OLEDB.4.0 'provider is not registered on the local machine

How to make this work on 64-bit machines?

Answer 1

Microsoft.Jet.OLEDB does not have a 64-bit version, only 32 bits. Compile the application as 32-bit (platform target: x86 in the build option).

Answer 2

Microsoft has released a driver distribution with a 64-bit driver that works for Access and Excel. You can download both 64-bit and 32-bit versions from the MS downloads website. The download page also contains a brief description of what you need to change in the connection string to reference the ACE driver.

In short, you install the distribution with a 64-bit driver, and then change the connection string to something along the lines:

 string connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=""Excel 8.0;IMEX=1"""; 

Answer 3

I don't think this works ... see this related question: OleDB not supported in 64-bit mode?

The problem is that COM / Interop is not designed for 64-bit environments, so it cannot be registered in 64-bit mode.

You can force the .NET application to run in 32-bit mode on a 64-bit machine, which allows you to access the OleDB functions.

Answer 4

Forget it, use these dlls from codeplex that solve this problem. http://exceldatareader.codeplex.com/