Bulk Insert with Double Quotes in ONE field only

Last Post 17 Jan 2012 10:08 AM by Brett. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Balance
New Member
New Member

--
12 May 2011 08:14 AM
Hello,

I'm doing a bulk insert where each field is separated by a comma except one field is separated with double quotes since the field contains commas in its value.

This is what the data file looks like (first row are columns and second row is data):

type,orig,rcpt,dlvSourceIp,vmta,header_x-cid,bounceCat,dsnDiag,timeQueued,jobID,timeLogged
t,support@acme.com,test@gmail.net,5.100.9.4,test1, ,bad-mailbox,"smtp;450 Recipient Rejected: Inactive mailbox, please try again later",2011-05-11 10:12:30-0500,,2011-05-11 12:20:35-0500

Here's my format file:

10.0
11
1 SQLCHAR 0 1 "," 1 type SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 250 "," 3 orig SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 250 "," 4 rcpt SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 50 "," 5 dlvSourceIP SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 150 "," 6 vmta SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 0 "," 7 header_x-cid SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 50 "\"" 8 bounceCat SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 250 "\"," 9 dsnDiag SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 0 "," 10 timeQueued SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 50 "," 12 jobID SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 0 "\r\n" 2 timeLogged SQL_Latin1_General_CP1_CI_AS


Here's my T-SQL:

BULK INSERT CSVFile 
FROM 'C:\test.csv' 
WITH

FIRSTROW = 2,
formatfile = 'C:\testCSV.fmt'
)

When I run this, 0 rows are added to the table. If I replace "\"" with "," and "\"," with "," the data is inserted into the table but the value of the dsnDiag field following the comma is shifted to the next field.

I've looked all over the net without avail. Any help is much appreciated.
Brett
New Member
New Member

--
17 Jan 2012 10:08 AM
Do a REPLACE ALL in the file?

OR use REPLACE after the data is loaded?

Make them all ","


Acceptable Use Policy
---