BULK INSERT from variable Date Filename - ERROR

Question

BULK INSERT from variable Date Filename - ERROR

Pigger

Old Hand

Points: 384
More actions
October 2, 2014 at 7:44 am

#98848

I am trying to do an insert of a csv that has the date as its filename.
DECLARE @dt AS VARCHAR(30)
SELECT @dt = REPLACE(CONVERT(date,getdate(),120),'-','')
DECLARE @sql varchar(max)
SET @sql=
'BULK INSERT Train.dbo.test
FROM "c:\data\'+@dt+ '.csv"'
+ 'WITH
(FIELDTERMINATOR = ",",
ROWTERMINATOR = "",
FIRSTROW = 2
)'
select @sql
exec(@sql)
The result shows the string as being
BULK INSERT Train.dbo.test FROM "c:\data\20141002.csv"WITH (FIELDTERMINATOR = ",", ROWTERMINATOR = "", FIRSTROW = 2 )
When I run the above I get a message of -
(1 row(s) affected)
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
Any ideas why I get this error ?
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You might be missing a space between your data file path and WITH keyword. Do you really don't have a row terminator?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

You've used double quotes in places where there should be single quotes.

Provided that @dt has been sufficiently scrubbed for SQL Injection, the following should work and requires a whole lot less attention when it comes to creating dynamic SQL. Of course, I've not test this particular code but it is the method that I use for such things.

SET @sql= REPLACE(REPLACE(

'BULK INSERT Train.dbo.test

FROM <<File>>

WITH

(FIELDTERMINATOR = ",",

ROWTERMINATOR = "",

FIRSTROW = 2

)'

,'"','''')

,'<<File>>',QUOTENAME('c:\data\'+@dt+ '.csv','"')

select @sql

exec(@sql)

To make your code a bit more bullet proof in the face of change, you might also want to make the path to the file dynamic.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)