bcp out empty string in the table into csv file

Question

bcp out empty string in the table into csv file

rkjayaraj

Old Hand

Points: 343
More actions
June 20, 2008 at 1:10 am

#189463

I have a SQL Server table with nvarchar type column which has not null constraint. I am inserting empty string ("") from Java to the table column. When I export this table into .csv file using bcp tool, empty string gets written as NUL character. Is there any way I can bcp out empty string as empty string itself instead of NUL to the file?
The bcp command I used to export table into csv file is
bcp "SELECT skillID,profileID,skillName,active,dateInactive from db_cra..Skill" queryout C:\Skill.csv -w -S 10.76.253.70\CRSSQL -t"," -T
In the table skillName column can have empty string value. Corresponding to the empty string csv file contain 'NUL' stored in it. I do not want 'NUL'. I need empty string in the resulting file.
Is there any way it can be done?
Thanks
Jayaraj

Viewing 4 posts - 1 through 3 (of 3 total)

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

rkjayaraj (6/20/2008)
I have a SQL Server table with nvarchar type column which has not null constraint. I am inserting empty string ("") from Java to the table column. When I export this table into .csv file using bcp tool, empty string gets written as NUL character. Is there any way I can bcp out empty string as empty string itself instead of NUL to the file?
The bcp command I used to export table into csv file is
bcp "SELECT skillID,profileID,skillName,active,dateInactive from db_cra..Skill" queryout C:\Skill.csv -w -S 10.76.253.70\CRSSQL -t"," -T
In the table skillName column can have empty string value. Corresponding to the empty string csv file contain 'NUL' stored in it. I do not want 'NUL'. I need empty string in the resulting file.
Is there any way it can be done?
Thanks
Jayaraj

The bcp utility represents an empty string as a null and a null string as an empty string 🙂

So what you can do is to use something along the lines of:

CASE WHEN len(thecolumn) = 0 THEN NULL ELSE thecolumn END

so if in your case it is the skillName column that may contain nulls, use:

bcp "SELECT skillID,profileID,CASE WHEN len(skillName ) = 0 THEN NULL ELSE skillName END,active,dateInactive from db_cra..Skill" queryout C:\Skill.csv -w -S 10.76.253.70\CRSSQL -t"," -T

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

rkjayaraj Old Hand Points: 343 More actions · Answer 2

Thanks for your help!

If I write null in case of empty string as you suggested, it is still written as null not as empty string while doing bcp out. could you please clarify how can I get empty string in the output file?

Yuri Mekesha SSC Rookie Points: 29 More actions · Answer 3

I faced with the same issue.

1) I think thecolumn= nullif(thecolumn,'') will be more graceful.

2) In the file this value will be stored as empty string, not char(0). But when you import data into SQL you can use -k bcp-parameter to manage null values

Regards, Yuri