question

Teresa avatar image
Teresa asked

How can I get data from multiple text files into a SQL 2000 table?

I need to get data from multiple text files based on the name of the text file, into a table in SQL Server 2000. I have written a DTL package that can get data from one text file where the text files name is hard coded as the Source. The text file names are as follows: Log_20090923.txt, Log_20090924.txt - one for every day of the week. I need to get at least 30 days of the text files into the table. How can I do this? Can you give me an example please?
Thanks a lot!

sql-server-2000dts
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mrdenny avatar image
mrdenny answered

It might be easier to do this via T/SQL. You can use xp_dirtree to find the names of the files, then use the BULK IMPORT command to import each file into SQL Server.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tony Fountain avatar image
Tony Fountain answered

SSIS is another alternative.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Teresa avatar image
Teresa answered

mrdenny, What if I want only certain files, not all files?

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Teresa avatar image
Teresa answered

Makes perfect sence!! Thanks alot. I can't wait to try it on Monday.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kristen avatar image
Kristen answered

If this is a one-off you could concatenate the files together, and then import the single filename.

From a command line:

COPY MyFile1+MyFile2 MyOutputFile            

If it needs to be a scheduled task you could still do similar, using a BATch file, and have the BATch file call BCP to push the text file(s) into a table in the DB.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.