Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 importing multiple textfiles

Author  Topic 

Padraig
Starting Member

1 Post

Posted - 2003-03-07 : 10:08:13
how can I DTS in several text files at once, all files have the same format. i.e *.txt

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-07 : 10:19:11
Garth has an article on exporting files dynamically:

http://www.sqlteam.com/item.asp?ItemID=4722

You can change his code in about 20 seconds to do dynamic imports, using eithe bcp or BULK INSERT. It will be a lot easier to do it that way than with DTS.

You can also check this web site for DTS magic:

http://www.sqldts.com/

I'm positive they have methods for DTS to do the same thing.

Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-07 : 11:19:12
See signature below :).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-07 : 11:37:03
Who said anything about cursors?
CREATE TABLE #files (filename varchar(128))
INSERT INTO #files (filename) EXEC master..xp_cmdshell 'dir/b c:\*.txt'
DELETE #files WHERE NullIf(filename, 'File Not Found') IS NULL
DECLARE @sql varchar(8000)
WHILE EXISTS (SELECT * FROM #files)
BEGIN
SELECT @sql=''
SELECT TOP 50 @sql=@sql+'BULK INSERT myTable FROM ''C:\' + filename + ''' WITH (ROWTERMINATOR=''\n'', FIELDTERMINATOR='',''); ' FROM #files
DELETE #files WHERE CharIndex(filename, @sql)>0
EXEC(@sql)
END
DROP TABLE #files
Obviously you'd need to change the path for the dir and BULK INSERT calls to point to the actual path of the *.txt files, and change any additional BULK INSERT parameters. And this won't work correctly if you need to BULK INSERT into more than one table, or if the text files have different formats.

Wow, did I screw up that example! Maybe it'll work correctly now.

Edited by - robvolk on 03/13/2003 11:48:01
Go to Top of Page
   

- Advertisement -