bcp -t field terminator

Typical bcp out uses comma (csv) or tab (tsv) but there was a special request that fields should be separated by char "|". bcp.exe has a special -t option to change default field terminator (tab) to something else. So tried to use this option.

C:> bcp "select au_id, au_lname from pubs.dbo.authors" queryout "1.csv" -S. -t | -c -T
'-c' is not recognized as an internal or external command,
operable program or batch file.

The problem is | in "-t | -c " is not considered as field terminator. In command shell, | is considered as pipe command. so prior to | is one command and the result is passed to next to | command, which is "-c" here. But there is no -c command, hence the error.

So how to solve this problem? Fortunately, we can use 1 byte hex value in -t option. So since Ascii hex value for | character is 0x7C, we can put this value in -t option to avoid command pipe.

C:> bcp "select au_id, au_lname from pubs.dbo.authors" queryout "1.csv" -S. -t 0x7C -c -T

Starting copy...

23 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1      Average : (23000.00 rows per sec.)