June 20, 2008 at 1:10 am
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
June 20, 2008 at 2:03 am
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
June 20, 2008 at 9:40 am
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?
August 21, 2008 at 11:03 am
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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy