bcp Export/Import wihtout FORMAT File


If we can control the format to export data to a file, we can use the same format to import the data file without using format file. Fomat file is useful to work with files that are not formatted as we expected.
We can define our own delimiters inside double quotes to make them safe to use.




--Column delimiter with arbitrary [||]
--Row delimiter with arbitrary ` plus regular CRLF

--We don't need  a format file with our own delimiters

DECLARE @cmd1 NVARCHAR(4000) 
set @cmd1 = 'BCP [test1].[dbo].[myFiles] OUT  "C:\temp\myexport1.csv" -e c:\temp\errorfile.txt -c -T -t"[||]" -r "`"\n -F 1 -S'+ @@servername  
exec master..xp_cmdshell @cmd1

DECLARE @cmd2 NVARCHAR(4000) 
set @cmd2 = 'BCP [test1].[dbo].[myFilesNew] in  "C:\temp\myexport1.csv" -e c:\temp\errorfile.txt -c -T -t"[||]" -r"`"\n -F 1 -S'+ @@servername
exec master..xp_cmdshell @cmd2


  
--Column delimiter with arbitrary ","  douvle quotes and comma   the file will miss the leading  quote but it will import OK with the same delimiters
--Row delimiter with arbitrary ` plus regular CRLF

DECLARE @cmd11 NVARCHAR(4000) 
set @cmd11 = 'BCP [test1].[dbo].[myFiles] OUT  "C:\temp\myexport1.csv" -e c:\temp\errorfile.txt -c -T -t\^",\^" -r "`"\n -F 1 -S'+ @@servername  
exec master..xp_cmdshell @cmd11

DECLARE @cmd21 NVARCHAR(4000) 
set @cmd21 = 'BCP [test1].[dbo].[myFilesNew] in  "C:\temp\myexport1.csv" -e c:\temp\errorfile.txt -c -T -t\^",\^" -r"`"\n -F 1 -S'+ @@servername
exec master..xp_cmdshell @cmd21


--truncate table [test1].[dbo].[myFilesNew]



---Reference: Lowell's
---http://www.sqlservercentral.com/Forums/Topic1315301-146-1.aspx

 
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