Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Open an Excel 2003 spreadsheet with C#. Could not find installable ISAM. Exception

Tags:

c#

excel

xls

isam

I need to pull data from an xls, I also need have the user be able to change the location of the file it will. So an OleDbConnection seemed like a good start, and it was until the first merged cell.

This works for all but the merged cells:

OleDbCommand cmd = new OleDbCommand();
cmd.Connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data 
Source=F:\test.xls;Extended Properties=Excel 8.0;");
cmd.CommandText = "SELECT * FROM [Sheet$]";
cmd.Connection.Open();

I found that this should allow access to the merged cells:

OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\test.xls;Extended Properties=Excel 8.0;HDR=Yes;IMEX=1;");

But then I get a Could not find installable ISAM exception on cmd.conn.open();

I followed the advice here: http://support.microsoft.com/kb/209805

And here: Error: "Could Not Find Installable ISAM"

No luck.

I’m open to other ways of pulling data from the xls. Or even if there was a command I could run on the xls to remove the mirged cells that might work.

like image 498
NitroxDM Avatar asked Mar 13 '09 19:03

NitroxDM


People also ask

Can you use C with Excel?

The Excel C API is the ideal choice when you want to create high-performance worksheet functions by creating XLL add-ins. The C API provides you with the most direct access to worksheet data. XLLs provide Excel with the most direct access to the DLL resources.

How do you change compatibility mode in Excel 2003?

Click File > Info > Check for Issues. Choose Check Compatibility. To check for compatibility automatically from now on, check the Check compatibility when saving this workbook box. Tip: You can also specify the versions of Excel that you want to include when you check for compatibility.

How do I open an old spreadsheet?

Open the file you want to view. Click File > Info > Version history. Select a version to open it in a separate window. If you want to restore a previous version you've opened, select Restore.


2 Answers

I think it's just because you have to enclose the Extended Properties in quotes if you have more than one

OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\test.xls;
Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';");

Or if single quotes don't work (you get the idea)

OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\test.xls;
Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;"";");

While your example doesn't show it, this error can also be caused by spaces in the file path. In which case you would need to wrap the file path in quotes as well.

OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""F:\test.xls"";...
like image 181
DJ. Avatar answered Oct 01 '22 09:10

DJ.


Assuming your system requirements include an installation of Excel, you can use the Excel Object Library

Excel.Sheets sheets = m_Excel.Worksheets;
Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
Excel.Range range = worksheet.get_Range("A1", "E1".ToString());

etc.

See also VSTO

like image 20
configurator Avatar answered Oct 01 '22 10:10

configurator