Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    97

    Unanswered: Bulk Insert with Double Quotes in one field

    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.

  2. #2
    Join Date
    Mar 2003
    Posts
    97
    Any thoughts?

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Use a CSV import tool, like SSIS instead of Bulk Insert to deal with complex CSV formats (that use quotes or escape characters).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Mar 2003
    Posts
    97
    SSIS is overkill for this. I just need to tweak the format file to accept double quotes for one field. Is that not possible?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •