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:
&–0x26
^–0x5E
/–0x2F
\–0x5C
'–0x27
"–0x22
–0x3E
– –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\^^
Delimiters:

^^ 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 



 

http://en.wikipedia.org/wiki/C0_and_C1_control_codes

Advertisements


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s