OPENDATASOURCE: text file as input ?

Version 2000.
How do I do something like the example

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended
properties=Excel 5.0')...xactions

but use a .txt-file instead ?

I tried building it using Access (that usually works :-) ) and that gives a
connectionstring of:
Text;DSN=Link
Sammenkdningsspecifikation;FMT=Delimited;HDR=NO;I MEX=2;CharacterSet=850;DAT
ABASE=c:\temp
Sourcetablename=link.txt

but I can't seem to "massage" it into working on the sql-server.

If I quick and dirty swap 'Microsoft.Jet.OLEDB.4.0' with 'Text' it gives
error:
Could not locate registry entry for OLE DB provider 'Text'.

tia
/jim"Jim Andersen" <jimVK@.officeconsult.dk> wrote in message
news:40bf26a4$0$3050$14726298@.news.sunsite.dk...
> Version 2000.
> How do I do something like the example
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended
> properties=Excel 5.0')...xactions
> but use a .txt-file instead ?
> I tried building it using Access (that usually works :-) ) and that gives
a
> connectionstring of:
> Text;DSN=Link
Sammenkdningsspecifikation;FMT=Delimited;HDR=NO;I MEX=2;CharacterSet=850;DAT
> ABASE=c:\temp
> Sourcetablename=link.txt
> but I can't seem to "massage" it into working on the sql-server.
> If I quick and dirty swap 'Microsoft.Jet.OLEDB.4.0' with 'Text' it gives
> error:
> Could not locate registry entry for OLE DB provider 'Text'.
> tia
> /jim

These articles might be helpful - you need a schema.ini file in addition to
the DSN:

http://www.users.drew.edu/skass/sql/TextDriver.htm
http://support.microsoft.com/defaul...kb;EN-US;149090

Simon|||Simon Hayes wrote:
> "Jim Andersen" <jimVK@.officeconsult.dk> wrote in message
> news:40bf26a4$0$3050$14726298@.news.sunsite.dk...
>> Version 2000.
>> How do I do something like the example
>>
>> SELECT *
>> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
>> 'Data Source="c:\Finance\account.xls";User
>> ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
>>
>> but use a .txt-file instead ?

> These articles might be helpful - you need a schema.ini file in
> addition to the DSN:
> http://www.users.drew.edu/skass/sql/TextDriver.htm
> http://support.microsoft.com/defaul...kb;EN-US;149090

Thx Simon, the links were very helpful.

Here is the solution:

File c:\temp\link.txt
10248;"Vins et alcools Chevalier";7/4/1996 0:00:00
10249;"Toms Spezialitten";7/5/1996 0:00:00
10250;"Hanari Carnes";7/8/1996 0:00:00

File c:\temp\schema.ini
[link.txt]
Format=Delimited(;)
CharacterSet=OEM
ColNameHeader=False
Col1=CustomerNumber Long
Col2=CustomerName Text Width 40
Col3=EntryDate Datetime

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\temp";Extended properties=Text')...table1#txt

And a link about schema.ini
http://msdn.microsoft.com/library/d...ma_ini_file.asp

But... I can't get it to work with
Format=Delimited(,)
or
Format=CSVDelimited
and
File c:\temp\link.txt
10248,"Vins et alcools Chevalier",7/4/1996 0:00:00
10249,"Toms Spezialitten",7/5/1996 0:00:00
10250,"Hanari Carnes",7/8/1996 0:00:00

or a ";" instead of a ","

But never mind....
/jima|||<snip
> Thx Simon, the links were very helpful.
> Here is the solution:
> File c:\temp\link.txt
> 10248;"Vins et alcools Chevalier";7/4/1996 0:00:00
> 10249;"Toms Spezialitten";7/5/1996 0:00:00
> 10250;"Hanari Carnes";7/8/1996 0:00:00
> File c:\temp\schema.ini
> [link.txt]
> Format=Delimited(;)
> CharacterSet=OEM
> ColNameHeader=False
> Col1=CustomerNumber Long
> Col2=CustomerName Text Width 40
> Col3=EntryDate Datetime
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="c:\temp";Extended properties=Text')...table1#txt
> And a link about schema.ini
http://msdn.microsoft.com/library/d...ma_ini_file.asp
> But... I can't get it to work with
> Format=Delimited(,)
> or
> Format=CSVDelimited
> and
> File c:\temp\link.txt
> 10248,"Vins et alcools Chevalier",7/4/1996 0:00:00
> 10249,"Toms Spezialitten",7/5/1996 0:00:00
> 10250,"Hanari Carnes",7/8/1996 0:00:00
> or a ";" instead of a ","
> But never mind....
> /jima

Format=CSVDelimited seems to work fine for me, using your sample above:

[test.txt]
Format=CSVDelimited
CharacterSet=OEM
ColNameHeader=False
Col1=CustomerNumber Long
Col2=CustomerName Text Width 40
Col3=EntryDate Datetime

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\scripts;Extended properties=Text')...test#txt

What error message do you get?

Simon|||>> But... I can't get it to work with
>> Format=Delimited(,)
>> or
>> Format=CSVDelimited
>> and
>> File c:\temp\link.txt
>> 10248,"Vins et alcools Chevalier",7/4/1996 0:00:00
>> 10249,"Toms Spezialitten",7/5/1996 0:00:00
>> 10250,"Hanari Carnes",7/8/1996 0:00:00
>>
>> or a ";" instead of a ","

> Format=CSVDelimited seems to work fine for me, using your sample
> above:
> [test.txt]
> Format=CSVDelimited
> CharacterSet=OEM
> ColNameHeader=False
> Col1=CustomerNumber Long
> Col2=CustomerName Text Width 40
> Col3=EntryDate Datetime
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source=c:\scripts;Extended properties=Text')...test#txt
> What error message do you get?

Hmmm, today it behaves a little different :-) I was sure it gave me a "ole
db error. the provider didnt return any information about what went wrong."
but today it gives me

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Tekstfilspecifikationens feltseparator er
magen til decimalseparatoren eller tekstafgrnseren.]
Which means "The textfile's fieldseparator is the same as the
decimalseparator or the textdelimiter."

But now I can get it to work with ";" and Format=Delimited(;)

thx again,

/jim