How to Use Special Delimiters with bcp


I have include a few special delimiters samples to work with bcp command and I hope it can save you some tiem if yo come across this post.

1. Caret ^, which has code point 5E in hex. It can be used to escape some characters like it was in the command-line interpreter to escape reserved characters.

The usages:
Option 1: -t ^^^ works with both fmt and xml format files;
Option 2: -t “^” works with both fmt and xml format files;
Option 3: -t 0x5E works with both fmt and xml format files;
Option 4: -t “0x5E” works with both fmt and xml format files;
Option 5: -tx5E works but show the hex value in both data and format files;

2. Pipe |, which is a commonly used delimiter.
The usages:
Option 1: -t\^| works with both fmt and xml format files;
Option 2: -t\ “|” works with both fmt and xml format files;
Option 3: -t “0x7C” works with both fmt and xml format files;
Option 4: -t\ 0x7C works with both fmt and xml format files;
Option 5: -tx7C works with xml format files but not fmt format;

3. Null terminator (nonvisible terminator)
-t null controll character works with both fmt and xml format files;

4. No delimiter: no space 0x90 or 0x8F
-t 0x90 generates no delimiter file;
-t 0x8F generates no delimiter file.






--^ Caret 0x5E ----------------^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

 

--Step 1
DECLARE @cmd3 NVARCHAR(4000) 
set @cmd3 = 'BCP  [test1].[dbo].[sales] OUT  "C:\temp\sales1.txt"  -c -t ^^^  -T  -S'+ @@servername
exec master..xp_cmdshell @cmd3
set @cmd3 = 'BCP  [test1].[dbo].[sales] format nul   -f  "C:\temp\sales1.fmt" -c -t ^^^  -T -S'+ @@servername
exec master..xp_cmdshell @cmd3
set @cmd3 = 'BCP [test1].[dbo].sales2  IN  "C:\temp\sales1.txt" -f  "C:\temp\sales1.fmt"  -T -S'+ @@servername
exec master..xp_cmdshell @cmd3

--both file and xml file has the symbol ^

DECLARE @cmd5 NVARCHAR(4000) 
set @cmd5 = 'BCP  [test1].[dbo].[sales] OUT  "C:\temp\sales1.txt"  -c -t ^^^  -T  -S'+ @@servername
exec master..xp_cmdshell @cmd5
set @cmd5 = 'BCP  [test1].[dbo].sales format nul   -f  "C:\temp\sales1.xml" -x -t ^^^   -c  -T -S'+ @@servername
exec master..xp_cmdshell @cmd5
set @cmd5 = 'BCP [test1].[dbo].sales2  IN  "C:\temp\sales1.txt" -f  "C:\temp\sales1.xml"  -T -S'+ @@servername
exec master..xp_cmdshell @cmd5



--fmt file: both exported file and fmt file have the symbol ^
--Step 1
DECLARE @cmd2 NVARCHAR(4000) 
set @cmd2 = 'BCP  [test1].[dbo].[sales] OUT  "C:\temp\sales1.txt"  -c -t 0x5E  -T  -S'+ @@servername
exec master..xp_cmdshell @cmd2
set @cmd2 = 'BCP  [test1].[dbo].[sales] format nul   -f  "C:\temp\sales1.fmt" -c -t 0x5E -T -S'+ @@servername
exec master..xp_cmdshell @cmd2
set @cmd2 = 'BCP [test1].[dbo].sales2  IN  "C:\temp\sales1.txt" -f  "C:\temp\sales1.fmt"  -T -S'+ @@servername
exec master..xp_cmdshell @cmd2


DECLARE @cmd20 NVARCHAR(4000) 
set @cmd20 = 'BCP  [test1].[dbo].[sales] OUT  "C:\temp\sales1.txt"  -c -t "0x5E"  -T  -S'+ @@servername
exec master..xp_cmdshell @cmd20
set @cmd20 = 'BCP  [test1].[dbo].[sales] format nul   -f  "C:\temp\sales1.fmt" -c -t "0x5E" -T -S'+ @@servername
exec master..xp_cmdshell @cmd20
set @cmd20 = 'BCP [test1].[dbo].sales2  IN  "C:\temp\sales1.txt" -f  "C:\temp\sales1.fmt"  -T -S'+ @@servername
exec master..xp_cmdshell @cmd20



--both file and xml file has the hex value

DECLARE @cmd1 NVARCHAR(4000) 
set @cmd1 = 'BCP  [test1].[dbo].[sales] OUT  "C:\temp\sales1.txt"  -c -t\0x5E  -T  -S'+ @@servername
exec master..xp_cmdshell @cmd1
set @cmd1 = 'BCP  [test1].[dbo].sales format nul   -f  "C:\temp\sales.xml" -x -t\0x5E   -c  -T -S'+ @@servername
exec master..xp_cmdshell @cmd1
set @cmd1 = 'BCP [test1].[dbo].sales2  IN  "C:\temp\sales1.txt" -f  "C:\temp\sales.xml"  -T -S'+ @@servername


--xml file: both exported file and fmt file have the symbol ^

DECLARE @cmd50 NVARCHAR(4000) 
set @cmd50 = 'BCP  [test1].[dbo].[sales] OUT  "C:\temp\sales1.txt"  -c -t "^"  -T  -S'+ @@servername
exec master..xp_cmdshell @cmd50
set @cmd50 = 'BCP  [test1].[dbo].sales format nul   -f  "C:\temp\sales1.xml" -x -t "^"    -c  -T -S'+ @@servername
exec master..xp_cmdshell @cmd50
set @cmd50 = 'BCP [test1].[dbo].sales2  IN  "C:\temp\sales1.txt" -f  "C:\temp\sales1.xml"  -T -S'+ @@servername
exec master..xp_cmdshell @cmd50

--fmt file: both exported file and fmt file have the symbol ^
DECLARE @cmd51 NVARCHAR(4000) 
set @cmd51 = 'BCP  [test1].[dbo].[sales] OUT  "C:\temp\sales1.txt"  -c -t "^"  -T  -S'+ @@servername
exec master..xp_cmdshell @cmd51
set @cmd51 = 'BCP  [test1].[dbo].sales format nul   -f  "C:\temp\sales1.fmt" -t "^"    -c  -T -S'+ @@servername
exec master..xp_cmdshell @cmd51
set @cmd51 = 'BCP [test1].[dbo].sales2  IN  "C:\temp\sales1.txt" -f  "C:\temp\sales1.fmt"  -T -S'+ @@servername
exec master..xp_cmdshell @cmd51
exec master..xp_cmdshell @cmd1


---End of Caret ---^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^



-- 2 Pipe delimiter

---pipe |   0x7C ----||||||||||||||||||||||||||||||||


--use the the pipe symbol | with escape ^
--generate symbol for both format and data files
 
DECLARE @cmd110 NVARCHAR(4000) 
set @cmd110 = 'BCP  [test1].[dbo].[sales] OUT  "C:\temp\sales1.txt"  -c -t\^|  -T  -S'+ @@servername
exec master..xp_cmdshell @cmd110 
set @cmd110 = 'BCP  [test1].[dbo].sales format nul   -f  "C:\temp\sales1.xml" -x -t\^|   -c  -T -S'+ @@servername
exec master..xp_cmdshell @cmd110
set @cmd110 = 'BCP [test1].[dbo].sales2  IN  "C:\temp\sales1.txt" -f  "C:\temp\sales1.xml"  -T -S'+ @@servername
exec master..xp_cmdshell @cmd110

--fmt file
----    -t\^| generates symbol | for both format and data files
DECLARE @cmd7 NVARCHAR(4000) 
set @cmd7 = 'BCP  [test1].[dbo].[sales] OUT  "C:\temp\sales1.txt"  -c -t\^|  -T  -S'+ @@servername
exec master..xp_cmdshell @cmd7
set @cmd7 = 'BCP  [test1].[dbo].[sales] format nul   -f  "C:\temp\sales1.fmt" -c -t\^| -T -S'+ @@servername
exec master..xp_cmdshell @cmd7
set @cmd7 = 'BCP [test1].[dbo].sales2  IN  "C:\temp\sales1.txt" -f  "C:\temp\sales1.fmt"  -T -S'+ @@servername
exec master..xp_cmdshell @cmd7


--use hex value for both format and data file
DECLARE @cmd11 NVARCHAR(4000) 
set @cmd11 = 'BCP  [test1].[dbo].[sales] OUT  "C:\temp\sales1.txt"  -c -t\0x7C  -T  -S'+ @@servername
exec master..xp_cmdshell @cmd11
set @cmd11= 'BCP  [test1].[dbo].sales format nul   -f  "C:\temp\sales1.xml" -x -t\0x7C   -c  -T -S'+ @@servername
exec master..xp_cmdshell @cmd11
set @cmd11 = 'BCP [test1].[dbo].sales2  IN  "C:\temp\sales1.txt" -f  "C:\temp\sales1.xml"  -T -S'+ @@servername
exec master..xp_cmdshell @cmd11




--fmt file
--    -t 0x7C generates symbol for both format and data files  but not working for -t\0x7C
DECLARE @cmd4 NVARCHAR(4000) 
set @cmd4 = 'BCP  [test1].[dbo].[sales] OUT  "C:\temp\sales1.txt"  -c -t 0x7C  -T  -S'+ @@servername
exec master..xp_cmdshell @cmd4
set @cmd4 = 'BCP  [test1].[dbo].[sales] format nul   -f  "C:\temp\sales1.fmt" -c -t 0x7C -T -S'+ @@servername
exec master..xp_cmdshell @cmd4
set @cmd4 = 'BCP [test1].[dbo].sales2  IN  "C:\temp\sales1.txt" -f  "C:\temp\sales1.fmt"  -T -S'+ @@servername
exec master..xp_cmdshell @cmd4
----   -t "0x7C" generates symbol for both format and data files
DECLARE @cmd40 NVARCHAR(4000) 
set @cmd40 = 'BCP  [test1].[dbo].[sales] OUT  "C:\temp\sales1.txt"  -c -t "0x7C"  -T  -S'+ @@servername
exec master..xp_cmdshell @cmd40
set @cmd40 = 'BCP  [test1].[dbo].[sales] format nul   -f  "C:\temp\sales1.fmt" -c -t "0x7C" -T -S'+ @@servername
exec master..xp_cmdshell @cmd40
set @cmd40 = 'BCP [test1].[dbo].sales2  IN  "C:\temp\sales1.txt" -f  "C:\temp\sales1.fmt"  -T -S'+ @@servername
exec master..xp_cmdshell @cmd40



--both file and xml file has the symbol |

DECLARE @cmd8 NVARCHAR(4000) 
set @cmd8 = 'BCP  [test1].[dbo].[sales] OUT  "C:\temp\sales1.txt"  -c -t "|"  -T  -S'+ @@servername
exec master..xp_cmdshell @cmd8
set @cmd8 = 'BCP  [test1].[dbo].sales format nul   -f  "C:\temp\sales1.xml" -x  -t "|" -c  -T -S'+ @@servername
exec master..xp_cmdshell @cmd8
set @cmd8 = 'BCP [test1].[dbo].sales2  IN  "C:\temp\sales1.txt" -f  "C:\temp\sales1.xml"  -T -S'+ @@servername
exec master..xp_cmdshell @cmd8

--both file and fmt file has the symbol |

DECLARE @cmd81 NVARCHAR(4000) 
set @cmd81 = 'BCP  [test1].[dbo].[sales] OUT  "C:\temp\sales1.txt"  -c -t "|"  -T  -S'+ @@servername
exec master..xp_cmdshell @cmd81
set @cmd81 = 'BCP  [test1].[dbo].sales format nul   -f  "C:\temp\sales1.fmt"  -t "|" -c  -T -S'+ @@servername
exec master..xp_cmdshell @cmd81
set @cmd81 = 'BCP [test1].[dbo].sales2  IN  "C:\temp\sales1.txt" -f  "C:\temp\sales1.fmt"  -T -S'+ @@servername
exec master..xp_cmdshell @cmd81

-- End of     pipe |   0x7C ----||||||||||||||||||||||||||||||||






---3. Null terminator

--\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0
--use Null terminator (nonvisible terminator)  null controll character with fmt format file
DECLARE @cmd9 NVARCHAR(4000) 
set @cmd9 = 'BCP  [test1].[dbo].[sales] OUT  "C:\temp\sales9.txt"  -c -t\0  -T  -S'+ @@servername
exec master..xp_cmdshell @cmd9
set @cmd9 = 'BCP  [test1].[dbo].[sales] format nul   -f  "C:\temp\sales1.fmt" -c -t\0 -T -S'+ @@servername
exec master..xp_cmdshell @cmd9
set @cmd9 = 'BCP [test1].[dbo].sales2  IN  "C:\temp\sales9.txt" -f  "C:\temp\sales1.fmt"  -T -S'+ @@servername
exec master..xp_cmdshell @cmd9

--Null control character with XML format file
DECLARE @cmd10 NVARCHAR(4000) 
set @cmd10 = 'BCP  [test1].[dbo].[sales] OUT  "C:\temp\sales1.txt"  -c -t\0  -T  -S'+ @@servername
exec master..xp_cmdshell @cmd10
set @cmd10 = 'BCP  [test1].[dbo].sales format nul   -f  "C:\temp\sales.xml" -x -t\0   -c  -T -S'+ @@servername
exec master..xp_cmdshell @cmd10
set @cmd10 = 'BCP [test1].[dbo].sales2  IN  "C:\temp\sales1.txt" -f  "C:\temp\sales.xml"  -T -S'+ @@servername
exec master..xp_cmdshell @cmd10


--\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0



-- 4. no space  0x90  or  0x8F

--I don't have a need for this one but I have seen once from a forum question.
-- 
DECLARE @cmd31 NVARCHAR(4000) 
set @cmd31 = 'BCP  [test1].[dbo].[sales] OUT  "C:\temp\sales3.txt"  -c -t 0x90  -T  -S'+ @@servername
exec master..xp_cmdshell @cmd31


 
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