none
Dealing with quotes in a quote encapsluated column RRS feed

  • Question

  • I know this issue has been raised here before, but I have a different angle/question on it.

    Flat File source, SQL Destination. No column from the source file has a consistent real width, just consistent to be less that the SQL column.

    Issue:

    CHAR(12), SMALLINT, CHAR(78), BIGINT

    "100012",0,"This is the ""Memo"" of the record, it needs to be imported.",12

    This row errors out due to the double quotes around the word MEMO. Additionally, the entire package stops processing due to there being a comma in the memo after the quote encapsulation is escaped.

    Has this issue been addressed in any of the SP's? If not, how about this thinking:

    1.) Character index the pattern ," and take the value of the first instance of the pattern.

    2.) Character index the pattern ", and take the LAST instance of the pattern (ignoring the pattern match after the first column).

    3.) Replace all double qoutes between the values of the indexes with single quotes.

    4.) Proceed with the Comma separated, quote encapsulated import.

     

    Any thoughts? Additionally, if this seems like it may work, does anyone have a clue how to program this in SSIS, or would I be better to run my files through a VBScript parser to accomplish this first?

     

    Thanks in advance.

     

    Tuesday, January 16, 2007 5:04 PM

All replies

  • First a question, then a thought...

    Do you in fact have two sets of double quotes around the word "memo"?

    You could try this...  Create a flat file source, using commas as the field separators.  Hook that up to a raw file destination.  Then use the raw file as your source in the data flow that actually does the work on the data and puts it into your SQL Server destination.

    That might work for you.
    Tuesday, January 16, 2007 5:11 PM
    Moderator
  • Phil,

     

    Thanks for your thoughts.

     

    Can you expand on this concept a little more? I've never used the raw file destination and am a little befuddled as to how this would work exactly.

     

    Thanks in advance for your input.

    Tuesday, January 16, 2007 5:18 PM
  • Just create a data flow, setup your flat file source, and then add a raw file destination component. Hook the two together. (Source -> destination)

    Then using the data flow you already have created, replace the flat file source with a raw file source component. Point it to the raw file created in the previous step. Continue as you normally would.

    In your control flow, you would hook the two data flows together so that the first data flow creates the raw file, and then the second data flow actually uses the raw file for processing.

    Note - There are other ways of doing this, and others will undoubtedly chime in.  SSIS provides many ways of tackling a problem, and I've only illustrated one that should work and that would be easy to implement given a beginner such as yourself.
    Tuesday, January 16, 2007 5:21 PM
    Moderator
  • OK, I gave it a try, but the format of the raw file output is all jacked up with a ton of unknown characters represented as a box and when I try to connect to it as a flat file source, it errors that it cannot find a row delimiter.

     

    Or am just missing something obvious? Like I said, I've never used raw file outputs.

     

    Thanks again.

    Tuesday, January 16, 2007 5:45 PM
  •  Narizz28 wrote:

    OK, I gave it a try, but the format of the raw file output is all jacked up with a ton of unknown characters represented as a box and when I try to connect to it as a flat file source, it errors that it cannot find a row delimiter.

    Or am just missing something obvious? Like I said, I've never used raw file outputs.

    Thanks again.



    Okay.  Here, follow EXACTLY as I illustrate.

    Data Flow #1:  Create a FLAT FILE SOURCE.  Hook it to a RAW FILE DESTINATION.  You should be able to validate that this works by looking at the PREVIEW of the FLAT FILE SOURCE. 

    Data Flow #2: Create a RAW FILE SOURCE.  Hook it up to whatever transformations you need to do on the data.  Ultimately, you'll end up (perhaps) with an OLE DB DESTINATION.

    Sounds to me like you used a FLAT FILE SOURCE in Data Flow #2 to point to the RAW FILE created in Data Flow #1.  DON'T DO THIS.  Use a RAW FILE SOURCE.  It's listed in your available source components in your toolbox.
    Tuesday, January 16, 2007 5:50 PM
    Moderator
  • My bad, that's exactly what I did. I'll try it again now.

     

    Thanks for pointing this out (It's so easy to overlook the obvious sometimes...)

    Tuesday, January 16, 2007 6:07 PM