Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Jeff Moden Skipping Columns (& other tricks) Using BCP Format Files (SQL Spackle) RE: Skipping Columns (& other tricks) Using BCP Format Files (SQL Spackle)

  • January 23, 2014 at 7:15 pm

    #1682984

    Andy DBA (1/23/2014)

    Jeff Moden (1/23/2014)

    If the quotes are consistent in the column, then you are, in fact, missing out on a format file option that MS didn't document because they "don't support CSVs". \" is what you use as part of the FIELDTEMINATOR column to represent double quotes. Importing such CSVs using BCP and BULK INSERT will be the subject of another article.

    Of course! Yes, I do remember coming across the approach where double quotes are included as part of the field terminator. There's also a way to "eat" the leading double quote from the first column, but I'll wait for your next article on the topic. I was able to take an approach where I didn't need format files, but I guess what I was really hoping for is a Magically-treat-double quotes-like-Excel-does command line option!:-D

    The old 32 bit "Jet" drivers used to work a treat for exactly that using OPENROWSET. Ii've not tried the 64 bit ACE drivers but rumor has it they they work just as well. The problem with OPENROWSET is that it requires some serious privs whereas BULK INSERT does not.

    --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)