How to read an .XLSX (Excel 2007) file using ADO.NET? I am finding "Could not find installable ISAM"-error

Question

I need to work in .net 2.0. So I can't use OpenXML.

This is my source code and I have already Installed AccessDatabaseEngine.exe.

But still getting the exception:

"Could not find installable ISAM".

I have also tried "Extended Properties=Excel 8.0" in the connection string.

static void Main(string[] args)
{
    DataSet dataSet = new DataSet();
    OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|Data Directory|\HSC.xlsx;Extended Properties=Excel 12.0;HDR=YES;");           
    OleDbDataAdapter dataAdapter= new OleDbDataAdapter("select * from [Sheet1$]", connection);
    dataAdapter.Fill(dataSet);
}

Solution 1

According to Carl Prothman, that should be

 Extended Properties="Excel 12.0 Xml;

-- http://www.connectionstrings.com/excel-2007

In more detail:

 OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Docs\\Book2.xlsx;Extended Properties='Excel 12.0 xml;HDR=YES;'");           

Note the single quotes.

Solution 2

I prefer to use the Microsoft OpenXML 2.0 SDK for this kind of functionality. It has a really nice interface, and it does not put a demand on having Office installed on the machine reading the XLSX file which is a good thing.

I'm writing this from my mobile, so hard to provide a link, but a Google search should easily find it for you.

Give it a try. I think you will like it.

EDIT

If you have to use .NET 2.0, you can go for using the JET variant of the OleDb instead.

That means you will do something like this to connect:

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + 
                 "Data Source='" + filename + "';" + 
                 "Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;\"";);

Then you can query it like in your example above:

OleDbDataAdapter objAdapter = new OleDbDataAdapter("select * from [Sheet1$]", connection);

Try it! Just note that Jet have some strange logic of deciding if a column is numeric or not. See the following SO questions for details: Problem with using OleDbDataAdapter to fetch data from a Excel sheet

Solution 3

You should make sure that the connection string looks like the following ( even if you are accessing microsoft excel version 10 ->

MyConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;
Data Source='D:\csharp-Excel.xls';Extended Properties='Excel 12.0 Xml;HDR=Yes;'");