Re: ADO Excel connection string reference documentation

> ...in searching for the information on Microsoft, I was fustrated in not
> finding an "official" reference which lists the available HDR, IMAX, etc.
> values and their definitions. All I found were articles referring to
> similar information, or examples, etc...but not a reference. One example
> I
> found gave setting IMAX to 2...but did say what it would do.

Do you mean IMEX ?

> Is there such a link out there which gives all of the Excel connection
> string parameters, and all of their possible values??

No. Basically it is the Jet 4.0 provider that allows you to read from Text,
Excel, Access but it is woefully under-documented as to what it can do. I
confirm what you have found.

See
http://support.microsoft.com/default.aspx?scid=kb;en-us;278973

(This has stuff on Schema and explanation on HDR)
http://support.microsoft.com/default.aspx?scid=kb;en-us;278973

Also (which talks about what happens if IMEX is set to 1)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;257819

And here which refers to ADO.NET but it happens to be valid for ADO
http://blog.lab49.com/?p=196
It seems that when "IMEX=1" is in the connection string this forces the
register value
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes
to be checked.
And the value of
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows
is also read (speculation: There is probably a different Extended Property
in the Connection string that governs this).

It seem to me that Jet is doing something like

IF HDR = Yes THEN
' read the first row for the types of each column
ELSE
' guess the type based on number of rows
IF column is is mixed type AND IMEX is set
'get registry setting and apply that
END IF
END IF

Stephen Howe


.

Relevant Pages