none
Truncation error arrising from quote escaped quotes in quote encapsulated strings RRS feed

  • Question

  • Hi All,

     

    I'm having trouble importing a CSV file into SQL using SSIS. The trouble seems to stem from truncation and from quote encapsulated fields.

     

    Firstly it's worth noting that some of the data within the quotes contains the separator. E.g.

     

    12,"some text, and a comma",34

     

    Thankfully SSIS seems to cope with that by specifying " as the Text Qualifier. My next problem was that the SSIS Import and Export Wizard gave an error: "failed because truncation occurred". But I fixed this by specifying the OutputColumnWidth for the NCHAR and NVARCHAR type columns to be the width from the table definition.

     

    But now I have another problem with the length of fields. Consider the following where the center column is NCHAR (22)

     

    101,"some text, and a comma",303

    102,"some ""quoted text"" bye",303

     

    The first row has the correct width (once the encapsulating quotes are stripped out), but the second row does not, because it seems that when I exported the table the export wizard escaped the quote characters within encapsulated strings using quote characters. So I am back to getting the "truncation occurred" error. How can I get around this within SSIS?

     

    Cheers,

     

    Tim.

    Wednesday, February 15, 2006 5:48 PM

Answers

  • Unfortunately, the flat file parser in SSIS does not know how to process embedded quotes.

    There is no a great workaround, but you might want to preprocess your file and turn embedded quotes into some other character (apostrophes’ maybe).

    I am sorry for the inconvenience. We should be able to fix this in the future. I am sure it is already reported as a bug.

    Thursday, February 16, 2006 1:22 AM