bcp command is replacing empty strings with nulls

Question

The following bcp command is auto converting empty strings to nulls:

BCP "stored_proc_name" queryout C:\Outdir\data.csv -c -t , -T -Sserverx -Uusery -Ppassz -dproddb

I need the empty strings to be retained and not be replaced by NULLs.

What's the best way to do this? Can the bcp auto conversion be turned off?

Answer 1

That is by design:

out copies from the database table or view to a file. If you specify an existing file, the file is overwritten. When extracting data, note that the bcp utility represents an empty string as a null and a null string as an empty string.

The workaround is in your stored procedure, when issuing SELECT, use NULLIF() or COALESCE() function to output a NULL instead.

Answer 2

The best way is to leave null value not empty string on database. Then BCP will export empty string into file (.csv, .txt or so).

Please refer to BCP MSDN:

• out copies from the database table or view to a file. If you specify an existing file, the file is overwritten. When extracting data, note that the bcp utility represents an empty string as a null and a null string as an empty string.

Answer 3

I am using a similar BCP script to export data into .csv. I found the easiest (in terms of latter changes if necessary) to create a view from the tables you need with COALESCE() or ISNULL() and ALTER your stored procedure to SELECT the data from the view.