IMEX=1 in OpenDataSource

I'm having an issue with selecting data from an Excel 2002 spreadsheet using
OpenDataSource (or DTS for that matter). Excel will sometimes identify the
column type as numeric and ignore all character data in the column,
returning nulls instead of the data (known issue -
http://support.microsoft.com/?id=194124). I've editted my registry to force
the Jet drive to always use text as the data types for columns; however, the
registry is ignored unless the "IMEX=1" switch appears in the connection
string. All the examples of using the switch that I've found have been for
using it with the command object in VBScript. Can this switch be used with
the OpenDataSource TSQL command? If so, what is the syntax and do I need to
have Office installed on the sql server?
Thanks!!!
WalterHi
It can be used according to http://www.connectionstrings.com/ e.g.
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended
Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
There is also a MAXSCANROWS extended property
http://support.microsoft.com/defaul...kb;en-us;278973
John
"Walt Mallon" wrote:

> I'm having an issue with selecting data from an Excel 2002 spreadsheet usi
ng
> OpenDataSource (or DTS for that matter). Excel will sometimes identify th
e
> column type as numeric and ignore all character data in the column,
> returning nulls instead of the data (known issue -
> http://support.microsoft.com/?id=194124). I've editted my registry to for
ce
> the Jet drive to always use text as the data types for columns; however, t
he
> registry is ignored unless the "IMEX=1" switch appears in the connection
> string. All the examples of using the switch that I've found have been fo
r
> using it with the command object in VBScript. Can this switch be used wit
h
> the OpenDataSource TSQL command? If so, what is the syntax and do I need
to
> have Office installed on the sql server?
> Thanks!!!
> Walter
>
>|||Got it!!! Had some quotes in the wrong place:
SELECT *
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\CEXP-Mixed-Analyze.xls;Extended properties="Excel
8.0;HDR=YES;IMEX=1"')...[data$]
That one worked!!!
Thanks.
"Walt Mallon" <waltmallon@.hotmail.nospam.com> wrote in message
news:e3M07$AyFHA.2652@.TK2MSFTNGP14.phx.gbl...
> I'm having an issue with selecting data from an Excel 2002 spreadsheet
> using OpenDataSource (or DTS for that matter). Excel will sometimes
> identify the column type as numeric and ignore all character data in the
> column, returning nulls instead of the data (known issue -
> http://support.microsoft.com/?id=194124). I've editted my registry to
> force the Jet drive to always use text as the data types for columns;
> however, the registry is ignored unless the "IMEX=1" switch appears in the
> connection string. All the examples of using the switch that I've found
> have been for using it with the command object in VBScript. Can this
> switch be used with the OpenDataSource TSQL command? If so, what is the
> syntax and do I need to have Office installed on the sql server?
> Thanks!!!
> Walter
>