Is this a bug? Broken As Designed (B.A.D.)? Or am I not interpreting the
meaning of IMEX=1 correctly (W.A.D.)?
My only work-around was to open up the Excel workbook, put an 'X' in front
of the number in the first nine rows, add code to strip it back out
(stringVar.Remove(0,1)), which seemed to fool ADO.Net into treating the
column as pure Text instead of a Double.
A portion of the connect string: Extended Properties="IMEX=1;HDR=Yes;"
¤ I noticed that when I tried to use ADO.Net to read an Excel workbook
¤
Setting IMEX=1 will not make the data type of the column Text unless you have a mixed mode
(alphanumeric) value in the first eight rows.
If the first eight rows (TypeGuessRows=8 in the Registry) are numeric then the column data type will
be numeric.
Paul ~~~ pcle...@ameritech.net
Microsoft MVP (Visual Basic)
For now, it's ADO.Not
"Paul Clement" <UseAdddressA...@swspectrum.com> wrote in message
news:78pqvv0rmossa1opn...@4ax.com...
¤ Thanks. I guess I'll just have to hope that Microsoft will provide
¤ programmers full control of column datatype interpretation of Excel "tables"
¤ in a future release of ADO.Net.
¤
¤ For now, it's ADO.Not
Determining the data type for the column is a function of the Jet database engine and the Excel ISAM
driver, not ADO.NET.
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes
TypeGuessRows: setting the value to 0 (zero) will force ADO to scan
all column values before choosing the appropriate data type.
ImportMixedTypes: should be set to value 'Text' i.e. import mixed-type
columns as text:
Using IMEX=1 in the connection string (as you have done) ensures the
registry setting is applied.
--
Paul Clement <UseAdddressA...@swspectrum.com> wrote in message news:<9rj500pb180k2fhtf...@4ax.com>...