Post reply

Importing from CSV file

  • October 15, 2007 at 11:52 am

    #118964

    I have a file that is CSV, but each value is wrapped in quotes (because some of the text contains commas)

    So it's like this

    "ABC","some text","some text,with a comma","I"

    "DEF","some text","some text,with a comma","F"

    I'd like to be able to use:

    SELECT A.*

    FROM

    OPENROWSET (BULK '\\PFC-IMAGE-SRVR\D$\DST_IMPORT\OTI.CSV',

    FORMATFILE = '\\PFC-IMAGE-SRVR\D$\DST_IMPORT\FORMAT\DST_FORMAT_FILE.fmt') AS A

    But It of course blows up if there is a comma in the text (and there almost always is). It would also be nice if I could parse out the quotes arround everything in one step, but that is easily accomplished after the import if needed.

    Here is a start for a format file (attached) (the tabs get messed up, sorry). Any advice on how to deal with the embedded commas would be greatly appriciated...

    Thanks,

    Jason

    The Redneck DBA

  • October 15, 2007 at 12:03 pm

    #742098

    take look at this link;

    http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file

    basically, they are using dblquote-comma-dblquote as the field delimiter, and dblquote-slash-n as the row terminator.

    that just leaves removing the preceeding dbl-quote for cleanup.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 15, 2007 at 8:52 pm

    #742203

    Please see the following... leaves no quotes to cleanup...

    http://www.sqlservercentral.com/Forums/Topic296166-8-1.aspx#BM296961

    --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 3 posts - 1 through 2 (of 2 total)

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