CSV file with embedded commas

I haven't found a solution to that (and would look forward to hearing from anyone who has). What we were able to do was get the outside agency to send it as pipe | delimited file rather than comma delimited. That took care of the problem for us.

The best solution I've used is regex (regular expression) in JavaScript. It's very powerful for parsing any type of string data. Not easiest thing to learn/grasp but it works well for what you're trying to do (trust me, I've been down this road

Terry,

If you are familar with regular expressions and Javascript, you could write one to look for the pattern [space comma] [comma space] and [space comma space]. You could put the code inside of an assign node after each pass for the record. Because most CSV files do not have a leading or trailing space for each field, if you found a match to your expression this would most likely be a comma inserted in somewhere in the data (in between the quotes). You would then replace the matches with just one space. I would run a check to make sure before committing any changes, but that should help get rid of the unwanted commas.

Good suggestions. I like the last one. I could do a replace on the entire record and do a replace ", " with " ". This is then before I parse the individual fields. I'll post the solution if it works.

That worked!

I added an Assign between the two data iterator activities to a new variable with this code:

CSVFile_outputData.replace(/, /g," ")

Then I replaced the input for the second one to use this new variable.