Regex to match CSV file nested quotes

Question

Hi,

I know this has been discussed a million times. I tried searching through the forums and have seen some close regex expressions and tried to modify them but to no avail.

Say there is a line in a csv file like this:

"123", 456, "701 "B" Street", 910

Is there an easy regex to detect "B" (since its a non-escaped set of quotes within the normal CSV quotes) and replace it with something like \"B\" ? The final string would end up looking like this:

"123", 456, "701 \"B\" Street", 910

Help would be greatly appreciated!

Answer 1

Trust me you don't want to do this with regex. You want something like Java CSV Library.

Answer 2

There are a few zillion libraries to help you parse CSV, but if you're wanting to use a regexp for academic reasons, this may help:

• quoted string with escape support. "(\\.|[^\\"])*"
• unquoted field: [^",]*
• delimiter: , *

I don't use CSV files, so I'm not sure about the 'other csv field' validity (matching 456, for example above), or whether /, */ is the delimiter you want..

At any rate, combining the above will match one field and one delimiter (or end of string):

(quotedstring|unquoted)(delimiter|$)

Answer 3

I would use a tailored sed expression as

's/\(.*\),\(.*\),\(.*\)"\(.*\)\" \(.*\),\(.*\)/\1,\2,\3 \4 \5 \6/g'

Answer 4

Your example is not proper csv:

"123", 456, "701 "B" Street", 910

this should actually be:

"123", 456, "701 ""B"" Street", 910

(There are plenty of variations of csv, of course, but since most of the time people want it for use with excel or access I stick to the microsoft definition.)

Therefore the regex for this can look like:

".+("").+("").+"

The groups (in parentheses) will be your double quotes, and the rest ensures that they are found within another set of quotes.

That covers the find part of your needs. The replace part depends on what you are programming in.