How to Handle Special Characters as Delimiter for bcp

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:
– –0x3D
:–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


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s