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: 172542

    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: http://brandietarvin.livejournal.com/[/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: 994863

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • widlund_martin

    Newbie

    Points: 7

    What worked for me was changing

    ROWTERMINATOR = '\n'

    to

    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