3 Replies - 68807 Views - Last Post: 29 January 2009 - 02:12 AM Rate Topic: -----

#1 metazoan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 27-January 09

Read Excel using ADO.Net - connection string issues

Posted 27 January 2009 - 07:38 PM

I wrote a windows form application in C# that uses ADO.Net to read from an Excel file. I receive xls files but have to manually save them as xlsx files because I can't get the program to read xls files (I get "External table is not in the expected format"). As an aside, for broadest .net compatibility, I set each of the Projects (which create assemblies/dlls) for the application to .net framework 2.0.

I have 2 questions:
1) How can I get my program to read xls files when Excel 2007 is installed?
2) How can I support environments where Excel 2002 and Excel 2003 are installed - I assume different connection strings and have the users ONLY open xls files?

Code Snippet:
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = "whatever"; // see bottom of post
connection.Open();


Connection strings I've tried:

// The first one worked for xlsx files w/ Excel 2007 installed (with the name of the file having the xlsx extension, of course):
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myFile.xls;Extended Properties=""Excel 12.0;IMEX=1;HDR=YES;""
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myFile.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;""
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myFile.xls;Extended Properties=""Excel 5.0;HDR=Yes;IMEX=1;""


Is This A Good Question/Topic? 0
  • +

Replies To: Read Excel using ADO.Net - connection string issues

#2 scalt  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 63
  • View blog
  • Posts: 344
  • Joined: 22-November 07

Re: Read Excel using ADO.Net - connection string issues

Posted 27 January 2009 - 08:20 PM

A string I use for Office 2k3:
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FileName(0) & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO;IMEX=1" & """"

Or for Office 2k7 (which also reads 2k3 but a little slower - I'm talking +5sec for 200mb file over network)

"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & FileName(0) & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1"";"

Keep in mind that both of these string DON'T use the first row of the file as headers (HDR=NO) and forceably read all values in as strings (IMEX=1).
Was This Post Helpful? 0
  • +
  • -

#3 metazoan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 27-January 09

Re: Read Excel using ADO.Net - connection string issues

Posted 28 January 2009 - 09:37 AM

View Postscalt, on 27 Jan, 2009 - 07:20 PM, said:

A string I use for Office 2k3:
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FileName(0) & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO;IMEX=1" & """"

Or for Office 2k7 (which also reads 2k3 but a little slower - I'm talking +5sec for 200mb file over network)

"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & FileName(0) & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1"";"

Keep in mind that both of these string DON'T use the first row of the file as headers (HDR=NO) and forceably read all values in as strings (IMEX=1).


Thanks for the reply. I can't get it to work - I've tried every suggestion so far. I'm now focusing solely on opening an xls file (before, I saved the xls as an xlsx using Excel 2007 and that's working). I even saved the xls file w/ Excel 2007 as Excel 97 - 2003 Workbook. In addition, I opened the Excel file in notepad to see if it's in HTML format and it's not.

CODE SNIPPET:

System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString);
connection.Open();

-or-

DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
connection.Open();


CONNECTION STRINGS AND EXCEPTIONS/ERRORS (Note: The exceptions say that the source is the Microsoft Office Access Database Engine):

// error: Could not find installable ISAM; reason: left out the double-quotes in Extended Properties
Data Source=C:\myFile.xls;Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 12.0;IMEX=1;HDR=YES

// error: External table is not in the expected format
Data Source=C:\myFile.xls;Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties="Excel 12.0;IMEX=1;HDR=YES";

// error: Unspecified error
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myFile.xls;Extended Properties="HTML Import";

// error: External table is not in the expected format
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFile.xls;Extended Properties="Excel 12.0 Xml;IMEX=1;HDR=YES";

// error: External table is not in the expected format
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\

This post has been edited by metazoan: 28 January 2009 - 09:38 AM

Was This Post Helpful? 0
  • +
  • -

#4 itlee  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 55
  • Joined: 16-July 08

Re: Read Excel using ADO.Net - connection string issues

Posted 29 January 2009 - 02:12 AM

This is what I use:

string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myFile.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""";
OleDbConnection connOleDb = new OleDbConnection(connString);



Don't forget to escape the special characters, see the @ symbol at the beginning of the string.

Also take a look at this site: ConnectionStrings.com

Lee.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1