How to Import CSV File with Double Quotes Text Qualifiers Through bcp



--truncate table myFiles
 
DECLARE @cmd VARCHAR(4000) 
set @cmd = 'BCP  [test1].[dbo].[myFiles] format nul   -f "E:\x\data\myFormatFile.xml" -x -c -t\^",\^" -r \^"\n  -T -S'+ @@servername
exec master..xp_cmdshell @cmd
 
--Modify the format file myFormatFile.xml
 
--Add one row in the Record section by copy Row ID=1 and change the ID to 0 or 100 (something not in Row Column sextion)  and the TERMINATOR to single double quote
  
  <FIELD ID="0" xsi:type="CharTerm" TERMINATOR="\&quot;,\&quot;" MAX_LENGTH="500" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 
 
---bcp data into the table with modified format file
 
DECLARE @cmd2 NVARCHAR(4000) 
set @cmd2 = 'BCP [test1].[dbo].myFiles IN "E:\x\data\GM2_MN_Files.csv" -f  "E:\x\data\myFormatFileModified.xml" -T -t\, -F 2 -S'+ @@servername
exec master..xp_cmdshell @cmd2


 

---Option 2



--Create a view based on the target table and add one dummy column as the first column like this:
----Select null as [dummy], col1,col2...
----and save the view as bcpView for generating format file
 
--  truncate table myFiles
 
DECLARE @cmd VARCHAR(4000) 
set @cmd = 'BCP  [test1].[dbo].[bcpView] format nul   -f  "E:\x\data\myFormatFile.xml" -x -c -t\^",\^" -r \^"\n  -T -S'+ @@servername
exec master..xp_cmdshell @cmd
 
--Modify the format file myFormatFile.xml
 
-- Step 1: Chnage the TERMINATOR to escape single double quote only in first line in <RECORD> <FIELD ID=.....
--  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\&quot;" MAX_LENGTH="12"/>
---
---Step 2: and remove the first column for the target table in  <ROW>  <COLUMN> section
   
 
 
---bcp data into the table with modified format file
 
DECLARE @cmd2 NVARCHAR(4000) 
set @cmd2 = 'BCP [test1].[dbo].myFiles IN  "E:\x\data\myfilewithDoubleqoutes.csv" -f  "E:\x\data\myFormatFile.xml" -T -t\, -F 2 -S'+ @@servername
exec master..xp_cmdshell @cmd2
 
--Note: use  &quot; to represent " in the XML format file for the delimiter

 
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