Yesterday’s discovery with reading Excel via ADO and the ODBC Text Driver: numeric values may be wrong if it takes a guess that the column contains date fields.
The geeky detail (found in VB6SP5, Excel 2003, ADO 2.7, Jet Provider 4.0):
The text driver looks at the first few lines (configurable via the MaxScanRows setting or the Rows To Scan field in the DSN configuration box) to figure out what sort of data it is dealing with in each column. If there are numbers, it assumes the columns are numbers.
But if it sees dates, and sees no other data in the next few rows, then it assumes all the data in those columns are dates. If it gets further down to rows containing actual numbers, it still thinks it’s reading dates, and when it tries to convert the number to a date and back again, it causes a rounding error and ends up adding 1 to the final number.
In my case, the solution was to get the spreadsheets to contain zeroes (quite valid for the data being loaded) in the first few rows, instead of blanks. A little tricky, but it remains a good method for getting data out of Excel.
Daniel, have had similar problems. There’s also a bug in the MaxScanRows meaning it doesn’t actually work. Because I don’t control the spreadsheets, my only solution was to *SHUDDER* read every cell in individually as a seperate select statement! That was the only way I could guarantee to read the file correctly.