January 13, 2009 at 9:26 am
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
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
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
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
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
You might try Openrowset (Bulk...)
-- 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