I am having problems reading in data from an excel spreadsheet using the
OleDbDataAdapter. Sometimes the data will read okay, but most of the time
many of the values are not recognized and read in as dbNull. I have yet
to find a pattern.

I have formatted all the cells in the spreadsheet the same. I have even
copyed values that read in one cell into a cell that would not read
correctly. Result was still dbNull.

Any ideas?

Thanks.

Just check the cell value to see if it equals DBNull.value before processing
the cell.

[quoted text, click to view]

Also, "formatting" the cells in Excel has no effect on data that may or may
not be in that cell.


[quoted text, click to view]

Excel has to "guess" at the datatype of the column, and this problem occurs
mostly in columns with mixed text and numeric data. The brutal behavior of
the driver is only to return data of the "majority" datatype, and to return
NULL for the minority.

The best solution is to force "import mode" by adding the little-known
IMEX=1 argument to the Jet connection string.

Q194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset (mixed
data types)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q194124

(Although the article was written for DAO, the solution remains the same for
ADO.)

-Doug

--
Douglas Laudenschlager
Microsoft SQL Server documentation team
Redmond, Washington, USA

This posting is provided "AS IS" with no warranties, and confers no rights.

[quoted text, click to view]