Apr 3, 2009, 7:50 AM
Post #1 of 2
Need solution for replacing commas in a comma separated string
I have a comma (,) separated csv file as input to the process. Now in some of the lines in that csv, there are fields which are enclosed between "" but there's a comma in the field value too. For example, a CSV record could look like:
MOD,USD,22-Oct-09,Pay,USNY,"11.21 partial, 1.25mm remains",FALSE,,MOD,USD
If you look at the field in Bold, it's enclosed in "", but there's a comma in the value. Now when we split this whole record using comma (,) as the separator, the split function is not able to differentiate between the CSV comma separator and the comma in field value. Hence it recognizes (11.21 Partial) and (1.25mm remains) as different fields. This totally screws up our backend process. Can somebody suggest a generic regex here, which can replace any comma that appears in the "" fields by a colon (;) ? Please note that there could be multiple "" fields with commas inside in a CSV record. For example:
MOD,USD,22-Oct-09,Pay,USNY,"11.21 partial, 1.25mm remains",FALSE,"paint 22, leave 43",MOD,"drive neo, zion is falling",USD
Thanks in advance for a solution.