Post reply

Multiple double quotes in a csv

  • June 7, 2011 at 2:05 pm

    #252278

    Ive been tasked with imported a csv file (comma seperated) and fields which contain text are delimited by a double quote ". No problems here are i can just change the text delimited on the source file.

    The problem arises when there are quotes in the text field, for example quotes from books. The field looks as follows.

    """This is some sample text from a book, doesnt help does it?""","Next field"

    Is there any way this can be imported through SSIS as just the two fields? using " as the text delimiter it wants to split the file out into 3 fields.

    Any advice or tips would be greatly received

    John

  • June 7, 2011 at 2:13 pm

    #1335305

    is every field dbl quote delimited, regardless of value being integer, decimal or string, or just strings?

    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!

  • June 7, 2011 at 2:25 pm

    #1335317

    And, of course, it can have commas in the text too, can't it?

    I think I'd build a custom import engine for this. Any of the various .NET languages should be able to easily split your text up into columns using the definitions you need.

    Alternately, and worth looking into, would be to import the data into a single text column, then parse it using a multi-character delimiter, then Pivot it into columns.

    .NET in the SSIS package will probably be best, but alternatives might be worth testing.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • June 7, 2011 at 3:33 pm

    #1335372

    Lowell (6/7/2011)

    is every field dbl quote delimited, regardless of value being integer, decimal or string, or just strings?

    No, only the text delimited fields can be like this and even then only ones which contain a quote have the """ 'feature'

  • June 7, 2011 at 3:35 pm

    #1335374

    GSquared (6/7/2011)

    Alternately, and worth looking into, would be to import the data into a single text column, then parse it using a multi-character delimiter, then Pivot it into columns.

    That is something which is definately worth looking into which a hadnt thought about, thanks 🙂

  • June 8, 2011 at 4:54 pm

    #1336005

    GSquared (6/7/2011)

    Alternately, and worth looking into, would be to import the data into a single text column, then parse it using a multi-character delimiter, then Pivot it into columns.

    this came in handy recently when I was tackling a similar issue. import as single column and handle parsing logic via script component.

    Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com

  • June 9, 2011 at 4:23 am

    #1336148

    An OPENROWSET through either JET or ACE drives will handle this automatically.

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

  • June 9, 2011 at 4:45 am

    #1336160

    Jeff Moden (6/9/2011)

    An OPENROWSET through either JET or ACE drives will handle this automatically.

    Thanks Jeff. ill take a look at that also.

    I think i have managed to persuade the vendors who produce the source file to output in tab seperated (i asked for pipe but they said its comma or tab). I think once its tab seperated it will work fine.

  • June 9, 2011 at 4:56 am

    #1336165

    Animal Magic (6/9/2011)

    Jeff Moden (6/9/2011)

    An OPENROWSET through either JET or ACE drives will handle this automatically.

    Thanks Jeff. ill take a look at that also.

    I think i have managed to persuade the vendors who produce the source file to output in tab seperated (i asked for pipe but they said its comma or tab). I think once its tab seperated it will work fine.

    You are a genius. Seriously. I keep telling people that if you don't like the format that a vendor is sending you, talk and work with the vendor. You're one of the very few people on this entire forum that I've seen take the time to do that. Very, very well done.

    --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 9 posts - 1 through 8 (of 8 total)

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