Regex to split a CSV

Question

I know this  or similar  has been asked many times but having tried out numerous possibilities I ve not been able to find a a regex that works 100    I ve got a CSV file and I m trying to split it into an array  but encountering two problems  quoted commas and empty elements   The CSV looks like   123 2 99 AMO024 Title  Description  more info   123987564   The regex I ve tried to use is   thisLine split                                             The only problem is that in my output array the 5th element comes out as 123987564 and not an empty string

User · Answer

In Java this pattern                                        almost work for me   String text                     asdasd a sd s ds ds dasda sds ds      String regex                                           Pattern p   Pattern compile regex   String   split   p split text   for String s split        System out println s       output         a       asdasd a sd s ds ds dasda sds ds     Disadvantage  not work  when column have an odd number of quotes

User · Answer

Yet another answer with a few extra features like support for quoted values that contain escaped quotes and CR LF characters  single values that span multiple lines    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       lt   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         lt Value gt                                    Quoted value -or-        lt Value gt          r n                        Unquoted value -or-         lt OpenValue gt                  r  n          Open ended quoted value -or-        lt Value gt                                     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        lt       lt Value gt                                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      lt   r  n       r  n             lt Value gt                   lt Value gt          r n        lt OpenValue gt                  r  n       lt Value gt            r  n             lt       lt Value gt        r n  r  n       Here is a visualization from Debuggex com  capture groups named for clarity     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

User · Answer

Worked on this for a bit and came up with this solution         n                             n       n       Try it out here   This solution handles  nice  CSV data like   a   b  c  d  e f   g   0   a  1   b  2  c 3   d  4  e 5  f 6  7   g    and uglier things like     test   one  test  two    test    three      test  four      0     test   one  1  test  two 2     test    three   3     test  four       Here s an explanation of how it works         n           all values must start at the beginning of the file                        the end of the previous line  or at a comma                     single capture group for ease of use  CSV can be either                            A  a double quoted string  beginning with a double quote                                character  containing any number  0   of                              escaped double quotes       or                              non-double quote characters                            in any order and any number of times                            and ending with a double quote character                         or  B  a non-quoted value           n          containing any number of characters which are not                     double quotes      commas      or newlines   n                          or  C  a single newline or end-of-file character                                used to capture empty values at the end of         n                     the file or at the ends of lines

User · Answer

This one matches all i need in c       lt          lt quote gt                       lt quote gt             strips quotes lets new lines lets double quotes in the quoted string lets commas in the quoted string

User · Answer

If you know that you won t have an empty field      then this expression works well                     As in the following example     Set rx   new RegExp rx Pattern                        rx Global   True Set col   rx Execute sText  For n   0 to col Count - 1     if n  gt  0 Then s   s  amp  vbCrLf     s   s  amp  col n  Next   However  if you anticipate an empty field and your text is relatively small than you might consider replacing the empty fields with a space prior to parsing to ensure that they are captured   For example         Set col   rx Execute Replace sText                      And if you need to maintain the integrity of the fields  you can restore the commas and test for empty spaces inside the loop   This may not be the most efficient method but it gets the job done

User · Answer

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   Regex                             1                            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  amp   Matches    amp  z  amp          amp  chr 34   amp  Server HTMLEncode Matches z    amp  chr 34   amp  chr 13      For zz   0 to Matches z  SubMatches Count-1       results   results  amp   Matches    amp  z  amp     SubMatches    amp  zz  amp          amp  chr 34   amp  Server HTMLEncode Matches z  SubMatches zz    amp  chr 34   amp  chr 13      next     results Left results Len results -1   amp  chr 13    next Response Write   lt pre gt    amp  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

User · Answer

I created this a few months ago for a project                         lt              It works in C  and the Debuggex was happy when I selected Python and PCRE  Javascript doesn t recognize this form of Proceeded By   lt        For your values  it will create matches on  123  2 99  AMO024  Title  Description  more info     123987564   Note that anything in quotes doesn t have a leading comma  but attempting to match with a leading comma was required for the empty value use case  Once done  trim values as necessary   I use RegexHero Net to test my Regex

User · Answer

I m using this one  it works with coma separator and double quote escaping  Normally that s should solved your problem        lt                                       g

User · Answer

I needed this answer too  but I found the answers  while informative  a little hard to follow and replicate for other languages   Here is the simplest expression I came up with for a single column out of the CSV line   I am not splitting   I m building a regex to match a column out of the CSV so I m not splitting the line                            This matches a single column from the CSV line   The first portion           of the expression is to match a quoted entry  the second part       is to match a non-quoted entry   Then either followed by a   or end of line     And the accompanying debuggex to test out the expression   https   www debuggex com r s4z Qi2gZiyzpAhx

User · Answer

The correct regular expression to match a single quoted value with escaped  doubled  single quotes in it is       n

User · Answer

s          s                               This regex works with single and double quotes and also for one quote inside another

User · Answer

If i try the regex posted by  chubbsondubs on http   regex101 com using the  g  flag  there are matches  that contain only     or an empty string  With this regex                                 i can match the parts of the CSV  inlcuding quoted parts    The line must be terminated with a     otherwise the last part isn t recognized    https   regex101 com r dF9kQ8 4 If the CSV looks like     huhu  hel  lo  world   there are 4 matches       huhu    hel  lo    world

User · Answer

I had a similar need for splitting CSV values from SQL insert statements    In my case  I could assume that strings were wrapped in single quotations and numbers were not   csv split              d   g  filter function x    return x              For some probably obvious reason  this regex produces some blank results  I could ignore those  since any empty values in my data were represented as            and not

User · Answer

I m late to the party  but the following is the Regular Expression I use                  w W                                                 r n  n    This pattern has three capturing groups    Contents of a quoted cell Contents of an unquoted cell A new line   This pattern handles all of the following    Normal cell contents without any special features   one 2 three Cell containing a double quote    is escaped to       no quote  a   quoted   thing  end Cell contains a newline character   one two nthree four Normal cell contents which have an internal quote   one two three four Cell contains quotation mark followed by comma   one  two   three    four  five   See this pattern in use   If you have are using a more capable flavor of regex with named groups and lookbehinds  I prefer the following      lt quoted gt    lt                w W                   lt normal gt    lt                            lt         lt           lt eol gt  r n  n    See this pattern in use   Edit                 w W                                                 r n  n    This slightly modified pattern handles lines where the first column is empty as long as you are not using Javascript  For some reason Javascript will omit the second column with this pattern  I was unable to correctly handle this edge-case

User · Answer

The advantage of using JScript for classic ASP pages is that you can use one of the many  many  libraries that have been written for JavaScript   Like this one  https   github com gkindel CSV-JS  Download it  include it in your ASP page  parse CSV with it    lt    language  javascript    gt    lt script language  javascript  runat  server  src  scripts csv js  gt  lt  script gt   lt script language  javascript  runat  server  gt   var text    123 2 99 AMO024 Title  Description  more info   123987564       rows   CSV parse line        Response Write rows 0  4     lt  script gt

User · Answer

Aaaand another answer here     Since I couldn t make the others quite work   My solution both handles escaped quotes  double occurrences   and it does not include delimiters in the match   Note that I have been matching against   instead of   as that was my scenario  but simply replace them in the pattern for the same effect   Here goes  remember to use the  ignore whitespace  flag  x if you use the commented version below       Only include if previous char was start of string or delimiter    lt               1st option  empty quoted string            2          2nd option  nothing                     3rd option  all but quoted strings   123        included linebreaks to allow multiline matching         r n           4th option  quoted strings    123  321        start pling                      double quote         2                  or anything but quotes                   at least one occurance - greedy            end pling         Only include if next char is delimiter or end of string           Single line version      lt           2            r n         2                       Debuggex Demo  regex101 example

User · Answer

I personally tried many RegEx expressions without having found the perfect one that match all cases   I think that regular expressions is hard to configure properly to match all cases properly  Although few persons will not like the namespace  and I was part of them   I propose something that is part of the  Net framework and give me proper results all the times in all cases  mainly managing every double quotes cases very well    Microsoft VisualBasic FileIO TextFieldParser  Found it here  StackOverflow  Example of usage   TextReader textReader   new StringReader simBaseCaseScenario GetSimStudy   Study FilesToDeleteWhenComplete   Microsoft VisualBasic FileIO TextFieldParser textFieldParser   new TextFieldParser textReader   textFieldParser SetDelimiters new string             string   fields   textFieldParser ReadFields    foreach  string path in fields              Hope it could help

User · Answer

I use this expression   It takes into account a space after a comma  which I have come across                       w W                                                 r n  n

[regex] Regex to split a CSV

Examples related to regex

Examples related to csv

Examples related to asp-classic

Examples related to split

Examples related to quotes