May 15, 2009 at 8:13 am
I am trying to load a CSV file into a table and maintain NULLs. If I use the import wizard, the file loads okay, but the empty fields are zero strings instead of NULLs.
I have tried the BULK INSERT statement, but I get an Msg 4861, Level 16, State 1, Line 1
(cannot find file path) error
I have tried copying the file to my local drive, and still get this error using BULK INSERT. I log on using Windows Authentication.
Any advice is appreciated.
May 15, 2009 at 8:39 am
Please post the code you are using
May 15, 2009 at 9:01 am
Below is the code
BULK INSERT DBO.an_STLaw_SampInfo
FROM 'C:\Data\sampling_info_050509_test.csv'
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
KEEPNULLS
)
go
May 15, 2009 at 9:19 am
code looks ok.. can you post the exact error messge - also are you sure this is the exact address of the file.. is it on the same box as the server?
May 15, 2009 at 9:32 am
Um, how is a NULL represented in a CSV? Especially if you do not (or cannot) have text delimiters defined?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 15, 2009 at 9:56 am
The file is not on the same box as the server. But it is on a server that is part of our internal network.
May 15, 2009 at 9:57 am
It isn't represented as a null, but when I bring in the file, I would like to have the fields represented as NULL instead of a zero length string.
May 15, 2009 at 9:58 am
you cant use the c drive then
May 15, 2009 at 10:24 am
That makes sense, but I have tried it on one of our network drives, and it doesn't work either.
May 15, 2009 at 10:35 am
lallen (5/15/2009)
The file is not on the same box as the server. But it is on a server that is part of our internal network.
In that case, could you script out the DDL of the target table and post it here? We also need a sample of the CSV input rows.
Thnx
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 18, 2009 at 7:49 am
Its a bit lazy but why not just update the table after the bulk load has finished?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply