Post reply

BULK INSERT and embedded commas

  • June 26, 2008 at 2:56 pm

    #187639

    I have seen quite a few people with the same problem reported on various forums but not seen a definitative answer. I have an external supplier of data sent in .csv files. I can't get them to change anything about the way those files are put together. One of the columns is text with double-quotes to delimit the text. Commas for field terminators. That column sometimes has a comma embedded in the text. I'm using BULK INSERT in SQL Server 2005 to get the .csv's into a table. However, the import fails due to the embedded commas.

    Question... Still using BULK INSERT, is there any way to resolve this problem?

    I can post some examples later if but I thought I would find out if there is a solution first.

    Thanks,

    Clive

  • June 26, 2008 at 7:14 pm

    #835073

    Yes... you need to (must) use a BCP Format file to do this with Bulk Insert... the double-quote part of the delimiter you want (two charters... either ," or ",) must be "escaped" just like any other special character... In the BCP Format file, the delimiters must be surrounded by double quotes .... so, the ," would look like ",\"" and the ", delimiter would look like "\","

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply