Accessing Excel 2007 file format with OLEDB

Question

I'm currently trying to access an excel 2007 file on a server that doesn't contain Microsoft Office on it. My connectionstring is something like this.

  String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
  "Data Source=" + file_path + ";Extended Properties=Excel 8.0;";

But this is for 2003 format, which works fine. I would like to take advantage of the 2007 xml format. But I'm unable to access the file through the following connection.(The target server doesn't have Office installed on it, not sure if thats the reason.

  String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
 "DataSource=" + file_path + ";HDR=Yes;IMEX=1;Extended Properties=Excel 12.0;";

I'm keep getting the "could not find installable isam." Error.

EDIT I'm using visual studio 2005 and developing in C# if this helps clear anything up.

score 4 · Answer 1 · answered Nov 11 '09 at 15:28

4

You may try with this connection string:

string connectionString = 
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + 
    file_name + 
    ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";

Please note though that this driver is not supported on x64 systems and it won't work.

answered Nov 11 '09 at 15:28

Darin Dimitrov

1,023,142
271
3,287
2,928

And, if you don't have Excel installed, you can get the AccessDatabaseEngine download from MS at http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en. That will still allow you to use ODBC connections to Excel. – Jeff Siver Nov 12 '09 at 06:20
For x64 systems, you will need to set your project's target platform to x86. – Even Mien Mar 01 '10 at 21:53

score 1 · Answer 2 · answered Nov 19 '09 at 20:33

You should try using the Linq to Excel open source project to query data from Excel spreadsheets. You can use LINQ statements to query the data, and Linq to Excel takes care of making the OLEDB connection and creating the SQL statement.

Here's an example of how easy it is to query data from an Excel spreadsheet

var book = new ExcelQueryFactory("pathToExcelFile");
var rows = from x in book.Worksheet() 
           select new                
           {
             Name = x["Name"],
             BirthDate = x["BirthDate"].Cast<DateTime>()
           };

Checkout the Linq to Excel intro video for more information about the open source project.

score 0 · Answer 3 · answered Nov 13 '09 at 03:33

SpreadsheetGear for .NET will let you open the workbook with no reliance on OleDB, COM Interop or any other API which can create problems on a server.

You can see live ASP.NET samples here and download the free trial here.

Disclaimer: I own SpreadsheetGear LLC

Accessing Excel 2007 file format with OLEDB

3 Answers3