SQL Server ( dts ? ) import multiple files into multiple tables

[j69ant]

Hi all,

Im struggling a bit trying to import csv files into an sql server table.

At present, we submit a data processing job that creates multiple cvs on a clients ftp server, for them to check / ammend etc. The file names are chosen by a job number, and are basically an 8 digit number, ie 12345678.csv or 98832322.csv. When we upload the file to the ftp, we also run an asp script that creates a table in our sql server with the same coloum names as the csv. The table is also called the same name as the csv file, ie 12345678

Therefore for every csv ever created and sent, we also have a table in the database.

I have created a process to check for updated files and new files on the clients ftp that runs every 30 minutes , and if so download the files back to a folder on our server.

What i need to do is import the csv files back into the sql server, ie every 30 minutes a process runs which imports each individually named csv into a table under the same name, ie 12345678.csv imports into the 12345678 table etc

It also needs to loop through every files in the folder and do this.

Is there a way using dts or some other sql function to import every csv in a folder into its own individual tables

Many thanks in advance

Anthony

 

[mrdenny]

Yes this can be done with some batch file scripting, and BCP.

First get a list of all the files in the folder without headers and store it in a text file.

dir /B *.csv > c:\dir.txt

Then use the dos command FOR to loop through the outputted text file c:\dir.txt and BCP the data into the correct table.

@for /f [delims=.] %%a in (c:\dir.txt) do bcp %%a in  %%a.csv -n

You might need some additional bcp flags. I don't remember all the one's you'll need for this.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)