How to read .XLSX file (Excel 2007) using ADO.NET? I find "Can't find installable ISAM" -error

Question

I need to work in .net 2.0 . Therefore, I can not use OpenXML.

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

But still getting an exception:

"Could not find installable ISAM."

I 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); } 

Answer 1

According to Karl Protman, it should be

  Extended Properties="Excel 12.0 Xml; 

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

More details:

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

Pay attention to single quotes.

Answer 2

I prefer to use the Microsoft OpenXML 2.0 SDK for this kind of function. It has a really good interface, and it does not require Office to be installed on a machine reading an .xlsx file, which is good.

I am writing this from my mobile phone, it is so difficult 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 need to use .NET 2.0, you can use the JET OleDb option OleDb .

This means that 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 request it, as in the example above:

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

Give it a try! Just note that Jet has a weird decision logic whether the column is numeric or not. See the following SO questions for more information: Problem using OleDbDataAdapter to retrieve data from an Excel worksheet

Answer 3

Make sure the connection string looks like this (even if you are accessing Microsoft Excel 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;'");