Regex to split a CSV

by

Description

Instead of using a split, I think it would be easier to simply execute a match and process all the found matches.

This expression will:

  • divide your sample text on the comma delimits
  • will process empty values
  • will ignore double quoted commas, providing double quotes are not nested
  • trims the delimiting comma from the returned value
  • trims surrounding quotes from the returned value
  • if the string starts with a comma, then the first capture group will return a null value

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

enter image description here

Example

Sample Text

123,2.99,AMO024,Title,"Description, more info",,123987564

ASP example using the non-java expression

Set regEx = New RegExp
regEx.Global = True
regEx.IgnoreCase = True
regEx.MultiLine = True
sourcestring = "your source string"
regEx.Pattern = "(?:^|,)(?=[^""]|("")?)""?((?(1)[^""]*|[^,""]*))""?(?=,|$)"
Set Matches = regEx.Execute(sourcestring)
  For z = 0 to Matches.Count-1
    results = results & "Matches(" & z & ") = " & chr(34) & Server.HTMLEncode(Matches(z)) & chr(34) & chr(13)
    For zz = 0 to Matches(z).SubMatches.Count-1
      results = results & "Matches(" & z & ").SubMatches(" & zz & ") = " & chr(34) & Server.HTMLEncode(Matches(z).SubMatches(zz)) & chr(34) & chr(13)
    next
    results=Left(results,Len(results)-1) & chr(13)
  next
Response.Write "<pre>" & results

Matches using the non-java expression

Group 0 gets the entire substring which includes the comma
Group 1 gets the quote if it’s used
Group 2 gets the value not including the comma

[0][0] = 123
[0][1] = 
[0][2] = 123

[1][0] = ,2.99
[1][1] = 
[1][2] = 2.99

[2][0] = ,AMO024
[2][1] = 
[2][2] = AMO024

[3][0] = ,Title
[3][1] = 
[3][2] = Title

[4][0] = ,"Description, more info"
[4][1] = "
[4][2] = Description, more info

[5][0] = ,
[5][1] = 
[5][2] = 

[6][0] = ,123987564
[6][1] = 
[6][2] = 123987564

Edited

As Boris pointed out CSV format will escape a double quote " as a double double quote "". Although this requirement wasn’t included by the OP, if your text includes double double quotes then you’ll want to use a this modified expression:

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

See also: https://regex101.com/r/y8Ayag/1

It should also be pointed out that Regex is a pattern matching tool not a parsing engine. Therefore if your text includes double double quotes it will still contain the double double quotes after pattern matching is completed. With this solution you’d still need to search for the double double quotes and replace them in your captured text.

More Related Contents:

  1. bash regex with quotes?
  2. Regex for quoted string with escaping quotes
  3. Regex for Comma delimited list
  4. Split string on commas but ignore commas within double-quotes?
  5. R split on delimiter (split) keep the delimiter (split)
  6. JavaScript: split doesn’t work in IE?
  7. How can I parse quoted CSV in Perl with a regex?
  8. Javascript: Splitting a string by comma but ignoring commas in quotes
  9. Powershell script to remove double quotes from CSV unless comma exists inside double quotes
  10. My regex is matching too much. How do I make it stop? [duplicate]
  11. How to validate phone numbers using regex
  12. Regex whitespace word boundary
  13. Regex Last occurrence?
  14. How to wisely combine shingles and edgeNgram to provide flexible full text search?
  15. Regular expression negative lookahead
  16. Why is a character class faster than alternation?
  17. Mail::RFC822::Address Regex
  18. Regular Expressions- Match Anything
  19. What is the Regular Expression For “Not Whitespace and Not a hyphen”
  20. regex, extract string NOT between two brackets
  21. Regex for extracting filename from path
  22. Validate mathematical expressions using regular expression?
  23. How exactly does String.split() method in Java work when regex is provided?
  24. Split a string on whitespace in Go?
  25. htaccess 301 redirect – Remove query string (QSA)
  26. Regex for positive float numbers
  27. Recursively change file extensions in Bash
  28. Regex validation of email addresses according to RFC5321/RFC5322
  29. Capturing text before and after a C-style code block with a Perl regular expression
  30. Regular expression to match a valid absolute Windows directory containing spaces

Leave a Comment