RegEx - Parse Csv Text

Question

So there are loads of posts on here that note rather than rolling my own csv parser I ought to use either the Vb.Net TextFiledParser.

I tried it but, and please tell me if I'm wrong, it'll parses based on a single delimeter.

So if I have an address field "Flat 1, StackOverflow House, London" I get three fields. Unfortunately that's not what I want. I need everything in a given cell to remain as a single item in the array.

So I started to write my own RegEx as follows :

var testString = @"""Test 1st string""" + "," + @"""Flat 1, StackOverflow House, London, England, The Earth""" + "," + "123456";

var matches = Regex.Matches(chars, @"""([^""\\])*?(?:\\.[^""\\]*)*?""");
var numbers = Regex.Matches(chars, @"\d+$");//only numbers
Assert.That(results.Count(), Is.EqualTo(3));
Assert.That(secondMatch.Count, Is.EqualTo(1));

The first assertion fails as the string "123456" is not returned. The expression only returns "Test 1st string" and "Flat 1, StackOverflow House, London, England, The Earth"

What I'd like is for the regex to return everything quoted\escaped, and numbers.

I don't control the data but figure strings will all be quoted\escaped and numbers won't.

I'd really appreciate some help as I'm going around in circles trying third party libraries without much success.

Needless to say string.split doesn't work in the case of addresses, and http://www.filehelpers.com/ doesn't seem to account for such examples.

Using regexes to parse CSV is indeed very messy, especially if you don't control your input. So unless you can guarantee that there won't be any escaped quotes inside strings, then you'll have a very hard time getting a regex to do this job reliably. — Tim Pietzcker, May 09 '12 at 13:12

score 2 · Answer 1 · answered May 09 '12 at 13:27

Just to give you an idea what you're up against: Here's a regex that should work quite well. But you definitely need to test the heck out of it since there are so many corner cases with CSV that I'm sure to have missed some (And I'm assuming the comma as the separator and " as quote character (which is escaped by doubling)):

(?:           # Match either
 (?>[^",\n]*) #  0 or more characters except comma, quote or newline
|             # or
 "            #  an opening quote
 (?:          #  followed by either
  (?>[^"]*)   #   0 or more non-quote characters
 |            #  or
  ""          #   an escaped quote ("")
 )*           #  any number of times
 "            #  followed by a closing quote
)             # End of alternation
(?=,|$)       # Assert that the next character is a comma (or end of line)

In VB.NET:

Dim ResultList As StringCollection = New StringCollection()
Dim RegexObj As New Regex(
    "(?:            # Match either" & chr(10) & _
    " (?>[^"",\n]*) #  0 or more characters except comma, quote or newline" & chr(10) & _
    "|              # or" & chr(10) & _
    " ""            #  an opening quote" & chr(10) & _
    " (?:           #  followed by either" & chr(10) & _
    "  (?>[^""]*)   #   0 or more non-quote characters" & chr(10) & _
    " |             #  or" & chr(10) & _
    "  """"         #   an escaped quote ("""")" & chr(10) & _
    " )*            #  any number of times" & chr(10) & _
    " ""            #  followed by a closing quote" & chr(10) & _
    ")              # End of alternation" & chr(10) & _
    "(?=,|$)        # Assert that the next character is a comma (or end of line)", 
    RegexOptions.Multiline Or RegexOptions.IgnorePatternWhitespace)
Dim MatchResult As Match = RegexObj.Match(SubjectString)
While MatchResult.Success
    ResultList.Add(MatchResult.Value)
    MatchResult = MatchResult.NextMatch()
End While

@loldop: Hey, if it's good enough for Joel Spolsky, it's definitely good enough for me :) — Tim Pietzcker, May 10 '12 at 11:33
@TimPietzcker however, it's my opinion, cause i spend too much time for programming on VB (mostly VBA) on last weekend :\ . How i said before, your answer is good, yeah :) And i think, author should be more polite and accept it. — gaussblurinc, May 10 '12 at 11:50

score 0 · Answer 2 · edited May 23 '17 at 11:56

A hacky way that I used to quickly get round it was to first Split by quotation marks, then in between every other index, strip out the quotes (or replace them with something). Then Split the string again on the commas

Just found this: Javascript code to parse CSV data - I appreciate that it's JavaScript and not vb.net. However, you should be able to follow it

Also How can I parse a CSV string with Javascript, which contains comma in data?

RegEx - Parse Csv Text

2 Answers2