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.