BCP to .csv delimiter issue

Question

I am having issue with the way results are displayed with a bcp export to csv.

The below works fine, but is comma delimited so won't work for what I need.

DECLARE @sql VARCHAR(8000)

SET @sql = 'bcp "SELECT * FROM db..viewname" queryout "C:\test.csv" -c –t, -T -S <SERVERNAME>'

EXEC master..xp_cmdshell @sql

Results:

But If I change the "," to a pipe (or anything else) it breaks.

DECLARE @sql VARCHAR(8000)

SET @sql = 'bcp "SELECT * FROM db..viewname" queryout "C:\test.csv" -c –t"|" -T -S <SERVERNAME>'

EXEC master..xp_cmdshell @sql

Results:

The view used is a simple column select from a table with a WHERE clause.

What am I missing here..?

Solution

I know this is an old question but I just ran into this problem yesterday. To add a pipe delimiter you need to escape the pipe with a carrot like so:

-t^|

So set your @SQL like so:

SET @sql = 'bcp "SELECT * FROM db..viewname" queryout "C:\test.csv" -c –t^| -T -S <SERVERNAME>'

Hope this helps someone. :)