Post reply

Bulk Insert and KeepNulls Option

  • January 13, 2009 at 9:26 am

    #199986

    Hello:

    Here is a bulk insert query that I use to import data from a file.

    bulk insert Data.Sigma_temp from 'S:\temp\file02.TXT' WITH (FORMATFILE='S:\temp\Format.fmt' ,FIRSTROW =1, KEEPNULLS)

    As you may see KEEPNULLS is set to True, but after the data is loaded, I still see EMPTY STRING values in the table for columns that do NOT have value in the data file. I want to have NULL instead of empty strings in the table. I thought that is what the KEEPNULLS option does but it does not.

    What am I missing here?.

    Thanks,

    Ganesh

  • January 13, 2009 at 11:39 am

    #926131

    You should check two things.

    1. Verify that the source data is actually NULL and not an empty string.

    2. Verify that the destination column does NOT has a default value of '' an allows the NULL.


    * Noel

  • January 13, 2009 at 1:15 pm

    #926199

    The file is a fixed width fields. Each field is of length "1". For example, one line of the data file is like this:

    A CDE

    Notice the space between the values A and C.

    If the above is inserted thru bulk inserted toa table with 5 columns (ColA, ColB, ColC, ColD, ColE) then I have:

    ColA : A

    ColB: "space"

    ColC: C

    ColD: D

    ColE: E

    How do I make the ColB have "NULL" instead of spaces?.

    Thanks,

    Ganesh

  • January 13, 2009 at 2:04 pm

    #926230

    The problem is that your input file does not have either NULL or an empty string for that field, but a Space, which is different.

    I do not see anyway that your import file can represent a NULL or empty value in a fixed-width field. Nor do I know of a way that the Bulk Insert command can change a " " to a NULL for you.

    Your only recourse then is to change it after BULK INSERT has imported it, probably with an UPDATE statement.

    [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]

  • January 13, 2009 at 3:09 pm

    #926267

    ganeshmuthuvelu (1/13/2009)

    The file is a fixed width fields. Each field is of length "1". For example, one line of the data file is like this:

    A CDE

    Notice the space between the values A and C.

    If the above is inserted thru bulk inserted toa table with 5 columns (ColA, ColB, ColC, ColD, ColE) then I have:

    ColA : A

    ColB: "space"

    ColC: C

    ColD: D

    ColE: E

    How do I make the ColB have "NULL" instead of spaces?.

    Thanks,

    Ganesh

    without field terminator "bulk insert" can't insert nulls.


    * Noel

  • January 14, 2009 at 11:11 am

    #926784

    You might try Openrowset (Bulk...)

    http://blogs.msdn.com/sqlserverstorageengine/archive/2006/05/30/Preprocessing-data-before-bulk-import.aspx

    -- insert into YourTable (ColA, ColB)

    SELECT

    ColA

    ,CASE WHEN LEN(ColB) > 0 THEN ColB ELSE NULL END AS ColB

    FROM OPENROWSET

    (BULK

    \\UNC\YourFile.txt

    ,FORMATFILE = \\UNC\FormatFile.fmt

    ) as YourData

Viewing 6 posts - 1 through 5 (of 5 total)

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