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

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
Thu Jan 08 08:17:59 CST 2004

On Wed, 7 Jan 2004 21:18:42 -0500, "Fred Morrison" <fmorrison@erols.com> wrote:

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

Fred
Sat Jan 10 16:03:52 CST 2004

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" <UseAdddressAtEndofMessage@swspectrum.com> wrote in message
news:78pqvv0rmossa1opng2j108rvs7hee56tm@4ax.com...
> On Wed, 7 Jan 2004 21:18:42 -0500, "Fred Morrison" <fmorrison@erols.com>
wrote:
>
> ¤ 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 ~~~ pclement@ameritech.net
> Microsoft MVP (Visual Basic)

Paul
Mon Jan 12 10:59:56 CST 2004

On Sat, 10 Jan 2004 17:03:52 -0500, "Fred Morrison" <fmorrison@erols.com> wrote:

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

onedaywhen
Tue Jan 13 09:12:31 CST 2004

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.

--

Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote in message news:<9rj500pb180k2fhtf7ldi00he5852dkier@4ax.com>...
> On Sat, 10 Jan 2004 17:03:52 -0500, "Fred Morrison" <fmorrison@erols.com> wrote:
>
> ¤ 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 ~~~ pclement@ameritech.net
> Microsoft MVP (Visual Basic)