By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,537 Members | 1,471 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,537 IT Pros & Developers. It's quick & easy.

bcp out empty string into csv files

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.