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

• Andras Belokosztolszki

  SSC-Insane

  Points: 22031

  More actions

  June 20, 2008 at 2:03 am

  #831558

  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

  June 20, 2008 at 9:40 am

  #831903

  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

  August 21, 2008 at 11:03 am

  #861434

  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