Can't read Excel formatted data with OLEDB

  • Thread starter Thread starter Igor Vukomanovic
  • Start date Start date
I

Igor Vukomanovic

Hi,

I'm using ADO.NET and have troubles reading excel formatted data.
leDbDataReader returns DBNull for all the values in the columns in xls
workbook which aren't formatted as "Text"!

I'm using this connection string:

"Provider = Microsoft.Jet.OLEDB.4.0;Data Source = xlsfile;Extended
Properties = Excel 8.0;"

This is the relevant part of the code:
---------
System.Data.OleDb.OleDbConnection excelConn;
System.Data.OleDb.OleDbCommand excelCommand;
System.Data.OleDb.OleDbDataReader excelReader;
object[] values;

excelConn = new System.Data.OleDb.OleDbConnection(excelConnStr);
excelCommand = new System.Data.OleDb.OleDbCommand();
excelConn.Open();
excelCommand.Connection = excelConn;

excelCommand.CommandText = "SELECT * FROM ["+sheetName+"]";
excelReader = excelCommand.ExecuteReader();
while (excelReader.Read())
{
excelReader.GetValues(values);
.....
}
-------------

"values" array is filled correctly with data from all the fields which are
formatted as "Text" in Excel workbook, but all others (I tried Date and
Number) are System.DBNull both in type and in value.

Any ideas ??

Thanks!
 
Igor,

Can you try this


Provider = Microsoft.Jet.OLEDB.4.0;Data Source = xlsfile;Extended
Properties = Excel 8.0;IMEX=1;

Tells the driver to always read "intermixed" data columns as text

As written on
http://www.connectionstrings.com/

At Excel OleDb

I hope this helps?

Cor
 
Yes, that worked! I only had to put the IMEX=1 inside the Extended
Properties property, like this:

Extended Properties = 'Excel 8.0;IMEX=1;'

I have suspected it might have had something to do with the Extended
Properties, but I wasn't able to find the list of those properties anywhere.
People talk about the file named "ADOproperties.doc" which contains the
list, but I couldn't find it.
Why isn't this information more available, I cannot understand :(

Thanks!

Igor
 
¤ Yes, that worked! I only had to put the IMEX=1 inside the Extended
¤ Properties property, like this:
¤
¤ Extended Properties = 'Excel 8.0;IMEX=1;'
¤
¤ I have suspected it might have had something to do with the Extended
¤ Properties, but I wasn't able to find the list of those properties anywhere.
¤ People talk about the file named "ADOproperties.doc" which contains the
¤ list, but I couldn't find it.
¤ Why isn't this information more available, I cannot understand :(
¤

It is. It just happens to date back to the Excel ISAM driver and DAO:

http://support.microsoft.com/default.aspx?scid=kb;en-us;194124


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top