Re: ADO Excel connection string reference documentation

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

  • Re: Thanks, but it doesnt work with .Net
    ... IMEX should work with ADO.NET as well. ... I think you have incorrect syntax in your connection string. ... I have written an app which reads an Excel spreadsheet and loads the ... In one column the cells can be any ...
    (microsoft.public.vb.database.ado)
  • Re: What IMEX mean in connection string with Excel
    ... Have an opinion on the effectiveness of Microsoft Embedded newsgroups? ... > My Connection String for excel have some problem before, ... > After the guide of Paul, changing the string by add IMEX and 34, it ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Thanks, but it doesnt work with .Net
    ... Here is my original connection string in my app.config file. ... when I realized I'd left out the double quotes in Paul's ... > Hi Geoff, ... > you post your connection string with IMEX here? ...
    (microsoft.public.vb.database.ado)
  • RE: read csv file to sql
    ... it was my fault to add another HDR in the connection string. ...
    (microsoft.public.dotnet.languages.csharp)
  • RE: How to get ADO 2.8 to obey HDR=NO ?
    ... Since you cannot get to the first row in the text file and you have ... schema.ini file in which the ColNameHeader property has been set to true. ... Prev by Date: ...
    (microsoft.public.data.ado)