Regex, csv-type layout, allow for "" inside quoted strings?

Question

I need a regex that will parse a csv-style file, something like 57 fields wide, most fields enclosed in quotes (but maybe not all), separated by commas, with quoted fields having the possibility of embedded doubles ("") that represent single quotes in the evaluated string.

I'm a regex beginner/intermediate, and I think I can get pretty quickly to the basic expression to do the field parsing, but it's the embedded double-quotes (and commas) I can't get my head around.

Anyone? (Not that it matters but specific language is Matlab.)

Answer 1

escape the quotes - ? makes it optional.

\"?

Answer 2

I know there i great hype around regular expressions nowadays, but I would really recommend using a library for tasks that have already been implemented by others - it will be easier to implement, easier to read and easier to maintain (want to read csvs separated by quotes next time? The library can possibly do it, but your regex will need a rewrite). A quick google search should give you a good start.

Answer 3

If you really have to do it with a regex, I would do it in two passes; firstly separate the fields by splitting on the commas with something such as:

regexp(theString, '(?<!\\),', 'split');

This should split on commas, only when there isn't a preceding slash (I'm assuming this is what you mean by escaped commas). (I think in matlab you'll end up with an array of indexes into the original strings)

Then you should check each matched field for escaped quotes, and replace them with something like:

regexprep(individualString, '""', '"');

Similarly for commas:

regexprep(individualString, '\\,', ',');

I'm not sure about the doubly escaped \'s in matlab having not had much experience with it.

As others have said, it's probably better to use a csv library for handling the initial file.

Answer 4

It took me a while to work this out, since many of the regexp's on the net don't handle one part or another. Here is code in F#/.NET. Sorry, but I don't speak matlab:

let splitCsv (s:string) =
    let re = new Regex("\\s*((?:\"(?:(?:\"\")|[^\"])*\")|[^\"]*?)\\s*(?:,|$)")

    re.Matches( s + " ")
    |> Seq.cast<Match>
    |> Seq.map (fun m -> m.Groups.[1].Value)
    |> Seq.map (fun s -> s.Replace( "\"\"", "\"" ))
    |> Seq.map (fun s -> s.Trim( [| '"'; ' ' |] ))
    |> List.of_seq

This version handles quoted strings, quotes escaped as double-quotes, and trims extra (escaped) quotes and spaces around the whole string (original: "Test", double-quoted: """Test"""). It also properly handles an empty field in the last position (hence the s + " ") and it also properly handles commas inside quoted strings.

Answer 5

Thanks for replies. Classic case of beginner thinking the problem is easy, experts knowing the problem is hard.

After reading your posts, I browsed for a canned csv parser library in Matlab; found a couple, neither of which could get the job done (first tried to do whole file at once, failed on memory; second failed to my specific bugaboo, doubled quotes in a quoted string).

So we rolled our own, with the help of a regex I found on the web and modified. Remains to be moved to Matlab but Python code is as follows:

import re

text = ["<omitted>"]

# Regex: empty before comma OR string w/ no quote or comma OR quote-surrounded string w/ optional doubles
p = re.compile('(?=,)|[^",]+|"(?:[^"]|"")*"')

for line in text:
    print 'Line: %s' % line
    m = p.search(line)                                  
    fld = 1
    while m:                                            
        val = m.group().strip('"').replace('""', '"')   
        print 'Field %d: %s' % (fld, val)
        line = re.sub(p, '', line, 1)        
        if line and line[0] == ',':          
            line = line[1:]
        fld += 1
        m = p.search(line)                   
    print

Answer 6

Page 271 of Friedl's Mastering Regular Expressions has a regular expression for extracting possibly quoted CSV fields, but it requires a bit of postprocessing:

>>> re.findall('(?:^|,)(?:"((?:[^"]|"")*)"|([^",]*))', '"a,b,c",d,e,f')
[('a,b,c', ''), ('', 'd'), ('', 'e'), ('', 'f')]
>>> re.findall('(?:^|,)(?:"((?:[^"]|"")*)"|([^",]*))', '"a,b,c",d,,f')
[('a,b,c', ''), ('', 'd'), ('', ''), ('', 'f')]

Same pattern with the verbose flag:

csv = re.compile(r"""
    (?:^|,)
    (?: # now match either a double-quoted field
        # (inside, paired double quotes are allowed)...
        " # (double-quoted field's opening quote)
          (    (?: [^"] | "" )*    )
        " # (double-quoted field's closing quote)
    |
      # ...or some non-quote/non-comma text...
        ( [^",]* )
    )""", re.X)

Answer 7

It's possible to do using a single regex with lookahead. Illustrated here in perl:

my @rows;

foreach my $line (@lines) {

    my @cells;
    while ($line =~ /( ("|').+?\2 | [^,]+? ) (?=(,|$))/gx) {
        push @cells, $1;
    }

    push @rows, \@cells;
}