November 7th, 2012, 11:59 AM
Importing multiple csv files into a table using DTS package and a batch file
I am trying to import multiple csv files into a database table.
I am using a DTS package (to insert the data) and a batch file to list all the csv files.
I've actually been following this article:
Has anyone tried using this particular method - using a batch file?
I have managed to follow most of the article and steps, but unsure of step 2 - where the input file is added.
the input file asks for the name of a csv/txt file - if i enter the name of a csv file, I can input this into a table ok.
However, I am confused by this this as the objective is to input multiple files.
In order to get something imported, I have used the name of the first file to be imported (a.csv) in step 2, however this just results in only this solitary file being imported and no other.
(I have an existing table set up already and have created a new user with read/write permission - as used in the batch file)
* My batch file works so far as creating a text file with a list of all csv files in same directory as the DTS package and csv files.
* Not sure if I should specify this text file name (DirList.txt) as the name of the input file (in step 2) - but when I do it justs imports the file names and not the data inside.
my batch file:
REM Export DIR listing to C:\MyImport\Dirlist.txt
dir c:\MyImport\*.csv /b > C:\MyImport\Dirlist.txt
REM Execute DTS package for every file name in the Dirlist.txt
for /f "tokens=1,2,3" %%i in (C:\MyImport\Dirlist.txt)
do "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\DTSrun.exe" -F
"c:\myimport\myimportdts.dts" -UmyImportuser -PmyImportPWD -A"ServerName"=
"TEST-SQL" -A"FileName"="c:\myimport\%%i" -A"DatabaseName"="myDBName"
REM Rename all the files with "old" as suffix
ren c:\myimport\*.csv *.csvold