469,592 Members | 1,670 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,592 developers. It's quick & easy.

bcp out empty string into csv files

Jay
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
Jun 27 '08 #1
2 13504
Have you tried using isnull() in the select?

Jay wrote:
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
Jun 27 '08 #2
Jay (rk*******@gmail.com) writes:
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?
I once filed a bug for this, but had it closed as by design. The issue
is that when you bulk-load a file, an empty field is taken as NULL, so
they need a way to import the empty string.

Since you are using queryout, there is an easy way out:
nullif(skillName, ''). This will also give NULL also for a string of spaces
only. If you want to retain trailing spaces, you need to use

CASE WHEN datalength(skillName) 0 THEN skillName ELSE NULL END

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 27 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Wayno | last post: by
3 posts views Thread by tornado | last post: by
1 post views Thread by balzano_1 | last post: by
6 posts views Thread by Tung Wai Yip | last post: by
6 posts views Thread by Chris Connett | last post: by
3 posts views Thread by Mads Westen | last post: by
5 posts views Thread by Per Juul Larsen | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.