Post reply

SQL Server Bulk Insert

  • May 15, 2009 at 8:13 am

    #204947

    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

    #995388

    Please post the code you are using

  • May 15, 2009 at 9:01 am

    #995410

    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

    #995432

    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

    #995448

    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

    #995471

    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

    #995473

    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

    #995479

    you cant use the c drive then

  • May 15, 2009 at 10:24 am

    #995501

    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

    #995511

    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 5:49 am

    #996237

    Your problem looks almost the same as what is happening to me! -> LINK

    I can't work it out, however it looks like it is down to permissions...

  • May 18, 2009 at 7:49 am

    #996331

    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