Automate importation of text files

[dswitzer]

I have a list of several hundred text files I want to move into SQL Server.

The text file names include dates such as:

x_2001_01_01.txt
x_2001_01_02.txt
....
x_2002_03_01.txt

I can write a looping script in VB to import it into Access -- but the dataset is much too large (would need 36 DB's. I know SQL Server must have a way to get through this...but am pounding my head into the wall to figure out how to get this done.

Thanks.

 

[dswitzer]

Well --
I looked into Bulk Insert -- and then just did a bunch of concatatenation in excel to get 450 bulk insert statements based on a date field....

It worked, but there has to be an easier way...

Bueller?Bueller?

 

[JayKusch]

Can you use an xp_cmdshell command to append all the text files into one file and then process the single file. Something like ...

exec
master..xp_cmdshell 'COPY C:\WhateverDir\x_2*.txt All.txt'


Thanks

J. Kusch