Replace double quotes (within qualifiers) in CSV for SSIS import

Question

I have a SSIS package importing data from a .csv file. This file has doulbe quotes (") qualifiers for each entry in it but also in between. I also added commas (,) as a column delimiter. I can't give you the original data I'm working with but here is an example how my data is passed in Flat File Source:

"ID-1","A "B"", C, D, E","Today"
"ID-2","A, B, C, D, E,F","Yesterday"
"ID-3","A and nothing else","Today"

As you can see the second column can contain quotes (and commas) which smashes my SSIS import with an error pointing at this line. I'm not really familiar with regular expressions, but I've heard that this might help in this case.

In my eyes I need to replace all the double quotes (") by single quotes (') except...

...all quotes at the beginning of one line
...all quotes at the end of one line
...quotes which are part of ","

Can anyone of you help me out in this thing? Would be great!

Thanks in advance!

Are you sure there are two double quotes after B and just one before it (the first row)? This does not look like a proper formatting. — Nikola Malešević
In CSV two doublequotes represents a single doublequote inside of element. Your formating is wrong or it is NOT a CSV format. Correct example would be "ID-1","A ""B"", C, D, E","Today" — Ωmega
The embedded text qualifier problem has been around for a while. I think they addressed it in SSIs 2012, but I haven't had a chance to test it yet. Here is a link to a couple solutions. My usual approach is the scripting task to clean up the file before importing (Plan A). timlaqua.com/2011/06/… — SQLMD
Unfortunatelly this is exactly the format I need to work with! I already reported it to the customer but they tell me there is nothing they can do about it. — Yheeky

Nikola Malešević Nikola Malešević · Accepted Answer · 2012-09-07T15:56:42

To replace double quotes with single quotes according to your specifications, use this simple regex. This regex will allow whitespace at the beginning and/or end of lines.

string pattern = @"(?<!^\s*|,)""(?!,""|\s*$)";
string resultString = Regex.Replace(subjectString, pattern, "'", RegexOptions.Multiline);

This is the explanation of the pattern:

// (?<!^\s*|,)"(?!,"|\s*$)
// 
// Options: ^ and $ match at line breaks
// 
// Assert that it is impossible to match the regex below with the match ending at this position (negative lookbehind) «(?<!^\s*|,)»
//    Match either the regular expression below (attempting the next alternative only if this one fails) «^\s*»
//       Assert position at the beginning of a line (at beginning of the string or after a line break character) «^»
//       Match a single character that is a “whitespace character” (spaces, tabs, and line breaks) «\s*»
//          Between zero and unlimited times, as many times as possible, giving back as needed (greedy) «*»
//    Or match regular expression number 2 below (the entire group fails if this one fails to match) «,»
//       Match the character “,” literally «,»
// Match the character “"” literally «"»
// Assert that it is impossible to match the regex below starting at this position (negative lookahead) «(?!,"|\s*$)»
//    Match either the regular expression below (attempting the next alternative only if this one fails) «,"»
//       Match the characters “,"” literally «,"»
//    Or match regular expression number 2 below (the entire group fails if this one fails to match) «\s*$»
//       Match a single character that is a “whitespace character” (spaces, tabs, and line breaks) «\s*»
//          Between zero and unlimited times, as many times as possible, giving back as needed (greedy) «*»
//       Assert position at the end of a line (at the end of the string or before a line break character) «$»

Replace double quotes (within qualifiers) in CSV for SSIS import

4 Answers

See this demo.