CODEWITHSUNDEEP
ado.netc#-2.0excel-2007oledbopenxml
user366312
user366312

Reputation: 16908

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

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

Upvotes: 5

Views: 25455

Answers (3)

Deena Fadel Awny
Deena Fadel Awny

Reputation: 11

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;'");

Upvotes: 1

Øyvind Bråthen
Øyvind Bråthen

Reputation: 60694

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

Upvotes: 2

Fionnuala
Fionnuala

Reputation: 91356

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.

Upvotes: 9

Related Questions