How to Handle Special Characters as Delimiter for bcpPosted: November 12, 2014
You can use most printable characters as delimiter by itself or combinations to up to 1023 of them.
But some of them are belong to special categories so you need to handle them differently.
You cannot use &, ^, ” , ‘,>,<,: in single occurrence but you can put them inside double quotations to make them work.
Another way is to use ^ to escape them but not with ",', or :.
For forward slash / , you can leave no space between -t and / to make it work: -t/ but backslash works fine either way.
You double quote itself, you need to use \" to escape it. But double quoting does not work for hyphen – and it has to use hex value: 0x3D.
Another option is to use HEX value of all these charaters with one exception: colon– 0x3A does not work).
The HEX value for these characters:
:–0x3A (does not work).
There are two standard ASCII control codes we can use to separate columns and rows: ^_ for column separator and ^^ row separator.
Use like -t^_ and -r^^ or -t\^_ and -r\^^
^^ 30 1E RS Record Separator
^_ 31 1F US Unit separator
But when these two are used as delimiters with bcp, they may have the same limitations as with the underscore or caret themselves.
I would choose some customized multiple delimiters to avoid data conflicts.
--Test code DECLARE @cmd100 NVARCHAR(4000) set @cmd100 = 'BCP [test1].[dbo].[sales] OUT "C:\temp\sales9.txt" -c -t 0x22 -T -S'+ @@servername exec master..xp_cmdshell @cmd100 set @cmd100 = 'BCP [test1].[dbo].[sales] format nul -f "C:\temp\sales1.fmt" -c -t 0x22 -T -S'+ @@servername exec master..xp_cmdshell @cmd100 set @cmd100 = 'BCP [test1].[dbo].sales2 IN "C:\temp\sales9.txt" -f "C:\temp\sales1.fmt" -T -S'+ @@servername exec master..xp_cmdshell @cmd100 select * from [test1].[dbo].sales2 truncate table [test1].[dbo].sales2