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

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?

Wow, I expected to be able to answer with "Just set the text qualifier", but figured you would have already tried that so I tried it before I answered. Surprise, SSIS doesn't support standard CSV files!

Looks like this is a common complaint. There is one comment in there from Microsoft about some samples that may help; Here is the codeplex project, they mentioned that the Regular Expression Flat File Source sample and the Delimited File Reader Source sample in particular may help -- I'm guessing the Delimited File Reader would be more worthwhile.

I ran into a similar problem yesterday.

We got the csv file that using comma , as delimiter and double quote " as text qualifier, but there is a field that contain double quote within double quote(non-escaped double quote within a string).

After spending half day searching, came up with the solution below:

// load the file into a one dimensional string array.
// fullFilePath is the full path + file name.
var fileContent = File.ReadAllLines(fullFilePath);

// Find double quotes within double quotes and replace with a single quote
var fileContentUpdated = fileContent.Select(
    x => new Regex(@"(?<!^)(?<!\,)""(?!\,)(?!$)"
    ).Replace(x, "'")).ToArray();

// write the string array into the csv file.
File.WriteAllLines(fullFilePath, fileContentUpdated);

I don't see any other way than replace the double quote with something else to avoid the issue.

This answer is not applicable to 2005 as referenced here, but in case someone comes across this while searching and is using 2008, this other question appears to have a working answer: SSIS 2008 and Undouble

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

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

   

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("\"", "''");
    }

Probably old news now, but this issue was fixed in SQL Server 2012. I was able to import the same file on a 2012 server that failed on my 2008 server.