CSV Regex split missing columns

Question

I'm trying to split a CSV input using the following regex:

(?:^|,)(?=[^"]|(")?)"?((?(1)[^"]*|[^,"]*))"?(?=,|$)

A line/row with the data ,a,b,c results in 3 matches:

2. ,b
3. ,c

I'm losing/missing the ,a and I can't figure out what needs to change.

It seems to work using the Python option: https://regex101.com/r/kW3pQ6/1

Any idea how to fix it for .NET?

This might help:

(?:^|,)(?=[^"]|(")?)"?((?(1)[^"]*|[^,"]*))"?(?=,|$)

Debuggex Demo

Answer 1

As others have suggested you should be using a class whose purpose it is to parse a CSV string. The TextFieldParser class is built into .NET. Unless you have additional requirements not mentioned in your question its probability not necessary to use an external library.

using(MemoryStream stream = new MemoryStream())
using(StreamWriter writer = new StreamWriter(stream))
{
    writer.Write(s);
    writer.Flush();
    stream.Position = 0;

    using(TextFieldParser parser = new TextFieldParser(stream)){
        parser.TextFieldType = FieldType.Delimited;
        parser.Delimiters = new string[] {","};
        parser.HasFieldsEnclosedInQuotes = true;

        while(!parser.EndOfData){ //Loop through lines until we reach the end of the file
            string[] fields = parser.ReadFields(); //This will contain your fields
        }
    }
}

https://msdn.microsoft.com/en-us/library/microsoft.visualbasic.fileio.textfieldparser%28v=vs.110%29.aspx

Answer 2

Why not use a Csv NuGet package that takes into account the many nuances of CSV parsing that you are trying to solve now and others of which you don't know you need to solve yet :-)

CsvHelper is a very popular OS package:
https://www.nuget.org/packages/CsvHelper
https://github.com/JoshClose/CsvHelper

Answer 3

Yes, I know regex is not the "right" answer, but it is what the question asked for and I like a good regex challenge.

NOTE: Though the solution below can likely be adapted for other regex engines, using it as-is will require that your regex engine treats multiple named capture groups using the same name as one single capture group. (.NET does this by default)

---

When multiple lines/records of a CSV file/stream (matching RFC standard 4180) are passed to the regular expression below it will return a match for each non-empty line/record. Each match will contain a capture group named Value that contains the captured values in that line/record (and potentially an OpenValue capture group if there was an open quote at the end of the line/record).

Here's the commented pattern (test it on Regexstorm.net):

(?<=\r|\n|^)(?!\r|\n|$)                       // Records start at the beginning of line (line must not be empty)
(?:                                           // Group for each value and a following comma or end of line (EOL) - required for quantifier (+?)
  (?:                                         // Group for matching one of the value formats before a comma or EOL
    "(?<Value>(?:[^"]|"")*)"|                 // Quoted value -or-
    (?<Value>(?!")[^,\r\n]+)|                 // Unquoted value -or-
    "(?<OpenValue>(?:[^"]|"")*)(?=\r|\n|$)|   // Open ended quoted value -or-
    (?<Value>)                                // Empty value before comma (before EOL is excluded by "+?" quantifier later)
  )
  (?:,|(?=\r|\n|$))                           // The value format matched must be followed by a comma or EOL
)+?                                           // Quantifier to match one or more values (non-greedy/as few as possible to prevent infinite empty values)
(?:(?<=,)(?<Value>))?                         // If the group of values above ended in a comma then add an empty value to the group of matched values
(?:\r\n|\r|\n|$)                              // Records end at EOL

Here's the raw pattern without all the comments or whitespace.

(?<=\r|\n|^)(?!\r|\n|$)(?:(?:"(?<Value>(?:[^"]|"")*)"|(?<Value>(?!")[^,\r\n]+)|"(?<OpenValue>(?:[^"]|"")*)(?=\r|\n|$)|(?<Value>))(?:,|(?=\r|\n|$)))+?(?:(?<=,)(?<Value>))?(?:\r\n|\r|\n|$)

[Here is a visualization from Debuggex.com][3] (capture groups named for clarity): ![Debuggex.com visualization][4]

Examples on how to use the regex pattern can be found on my answer to a similar question here, or on C# pad here, or here.