How to import multiple CSV files using MS SQL store procedure?
March 29th, 2007 — ¥ong¥s
Online project management, task software, and free collaboration workspace: AJAXWorkspace
AJAXWorkspace is a free online workspace that offers task tracking software, team collaboration, online calendar, document sharing, and file management for project management and teamwork. |
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.
May 9th, 2007 at 10:53 pm
[...] may encounter this error while testing on the sample in “How to import multiple CSV files using MS SQL store procedure?“ Posted in [...]
March 24th, 2008 at 4:04 am
Get an “access denied” when I try running the above example. What do I need to do to correct this?
March 24th, 2008 at 11:42 am
I’m having alot of trouble running the bcp statement above. Any help would be great.
March 24th, 2008 at 10:07 pm
Hi cfigy, bcp issues,
Can you please provide more info on the error? Which part you get the error? Is your bcp working at the first place?
I would need more infor in order to help on these. Thanks.
YS
March 25th, 2008 at 9:06 am
My cvs files do not have a header row, how would I use the above code modified for a cvs file without a header row? Also, I really don’t want to add the additional information to each row. I tried removing it, but incounter a select and insert statements don’t match error.
March 25th, 2008 at 11:34 pm
Hi,
You have 2 issues here:
1. To import a file without header, simply change “FIRSTROW = 2″ in usp_import_common.sql to “FIRSTROW = 1″.
2. For the additional information issues, you have to make sure “INSERT Payment…” query in usp_import_common.sql doesn’t include the additional columns. In this case, you should exclude “[FileName]” and “[BatchID]“. The section should be something like this:
SET @SQL1 = ” +
‘INSERT Payment’ +
‘(’ +
‘ [InvoiceNo],’ +
‘ [Date],’ +
‘ [Status],’ +
‘ [CheckNumber],’ +
‘ [VoucherNo],’ +
‘ [PaymentAmount]‘ +
‘) ‘
SET @SQL2 = ” +
‘SELECT’ +
‘ TMP.[InvoiceNo],’ +
‘ TMP.[Date],’ +
‘ TMP.[Status], ‘ +
‘ TMP.[CheckNumber],’ +
‘ TMP.[VoucherNo],’ +
‘ TMP.[PaymentAmount] ‘ +
‘FROM OPENROWSET(’ +
‘ BULK ”’ + @SourceFile + ”’, ‘ +
‘ FORMATFILE = ”’ + @FormatFile + ”’, ‘ +
‘ FIRSTROW = 2 ) TMP’
Hope this will help to solve your problem
March 31st, 2008 at 9:25 am
Thank You very much for the above code. It is very helpful. I have a question that you might be able to help me with. I’m importing several hundred files and each row has a date that i want to add a time to the date. There are acouple of other columns in the cvs files I would like to add or remove parts of the data. Is there an easy way to change or manipulate the data in the above code while it is being imported? (Thanks Again)
March 31st, 2008 at 10:12 pm
You have to create the xml schema base on all the columns in your file. But, you can choose those fields required. Just select “TMP.[Columns]” according to your needs.
For the appending time to a date field, I am not sure what you want here. I assume u got a date column and want to append current time to that field and save to DB. I got a stupid but works method. You might have a better solution. First, I assume you got a date only columns in proper format (YourDateField). Then, proceed to get the current DateTime using GETDATE() function and convert to VARCHAR with time format only. Lastly, concatenate to your date field. Then, you will get your date column + current time. Here is the simple example:
DECLARE @YourDateField AS VARCHAR(50)
SET @YourDateField = ‘2008-03-31′
SELECT CAST(@YourDateField + ‘ ‘ + Convert(VARCHAR(50),getdate(), 14) AS DATETIME) AS DateTimeField
June 7th, 2008 at 6:28 pm
I have sql server 2000 and gives me error “EXECUTE cannot be used as a source when inserting into a table variable.”
How to rectify that
August 17th, 2008 at 11:54 am
[...] Read the rest of this entry » Posted in SQL. [...]