I had come across this task few days ago. Hence, put it here for future reference. The requirement is simple. System need to scan a specific folder and import all files into database. Ideally, these are the steps how I do it:
- Create a import format file using BCP for BULK INSERT usage
- Use master..xp_cmdshell to get all the file names in to table variable
- Loop through the table and import data one by one using OPENROWSET and BULK
Create Sample Payment Table
Run Create_Payment_Table.sql to create a “Payment” Table. The columns must match with columns in CSV file.
Creating an XML Format file
Run
EXEC master..xp_cmdshell ‘bcp yourdbname.dbo.Payment format nul -T -t, -c -x -f C:\Payment.xml’
to create an XML format file for BULK INSERT usage.
Alter Sample Payment Table to add more columns
Run Alter_Payment_Table.sql to add extra columns ID, Filename, BatchID, DateCreated, DateModified for tracking purpose.
How to scan all CSV files in a specific folder?
Let say we expect all CSV files will be drop in c:\inbox folder. So, we will use master..xp_cmdshell with DIR dos command to get all the file names.
Store Proc “usp_import”
Loop through inbox folder and call “usp_import_common” to start import.
First declare a @Files table variable and insert result into @Files
Declare @Files Table ([File] varchar(250) null)
Insert all files’ name to @Files
INSERT @Files ([File])
EXEC master..xp_cmdshell ‘DIR C:\inbox\*.csv /b’
Open cursor to import CSV files one by one via store proc “usp_import_common”.
DECLARE @FILENAME VARCHAR(250)
DECLARE file_cursor CURSOR FOR
SELECT [File] FROM @Files
WHERE [File] NOT IN (‘NULL’,‘File Not Found’)
OPEN file_cursor
FETCH NEXT FROM file_cursor
INTO @FILENAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @FullFileName = @FilePath + @FILENAME
–Start Import
EXEC usp_import_common @FullFileName
FETCH NEXT FROM file_cursor
INTO @FILENAME
END
CLOSE file_cursor
DEALLOCATE file_cursor
Store Proc “usp_import_common”
Import data using BULK command with “Payment.xml” as format file
– Set Payment’s table XML format file
SET @FormatFile =‘C:\Payment.xml’
– Get data from file
SET @SQL1 = ” +
‘INSERT Payment’ +
‘(’ +
‘ [FileName],’ +
‘ [InvoiceNo],’ +
‘ [Date],’ +
‘ [Status],’ +
‘ [CheckNumber],’ +
‘ [VoucherNo],’ +
‘ [PaymentAmount],’ +
‘ [BatchID]‘ +
‘) ‘
SET @SQL2 = ” +
‘SELECT’ +
‘ ”’ + @SourceFile + ”’,’ +
‘ TMP.[InvoiceNo],’ +
‘ TMP.[Date],’ +
‘ TMP.[Status], ‘ +
‘ TMP.[CheckNumber],’ +
‘ TMP.[VoucherNo],’ +
‘ TMP.[PaymentAmount],’ +
‘ ”’ + CAST (@BatchID AS VARCHAR(50)) + ”’ ‘ +
‘FROM OPENROWSET(’ +
‘ BULK ”’ + @SourceFile + ”’, ‘ +
‘ FORMATFILE = ”’ + @FormatFile + ”’, ‘ +
‘ FIRSTROW = 2 ) TMP’
EXEC (@SQL1 + @SQL2)
After import, we will delete the CSV:
DECLARE @DirCMD VARCHAR(1000)
SELECT @DirCMD = ‘DEL “‘ + @SourceFile + ‘” /F’
EXEC master..xp_cmdshell @DirCMD
Finally copy testdata.csv file in to c:\inbox and run EXEC usp_import. Then, you are able to import data successfully.
You may download all the sql, xml and csv file here.
