Import CSV with text qualifier

  • Casper101

    Hall of Fame

    Points: 3880

    I have a comma separated CSV file with double quotes ( " ) as my text qualifier.

    I need to create a script to import this file into a table.

    BULK INSERT does not work because it does not have a text qualifier indicator.

    What are my options? How can I import this file using a script (it has to be scripted....) This has to be automated on a SQL express machine - there is not VS installation so i can not create a SSIS package.

  • Brandie Tarvin

    SSC Guru

    Points: 172754

    Not sure you need VS installed to create an SSIS package. The SSIS Import / Export wizard could create the package for you. You would, however, need to have SSIS installed (and the service running) in order to run the package at need.

    Have you looked at the BCP Utility? I don't use it, so I don't know if that will work for you or not.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog:[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Jeff Moden

    SSC Guru

    Points: 996482

    BCP and BULK INSERT are virtually identical now-a-days. Neither will handle true CSV (with text qualifiers, etc) without a bit of work. If the text qualifiers are used consistently from row-to-row including the header of the file, it is possible to make a BCP Format File that will suss the problem.

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

  • widlund_martin


    Points: 7

    What worked for me was changing



    ROWTERMINATOR = '0x0a'

    in the BULK INSERT's WITH-statement .

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

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