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

Question

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?

Answer 1

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.

Answer 2

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