Reading Excel via ADO.Net: IMEX=1 ignored when HDR=Yes

[Fred Morrison]

I noticed that when I tried to use ADO.Net to read an Excel workbook
(actually a Named Range called Data) that includes headers (thus HDR=Yes), I
notice that a purely numeric value in the first column is treated as a
Double, despite being told (via IMEX=1) to treat all columns as Text.

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;"

 

[Paul Clement]

¤ I noticed that when I tried to use ADO.Net to read an Excel workbook
¤ (actually a Named Range called Data) that includes headers (thus HDR=Yes), I
¤ notice that a purely numeric value in the first column is treated as a
¤ Double, despite being told (via IMEX=1) to treat all columns as Text.
¤
¤ 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;"
¤

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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Fred Morrison]

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

 

[Paul Clement]

¤ 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.


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

 

[onedaywhen]

Check the following registry settings for the *machine*:

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.