How to load null values into text fields using BCP

[ddiamond]

I am using BCP to import a fixed width flat file into an sql server staging table. The problem is that when a text field is blank, BCP is loading spaces into the staging table. I would like BCP to treat a blank field as null, not spaces. Is this possible?

- Dan

 

[gmmastros]

Just a guess, but...

I suggest you try adding the -k command line parameter to your bcp command. -k is for "keep null values". More here:

http://technet.microsoft.com/en-us/library/ms187887.aspx

I never used this, but I suspect it's worth a try.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ddiamond]

I tried it, but it did not work. The problem is since it is a fixed width flat file, there is no way to distinguish a blank field from a field that is all spaces. I would like bcp alway treat all spaces as a null.

 

[gmmastros]

There may be a method that I am not aware of.

What you could do is to import the data in to a temporary holding table. Then write a query that inserts the data in to your real table. Something like...

Insert Into RealTable(Col1, Col2)
Select Col1, [!]NullIf([/!]Col2[!], ' ')[/!]
From   TemporaryHoldingTable

Generally, it's best to avoid temporary holding tables if possible, but it's also a good technique to use when you need to "clean up" your data prior to loading in to real tables.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ddiamond]

George,

Thanks for the tip. I may have to use a holding table like you suggested.

- Dan