Is there a way to escape a double quote within a text qualified string on a SSIS Csv import?

user_62068 0
Viewed 20793

I have a CSV I'm trying to import into SQL using SSIS packages through code. A line might look something like this

321,1234,"SOME MACHINE, MACHINE ACCESSORIES 1 1/2"" - 4"""

In this example they're using a double quote to symbolize inches. They are trying to escape the inches double quote with a double quote. SSIS, however, does not honour this escapism and fails.

Is there anyway I can still use the double quote symbol for inches and escape it within the quoted text?

Many suggestions are to replace the double quote with two single quotes. Is this the only work around or can I use some other escape technique?

I've seen people talk about using the Derived Column transformation but in my case SSIS fails at the Flat File Source step and I therefore cannot get to a derived column transform step.

I'm currently running a script task in the control flow, just before the data flow, to manipulate the Csv with some regex's to cleanup the data.

I need the string to be text qualified with the 2 outer double quotes because of potential commas in the description column.

What can I do about the double quotes within the text qualified string?

ssis sql-server-2005 sql-server
6 Answers

There is a workaround if in the File connection you remove the " as text qualifier you can remove all the double quotes later with a derived column expression REPLACE(Item_Name,"\"",""). The downside is that you will need to do it for every field

user_86362470

I didn't find a direct way to achieve this so I wrote a script:

  1. Add a Script component to the workflow (make sure to connect the input arrow or it won't recognize the columns)
  2. Right click on the Script component -> Input Columns, change the column Usage Type to READWRITE enter image description here
  3. Click Ok
  4. Edit the Script, replace double quotes with two single quotes
  public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        var descr = Row.Description;
        Row.Description = Row.Description.Replace("\"", "''");
    }
user_35964410
Related