OPENDATASOURCE: text file as input ?

[Jim Andersen]

Version 2000.
How do I do something like the example

SELECT *
FROM OpenDataSource( 'Microsoft.Jet. OLEDB.4.0',
'Data Source="c:\Fina nce\account.xls ";User ID=Admin;Passwo rd=;Extended
properties=Exce l 5.0')...xaction s

but use a .txt-file instead ?

I tried building it using Access (that usually works :-) ) and that gives a
connectionstrin g of:
Text;DSN=Link
Sammenkædningss pecifikation;FM T=Delimited;HDR =NO;IMEX=2;Char acterSet=850;DA T
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

[Simon Hayes]

Re: OPENDATASOURCE: text file as input ?


"Jim Andersen" <jimVÆÆK@office consult.dk> wrote in message
news:40bf26a4$0 $3050$14726298@ news.sunsite.dk ...[color=blue]
> Version 2000.
> How do I do something like the example
>
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet. OLEDB.4.0',
> 'Data Source="c:\Fina nce\account.xls ";User ID=Admin;Passwo rd=;Extended
> properties=Exce l 5.0')...xaction s
>
> but use a .txt-file instead ?
>
> I tried building it using Access (that usually works :-) ) and that gives[/color]
a[color=blue]
> connectionstrin g of:
> Text;DSN=Link
>[/color]
Sammenkædningss pecifikation;FM T=Delimited;HDR =NO;IMEX=2;Char acterSet=850;DA T[color=blue]
> 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
>
>[/color]

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/default.aspx?scid=kb;EN-US;149090

Simon

[Jim Andersen]

Re: OPENDATASOURCE: text file as input ?

Simon Hayes wrote:[color=blue]
> "Jim Andersen" <jimVÆÆK@office consult.dk> wrote in message
> news:40bf26a4$0 $3050$14726298@ news.sunsite.dk ...[color=green]
>> Version 2000.
>> How do I do something like the example
>>
>> SELECT *
>> FROM OpenDataSource( 'Microsoft.Jet. OLEDB.4.0',
>> 'Data Source="c:\Fina nce\account.xls ";User
>> ID=Admin;Passwo rd=;Extended properties=Exce l 5.0')...xaction s
>>
>> but use a .txt-file instead ?[/color][/color]
[color=blue]
> 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/default...b;EN-US;149090[/color]

Thx Simon, the links were very helpful.

Here is the solution:

File c:\temp\link.tx t
10248;"Vins et alcools Chevalier";7/4/1996 0:00:00
10249;"Toms Spezialitäten"; 7/5/1996 0:00:00
10250;"Hanari Carnes";7/8/1996 0:00:00

File c:\temp\schema. ini
[link.txt]
Format=Delimite d(;)
CharacterSet=OE M
ColNameHeader=F alse
Col1=CustomerNu mber Long
Col2=CustomerNa me 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/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

But... I can't get it to work with
Format=Delimite d(,)
or
Format=CSVDelim ited
and
File c:\temp\link.tx t
10248,"Vins et alcools Chevalier",7/4/1996 0:00:00
10249,"Toms Spezialitäten", 7/5/1996 0:00:00
10250,"Hanari Carnes",7/8/1996 0:00:00

or a ";" instead of a ","

But never mind.....
/jima

[Simon Hayes]

Re: OPENDATASOURCE: text file as input ?

<snip>
[color=blue]
> Thx Simon, the links were very helpful.
>
> Here is the solution:
>
> File c:\temp\link.tx t
> 10248;"Vins et alcools Chevalier";7/4/1996 0:00:00
> 10249;"Toms Spezialitäten"; 7/5/1996 0:00:00
> 10250;"Hanari Carnes";7/8/1996 0:00:00
>
> File c:\temp\schema. ini
> [link.txt]
> Format=Delimite d(;)
> CharacterSet=OE M
> ColNameHeader=F alse
> Col1=CustomerNu mber Long
> Col2=CustomerNa me 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
>[/color]
http://msdn.microsoft.com/library/de...a_ini_file.asp[color=blue]
>
> But... I can't get it to work with
> Format=Delimite d(,)
> or
> Format=CSVDelim ited
> and
> File c:\temp\link.tx t
> 10248,"Vins et alcools Chevalier",7/4/1996 0:00:00
> 10249,"Toms Spezialitäten", 7/5/1996 0:00:00
> 10250,"Hanari Carnes",7/8/1996 0:00:00
>
> or a ";" instead of a ","
>
> But never mind.....
> /jima
>
>[/color]

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

[test.txt]
Format=CSVDelim ited
CharacterSet=OE M
ColNameHeader=F alse
Col1=CustomerNu mber Long
Col2=CustomerNa me Text Width 40
Col3=EntryDate Datetime

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

What error message do you get?

Simon

[Jim Andersen]

Re: OPENDATASOURCE: text file as input ?

>> But... I can't get it to work with[color=blue][color=green]
>> Format=Delimite d(,)
>> or
>> Format=CSVDelim ited
>> and
>> File c:\temp\link.tx t
>> 10248,"Vins et alcools Chevalier",7/4/1996 0:00:00
>> 10249,"Toms Spezialitäten", 7/5/1996 0:00:00
>> 10250,"Hanari Carnes",7/8/1996 0:00:00
>>
>> or a ";" instead of a ","[/color][/color]
[color=blue]
> Format=CSVDelim ited seems to work fine for me, using your sample
> above:
>
> [test.txt]
> Format=CSVDelim ited
> CharacterSet=OE M
> ColNameHeader=F alse
> Col1=CustomerNu mber Long
> Col2=CustomerNa me Text Width 40
> Col3=EntryDate Datetime
>
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet. OLEDB.4.0',
> 'Data Source=c:\scrip ts;Extended properties=Text ')...test#txt
>
> What error message do you get?[/color]

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: Tekstfilspecifi kationens feltseparator er
magen til decimalseparato ren eller tekstafgrænsere n.]
Which means "The textfile's fieldseparator is the same as the
decimalseparato r or the textdelimiter."

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

thx again,

/jim