How do I Import textfile into SQL table using schema

I've been importing delimited textfile data into Access db using a Schema.in
i
file
to describe the text like this
[Import.TXT] ( where the raw text is )
ColNameHeader=False
Format=CSVDelimited
CharacterSet=ANSI
Col1 = Field1 text width 255
Col2 = Field2 text width 255
Col3 = Field3 text width 255
Col4 = Field4 text width 255
With the following code ( VB )
'{Microsoft Text Driver (*.txt; *.csv)}
' paths for the jet engine to find schema & files + text driver for
importation
strCn = "Driver=" & "{Microsoft Text Driver (*.txt; *.csv)}" & ";" & _
"DBQ=" & filepath & ";" & _
"DefaultDir=" & filepath & ";" & _
"Uid=Admin;Pwd=;"
' use ADO - init connection
Set adoCn = New ADODB.Connection
adoCn.Open strCn
then
strSQL = "SELECT * INTO [" & tbl & "] IN '" & dbName & "'"
strSQL = strSQL & "FROM " & txtfile
adoCn.Execute strSQL
to import the text into the appropriate fields in the table.
How do I do this with SQL Server 200?
thanks -- jackTry using DTS Wizard to do the job !
run at shell command "dtswiz"
Pollus Brodeur|||Hi
Along with DTS, you can use BCP or the BCP API. You may also want to look at
the BULK INSERT statement.
John
"hushtech" <hushtech@.discussions.microsoft.com> wrote in message
news:A78B5213-B579-44CC-AA3F-7949BC661C7E@.microsoft.com...
> I've been importing delimited textfile data into Access db using a
> Schema.ini
> file
> to describe the text like this
> [Import.TXT] ( where the raw text is )
> ColNameHeader=False
> Format=CSVDelimited
> CharacterSet=ANSI
> Col1 = Field1 text width 255
> Col2 = Field2 text width 255
> Col3 = Field3 text width 255
> Col4 = Field4 text width 255
> With the following code ( VB )
> '{Microsoft Text Driver (*.txt; *.csv)}
> ' paths for the jet engine to find schema & files + text driver for
> importation
> strCn = "Driver=" & "{Microsoft Text Driver (*.txt; *.csv)}" & ";" & _
> "DBQ=" & filepath & ";" & _
> "DefaultDir=" & filepath & ";" & _
> "Uid=Admin;Pwd=;"
> ' use ADO - init connection
> Set adoCn = New ADODB.Connection
> adoCn.Open strCn
> then
> strSQL = "SELECT * INTO [" & tbl & "] IN '" & dbName & "'"
> strSQL = strSQL & "FROM " & txtfile
> adoCn.Execute strSQL
> to import the text into the appropriate fields in the table.
> How do I do this with SQL Server 200?
> thanks -- jack|||Jack,
If the import file will always be in the same directory, you could
set up a linked server to that directory. This will automatically
use the specifications in the schema.ini file. Here is an example
for exporting, but it should be simple to adapt for importing.
1) Create a directory for the text files. I chose E:\txtsrv
2) Add this as a linked server
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'e:\txtsrv',
NULL,
'Text'
3) Put an empty text file in this directory (I used empty.txt).
You can do this with master..xp_cmdshell, but I didn't. You
can't do CREATE TABLE on the linked server.
4) Create a schema.ini file to describe how this file should
represent your table. One schema.ini file is used for all the
tables of the linked server.
This is my schema.ini:
[empty.txt]
Format=FixedLength
ColNameHeader=True
Col1=CustomerNumber Long Width 11
Col2=CustomerName Text Width 30
Col3=CustomerDate DateTime Width 25
5) Populate the text-table, replacing the filename's . with # for the
table name.
insert into txtsrv...empty#txt(CustomerNumber, CustomerName,
CustomerDate)
select orderid, customerid, orderdate
from northwind..orders
order by customerid
Steve Kass
Drew University
"hushtech" <hushtech@.discussions.microsoft.com> wrote in message
news:A78B5213-B579-44CC-AA3F-7949BC661C7E@.microsoft.com...
> I've been importing delimited textfile data into Access db using a
> Schema.ini
> file
> to describe the text like this
> [Import.TXT] ( where the raw text is )
> ColNameHeader=False
> Format=CSVDelimited
> CharacterSet=ANSI
> Col1 = Field1 text width 255
> Col2 = Field2 text width 255
> Col3 = Field3 text width 255
> Col4 = Field4 text width 255
> With the following code ( VB )
> '{Microsoft Text Driver (*.txt; *.csv)}
> ' paths for the jet engine to find schema & files + text driver for
> importation
> strCn = "Driver=" & "{Microsoft Text Driver (*.txt; *.csv)}" & ";" & _
> "DBQ=" & filepath & ";" & _
> "DefaultDir=" & filepath & ";" & _
> "Uid=Admin;Pwd=;"
> ' use ADO - init connection
> Set adoCn = New ADODB.Connection
> adoCn.Open strCn
> then
> strSQL = "SELECT * INTO [" & tbl & "] IN '" & dbName & "'"
> strSQL = strSQL & "FROM " & txtfile
> adoCn.Execute strSQL
> to import the text into the appropriate fields in the table.
> How do I do this with SQL Server 200?
> thanks -- jack|||PollusB,
I was able to use the DTS Wizard to get a CSV file into my Table, but I
need to do this programatically on many files every day so a manual operatio
n
is not practical. I have seen some information that indicates DTS can be
used as an object etc., but I have no clue at this point about how that migh
t
be done. If you know how the task can be done using DTS internal to a VB ap
p
I'd sure appreciate it.
thanks for your help -- jack
"PollusB" wrote:

> Try using DTS Wizard to do the job !
> run at shell command "dtswiz"
> Pollus Brodeur
>|||John,
I tried to use a BCP format file, but since my input file has varying
number of columns per row, I wasn't able to get it to work. I've tried the
Bulk Insert approach
and it has the same problem. It wants to have the input 'line' contain the
exact
number of fields as the table, otherwise it gives an unexpected end of line
errror.
I've pretty much given up on the Bulk Insert method because I couldn't find
a method to get around it's limitations. I'm currently looking at how to us
e
DTS in
my application to get the files imported.
Thanks for your suggestions -- jack
"John Bell" wrote:

> Hi
> Along with DTS, you can use BCP or the BCP API. You may also want to look
at
> the BULK INSERT statement.
> John
> "hushtech" <hushtech@.discussions.microsoft.com> wrote in message
> news:A78B5213-B579-44CC-AA3F-7949BC661C7E@.microsoft.com...
>
>|||Steve,
The technique you've suggested is almost verbatim the method I used for
Access
databases. It worked very well in that environment. I couldn't get the
'linked server' to work; I'm actually not familiar with that technique.
Thanks for your suggested solution and code -- jack
"Steve Kass" wrote:

> Jack,
> If the import file will always be in the same directory, you could
> set up a linked server to that directory. This will automatically
> use the specifications in the schema.ini file. Here is an example
> for exporting, but it should be simple to adapt for importing.
> 1) Create a directory for the text files. I chose E:\txtsrv
> 2) Add this as a linked server
> EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> 'e:\txtsrv',
> NULL,
> 'Text'
> 3) Put an empty text file in this directory (I used empty.txt).
> You can do this with master..xp_cmdshell, but I didn't. You
> can't do CREATE TABLE on the linked server.
> 4) Create a schema.ini file to describe how this file should
> represent your table. One schema.ini file is used for all the
> tables of the linked server.
> This is my schema.ini:
> [empty.txt]
> Format=FixedLength
> ColNameHeader=True
> Col1=CustomerNumber Long Width 11
> Col2=CustomerName Text Width 30
> Col3=CustomerDate DateTime Width 25
> 5) Populate the text-table, replacing the filename's . with # for the
> table name.
> insert into txtsrv...empty#txt(CustomerNumber, CustomerName,
> CustomerDate)
> select orderid, customerid, orderdate
> from northwind..orders
> order by customerid
> Steve Kass
> Drew University
> "hushtech" <hushtech@.discussions.microsoft.com> wrote in message
> news:A78B5213-B579-44CC-AA3F-7949BC661C7E@.microsoft.com...
>
>