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

Show quoteHide quote

¤ 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 ~~~ pclem***@ameritech.net
Microsoft MVP (Visual Basic)

Comparing two DataSets with a Merge
Is this possible
does Ado.Net 2.0 have bulk insert functionality?
Object reference not set to an instance
Designing a Data Access Layer (DAL) with remoting in mind