How can I parse a CSV string with JavaScript which contains comma in data

Question

I have the following type of string var string    quot  string  duppi  du   23  lala quot   I want to split the string into an array on each comma  but only the commas outside the single quotation marks  I can t figure out the right regular expression for the split    string split       will give me   quot  string quot    quot  duppi quot    quot  du  quot    quot  23 quot    quot  lala quot    but the result should be    quot string  duppi  du quot    quot 23 quot    quot lala quot    Is there a cross-browser solution

User · Answer

People seemed to be against RegEx for this. Why?

(\s*'[^']+'|\s*[^,]+)(?=,|$)

Here's the code. I also made a fiddle.

String.prototype.splitCSV = function(sep) {
  var regex = /(\s*'[^']+'|\s*[^,]+)(?=,|$)/g;
  return matches = this.match(regex);    
}

var string = "'string, duppi, du', 23, 'string, duppi, du', lala";
var parsed = string.splitCSV();
alert(parsed.join('|'));

User · Answer

RFC 4180 solution  This does not solve the string in the question since its format is not conforming with RFC 4180  the acceptable encoding is escaping double quote with double quote  The solution below works correctly with CSV files d l from google spreadsheets   UPDATE  3 2017   Parsing single line would be wrong  According to RFC 4180 fields may contain CRLF which will cause any line reader to break the CSV file  Here is an updated version that parses CSV string    x000D   x000D   use strict   x000D   x000D  function csvToArray text    x000D      let p       row         ret    row   i   0  r   0  s    0  l  x000D      for  l of text    x000D          if          l    x000D              if  s  amp  amp  l     p  row i     l  x000D              s    s  x000D            else if          l  amp  amp  s  l   row   i        x000D          else if    n      l  amp  amp  s    x000D              if    r      p  row i    row i  slice 0  -1   x000D              row   ret   r     l        i   0  x000D            else row i     l  x000D          p   l  x000D        x000D      return ret  x000D     x000D   x000D  let test     one   two with escaped      double quotes     three  with  commas  four with no quotes  five with CRLF r n  r n 2nd line one   two with escaped      double quotes     three  with  commas  four with no quotes  five with CRLF r n    x000D  console log csvToArray test    x000D   x000D   x000D    OLD ANSWER   Single line solution    x000D   x000D  function CSVtoArray text    x000D      let ret         i   0  p       s   true  x000D      for  let l in text    x000D          l   text l   x000D          if          l    x000D              s    s  x000D              if          p    x000D                  ret i          x000D                  l    -   x000D                else if         p  x000D                  l    -   x000D            else if  s  amp  amp          l  x000D              l   ret   i        x000D          else x000D              ret i     l  x000D          p   l  x000D        x000D      return ret  x000D    x000D  let test     one   two with escaped      double quotes     three  with  commas  four with no quotes five for fun   x000D  console log CSVtoArray test    x000D   x000D   x000D    And for the fun  here is how you create CSV from the array    x000D   x000D  function arrayToCSV row    x000D      for  let i in row    x000D          row i    row i  replace    g         x000D        x000D      return       row join               x000D    x000D   x000D  let row     x000D     one   x000D     two with escaped    double quote   x000D     three  with  commas   x000D     four with no quotes  now has    x000D     five for fun  x000D     x000D  let text   arrayToCSV row   x000D  console log text   x000D   x000D   x000D

User · Answer

You can use papaparse js like the example below   lt  DOCTYPE html gt   lt html lang  quot en quot  gt        lt head gt           lt title gt CSV lt  title gt       lt  head gt        lt body gt           lt input type  quot file quot  id  quot files quot  multiple  quot  quot  gt           lt button onclick  quot csvGetter   quot  gt CSV Getter lt  button gt           lt h3 gt The Result will be in the Console  lt  h3 gt            lt script src  quot papaparse min js quot  gt  lt  script gt            lt script gt              function csvGetter                      var file   document getElementById  files   files 0                   Papa parse file                        complete  function results                            console log results data                                                                      lt  script gt       lt  body gt    lt  html gt   Don t forget to include papaparse js in the same folder

User · Answer

Regular expressions to the rescue  These few lines of code handle properly quoted fields with embedded commas  quotes  and newlines based on the RFC 4180 standard   function parseCsv data  fieldSep  newLine        fieldSep   fieldSep             newLine   newLine      n       var nSep     x1D       var qSep     x1E       var cSep     x1F       var nSepRe   new RegExp nSep   g        var qSepRe   new RegExp qSep   g        var cSepRe   new RegExp cSep   g        var fieldRe   new RegExp     lt          fieldSep      n         s  S      lt                     fieldSep      n       g        var grid           data replace   r g      replace   n         replace fieldRe  function match  p1  p2            return p2 replace   n g  nSep  replace     g  qSep  replace    g  cSep          split   n   forEach function line            var row   line split fieldSep  map function cell                return cell replace nSepRe  newLine  replace qSepRe       replace cSepRe                            grid push row               return grid     const csv    A1 B1 C1 n A   2     B  2   C n2    const separator                field separator  default      const newline      lt br   gt        newline representation in case a field contains newlines  default    n   var grid   parseCsv csv  separator  newline      expected       A1    B1    C1        A  2     B  2    C  lt br   gt  2        Unless stated elsewhere  you don t need a finite state machine  The regular expression handles RFC 4180 properly thanks to positive lookbehind  negative lookbehind  and positive lookahead   Clone download code at https   github com peterthoeny parse-csv-js

User · Answer

According to this blog post  this function should do it   String prototype splitCSV   function sep      for  var foo   this split sep   sep          x   foo length - 1  tl  x  gt   0  x--        if  foo x  replace    s          charAt foo x  length - 1                  if   tl   foo x  replace    s           length  gt  1  amp  amp  tl charAt 0                    foo x    foo x  replace    s     s   g      replace     g                else if  x            foo splice x - 1  2   foo x - 1   foo x   join sep            else foo   foo shift   split sep  concat foo         else foo x  replace     g            return foo       You would call it like so   var string     string  duppi  du   23  lala   var parsed   string splitCSV    alert parsed join          This jsfiddle kind of works  but it looks like some of the elements have spaces before them

User · Answer

I had a very specific use case where I wanted to copy cells from Google Sheets into my web app   Cells could include double-quotes and new-line characters   Using copy and paste  the cells are delimited by a tab characters  and cells with odd data are double quoted   I tried this main solution  the linked article using regexp  and Jquery-CSV  and CSVToArray   http   papaparse com   Is the only one that worked out of the box   Copy and paste is seamless with Google Sheets with default auto-detect options

User · Answer

No regexp  readable  and according to https   en wikipedia org wiki Comma-separated values Basic rules  function csv2arr str  string        let line     quot  quot         const ret    line        let quote   false       for  let i   0  i  lt  str length  i              const cur   str i           const next   str i   1            if   quote                const cellIsEmpty   line line length - 1  length     0              if  cur       quot    amp  amp  cellIsEmpty  quote   true              else if  cur      quot   quot   line push  quot  quot                else if  cur      quot  r quot   amp  amp  next      quot  n quot     line     quot  quot     ret push line   i                  else if  cur      quot  n quot     cur      quot  r quot     line     quot  quot     ret push line                 else line line length - 1     cur            else               if  cur       quot    amp  amp  next       quot      line line length - 1     cur  i                  else if  cur       quot    quote   false              else line line length - 1     cur                      return ret

User · Answer

I liked FakeRainBrigand s answer  however it contains a few problems  It can not handle whitespace between a quote and a comma  and does not support 2 consecutive commas  I tried editing his answer but my edit got rejected by reviewers that apparently did not understand my code  Here is my version of FakeRainBrigand s code  There is also a fiddle  http   jsfiddle net xTezm 46   String prototype splitCSV   function             var matches   this match    s         s   s                 g           for  var n   0  n  lt  matches length    n                matches n    matches n  trim                if  matches n          matches n                          if  this 0          matches unshift              return matches     var string      string  duppi  du    23      string  duppi  du  dup       lala   var parsed   string splitCSV    alert parsed join

User · Answer

If you can have your quote delimiter be double quotes  then this is a duplicate of Example JavaScript code to parse CSV data  You can either translate all single-quotes to double-quotes first  string   string replace     g    quot          or you can edit the regex in that question to recognize single-quotes instead of double-quotes     Quoted fields   quot                           quot     However  this assumes certain markup that is not clear from your question  Please clarify what all the various possibilities of markup can be  per my comment on your question

User · Answer

Aside from the excellent and complete answer from ridgerunner  I thought of a very simple workaround for when your backend runs PHP  Add this PHP file to your domain s backend  say  csv php   lt  php     session start       Optional     header  quot content-type  text xml quot        header  quot charset UTF-8 quot           Set the delimiter and the End of Line character of your CSV content      echo json encode array map  str getcsv   str getcsv   POST  quot csv quot     quot  n quot        gt   Now add this function to your JavaScript toolkit  should be revised a bit to make crossbrowser I believe   function csvToArray csv        var oXhr   new XMLHttpRequest      oXhr addEventListener  quot readystatechange quot           function                  if  this readyState    4  amp  amp  this status    200                    console log this responseText                   console log JSON parse this responseText                                       oXhr open  quot POST quot   quot path to csv php quot  true       oXhr setRequestHeader  quot Content-type quot    quot application x-www-form-urlencoded  charset utf-8 quot        oXhr send  quot csv  quot    encodeURIComponent csv       It will cost you one Ajax call  but at least you won t duplicate code nor include any external library  Ref  http   php net manual en function str-getcsv php

User · Answer

Adding one more to the list  because I find all of the above not quite  KISS  enough     This one uses regex to find either commas or newlines while skipping over quoted items   Hopefully this is something noobies can read through on their own   The splitFinder regexp has three things it does  split by a         - finds commas  r  n - finds new lines   potentially with carriage return if the exporter was nice                 - skips anynthing surrounded in quotes  because commas and newlines don t matter in there   If there is an escaped quote     in the quoted item  it will get captured before an end quote can be found     x000D   x000D  const splitFinder       r  n                g  x000D   x000D  function csvTo2dArray parseMe    x000D    let currentRow       x000D    const rowsOut    currentRow   x000D    let lastIndex   splitFinder lastIndex   0  x000D     x000D       add text from lastIndex to before a found newline or comma x000D    const pushCell    endIndex    gt    x000D      endIndex   endIndex    parseMe length  x000D      const addMe   parseMe substring lastIndex  endIndex   x000D         remove quotes around the item x000D      currentRow push addMe replace        g        x000D      lastIndex   splitFinder lastIndex  x000D      x000D   x000D   x000D    let regexResp  x000D       for each regexp match  either comma  newline  or quoted item  x000D    while  regexResp   splitFinder exec parseMe     x000D      const split   regexResp 0   x000D   x000D         if it s not a quote capture  add an item to the current row x000D          quote captures will be pushed by the newline or comma following  x000D      if  split startsWith          false    x000D        const splitStartIndex   splitFinder lastIndex - split length  x000D        pushCell splitStartIndex   x000D   x000D           then start a new row if newline x000D        const isNewLine      r  n   test split   x000D        if  isNewLine    rowsOut push currentRow          x000D        x000D      x000D       make sure to add the trailing text  no commas or newlines after  x000D    pushCell    x000D    return rowsOut  x000D    x000D   x000D  const rawCsv    a b c n test r n   comma  test    r n       nsecond row ends with empty n quote  test   x000D  const rows   csvTo2dArray rawCsv   x000D  console log rows   x000D   x000D   x000D

User · Answer

Disclaimer 2014-12-01 Update  The answer below works only for one very specific format of CSV  As correctly pointed out by DG in the comments  this solution does not fit the RFC 4180 definition of CSV and it also does not fit Microsoft Excel format  This solution simply demonstrates how one can parse one  non-standard  CSV line of input which contains a mix of string types  where the strings may contain escaped quotes and commas  A non-standard CSV solution As austincheney correctly points out  you really need to parse the string from start to finish if you wish to properly handle quoted strings that may contain escaped characters  Also  the OP does not clearly define what a  quot CSV string quot  really is  First we must define what constitutes a valid CSV string and its individual values  Given   quot CSV String quot  Definition For the purpose of this discussion  a  quot CSV string quot  consists of zero or more values  where multiple values are separated by a comma  Each value may consist of   A double quoted string  may contain unescaped single quotes   A single quoted string  may contain unescaped double quotes   A non-quoted string  may not contain quotes  commas or backslashes   An empty value   An all whitespace value is considered empty    Rules Notes   Quoted values may contain commas  Quoted values may contain escaped-anything  e g   that  s cool   Values containing quotes  commas  or backslashes must be quoted  Values containing leading or trailing whitespace must be quoted  The backslash is removed from all     in single quoted values  The backslash is removed from all    quot  in double quoted values  Non-quoted strings are trimmed of any leading and trailing spaces  The comma separator may have adjacent whitespace  which is ignored    Find  A JavaScript function which converts a valid CSV string  as defined above  into an array of string values  Solution  The regular expressions used by this solution are complex  And  IMHO  all non-trivial regular expressions should be presented in free-spacing mode with lots of comments and indentation  Unfortunately  JavaScript does not allow free-spacing mode  Thus  the regular expressions implemented by this solution are first presented in native regular expressions syntax  expressed using Python s handy r          raw-multi-line-string syntax   First here is a regular expression which validates that a CVS string meets the above requirements  Regular expression to validate a  quot CSV string quot   re valid   r quot  quot  quot    Validate a CSV string having single  double or un-quoted values                                        Anchor to start of string   s                                    Allow whitespace before value                                        Group for value alternatives                   S s                  Either Single quoted string     quot    quot            S s    quot        quot        or Double quoted string         quot  s        s      quot  s            or Non-comma  non-quote stuff                                        End group of value alternatives   s                                    Allow whitespace after value                                        Zero or more additional values                                       Values separated by a comma     s                                  Allow whitespace before value                                        Group for value alternatives                     S s                Either Single quoted string       quot    quot            S s    quot        quot      or Double quoted string           quot  s        s      quot  s          or Non-comma  non-quote stuff                                        End group of value alternatives     s                                  Allow whitespace after value                                        Zero or more additional values                                       Anchor to end of string   quot  quot  quot   If a string matches the above regular expression  then that string is a valid CSV string  according to the rules previously stated  and may be parsed using the following regular expression  The following regular expression is then used to match one value from the CSV string  It is applied repeatedly until no more matches are found  and all values have been parsed   Regular expression to parse one value from a valid CSV string  re value   r quot  quot  quot    Match one value in valid CSV string      s                                 Don t match empty last value   s                                    Strip whitespace before value                                        Group for value alternatives                    S s                 Either  1  Single quoted string     quot     quot            S s    quot         quot      or  2  Double quoted string          quot  s        s      quot  s           or  3  Non-comma  non-quote stuff                                        End group of value alternatives   s                                    Strip whitespace after value                                        Field ends on comma or EOS   quot  quot  quot   Note that there is one special case value that this regular expression does not match - the very last value when that value is empty  This special  quot empty last value quot  case is tested for and handled by the JavaScript function which follows  JavaScript function to parse CSV string     Return array of string values  or NULL if CSV string not well formed  function CSVtoArray text        var re valid      s                   S s             quot    quot            S s    quot        quot       quot  s        s      quot  s        s      s                   S s             quot    quot            S s    quot        quot       quot  s        s      quot  s        s           var re value        s    s                    S s              quot     quot            S s    quot         quot        quot  s        s      quot  s         s         g          Return NULL if input string is not well formed CSV string      if   re valid test text   return null       var a          Initialize array to receive values      text replace re value      quot Walk quot  the string using replace with callback          function m0  m1  m2  m3                    Remove backslash from    in single quoted values              if  m1     undefined  a push m1 replace      g   quot   quot                     Remove backslash from   quot  in double quoted values              else if  m2     undefined  a push m2 replace     quot  g    quot                  else if  m3     undefined  a push m3               return        Return empty string                      Handle special case of empty last value      if     s    test text   a push          return a      Example input and output  In the following examples  curly braces are used to delimit the  result strings    This is to help visualize leading trailing spaces and zero-length strings      Test 1  Test string from original question  var test    quot  string  duppi  du   23  lala quot   var a   CSVtoArray test      Array has three elements      a 0     string  duppi  du      a 1     23      a 2     lala         Test 2  Empty CSV string  var test    quot  quot   var a   CSVtoArray test      Array has zero elements         Test 3  CSV string with two empty values  var test    quot   quot   var a   CSVtoArray test      Array has two elements      a 0           a 1              Test 4  Double quoted CSV string having single quoted values  var test    quot  one   two with escaped    single quote    three  with  commas  quot   var a   CSVtoArray test      Array has three elements      a 0     one      a 1     two with escaped   single quote      a 2     three  with  commas         Test 5  Single quoted CSV string having double quoted values  var test     quot one quot   quot two with escaped   quot  double quote quot    quot three  with  commas quot    var a   CSVtoArray test      Array has three elements      a 0     one      a 1     two with escaped  quot  double quote      a 2     three  with  commas         Test 6  CSV string with whitespace in and around empty and non-empty values  var test    quot    one      two          four      six      seven       quot   var a   CSVtoArray test      Array has eight elements      a 0     one      a 1     two      a 2           a 3      four      a 4           a 5     six       a 6      seven       a 7           Additional notes  This solution requires that the CSV string be  quot valid quot   For example  unquoted values may not contain backslashes or quotes  e g  the following CSV string is not valid  var invalid1    quot one  that s me   escaped    comma quot   This is not really a limitation because any sub-string may be represented as either a single or double quoted value  Note also that this solution represents only one possible definition for  quot comma-separated values quot   Edit history  2014-05-19  Added disclaimer  2014-12-01  Moved disclaimer to top

User · Answer

My answer presumes your input is a reflection of code content from web sources where single and double quote characters are fully interchangeable provided they occur as an non-escaped matching set   You cannot use regex for this   You actually have to write a micro parser to analyze the string you wish to split   I will  for the sake of this answer  call the quoted parts of your strings as sub-strings   You need to specifically walk across the string  Consider the following case   var a    some sample string with   double quotes   and  single quotes  and some craziness like this       or           b    sample of code from JavaScript with a regex containing a comma      that should probably be ignored      In this case you have absolutely no idea where a sub-string starts or ends by simply analyzing the input for a character pattern   Instead you have to write logic to make decisions on whether a quote character is used a quote character  is itself unquoted  and that the quote character is not following an escape   I am not going to write that level of complexity of code for you  but you can look at something I recently wrote that has the pattern you need   This code has nothing to do with commas  but is otherwise a valid enough micro-parser for you to follow in writing your own code   Look into the asifix function of the following application   https   github com austincheney Pretty-Diff blob master fulljsmin js

User · Answer

PEG  js  grammar that handles RFC 4180 examples at http   en wikipedia org wiki Comma-separated values   start       n r   first line rest    n r   data line   return data        n r     rest unshift first   return rest     line     first field rest      text field   return text           amp    return   first    rest length       ignore blank lines       rest unshift first   return rest     field         text char        return text join            text    n r      return text join         char               return                   Test at http   jsfiddle net knvzk 10 or https   pegjs org online   Download the generated parser at https   gist github com 3362830

User · Answer

I ve used regex a number of times  but I always have to relearn it each time  which is frustrating  -  So Here s a non-regex solution  function csvRowToArray row  delimiter        quoteChar     quot         let nStart   0  nEnd   0  a     nRowLen row length  bQuotedValue      while  nStart  lt   nRowLen            bQuotedValue    row charAt nStart      quoteChar           if  bQuotedValue                nStart                nEnd   row indexOf quoteChar   delimiter  nStart            else               nEnd   row indexOf delimiter  nStart                    if  nEnd  lt  0  nEnd   nRowLen          a push row substring nStart nEnd            nStart   nEnd   delimiter length    bQuotedValue   1   0            return a     How it works   Pass in the csv string in row  While the start position of the next value is within the row  do the following   If this value has been quoted  set nEnd to the closing quote  Else if value has NOT been quoted  set nEnd to the next delimiter  Add the value to an array  Set nStart to nEnd plus the length of the delimeter     Sometimes it s good to write your own small function  rather than use a library  Your own  code is going to perform well and use only a small footprint  In addition  you can easily tweak it to suit your own needs

User · Answer

To complement this answer  If you need to parse quotes escaped with another quote  example    some   value   that is on xlsx file  123   You can use  function parse text      const csvExp        s    s                    S s                             S s                           S s                   s        s       s         s         g     const values          text replace csvExp   m0  m1  m2  m3  m4    gt        if  m1     undefined          values push m1 replace      g                   else if  m2     undefined          values push m2 replace      g                   else if  m3     undefined          values push m3 replace     g                   else if  m4     undefined          values push m4             return              if     s    test text         values push             return values

User · Answer

While reading the CSV file into a string  it contains null values in between strings  so try it with  0 line by line  It works for me  stringLine   stringLine replace   0 g   quot  quot

User · Answer

I have also faced the same type of problem when I had to parse a CSV file  The file contains a column address which contains the       After parsing that CSV file to JSON  I get mismatched mapping of the keys while converting it into a JSON file  I used Node js for parsing the file and libraries like baby parse and csvtojson  Example of file - address pincode foo baar   123456  While I was parsing directly without using baby parse in JSON  I was getting      address   foo    pincode   baar     field3    123456      So I wrote code which removes the comma    with any other delimiter with every field   x000D   x000D      csvString input     address  pincode  nfoo  bar  123456  n   output    address  pincode  nfoo  YOUR DELIMITER  bar  123455  n     const removeComma   function csvString       let delimiter           let Baby   require  babyparse       let arrRow   Baby parse csvString  data               arrRow              address    pincode              foo  bar    123456                      return arrRow map  singleRow  index    gt              the data will include                    singleRow      address    pincode                       return singleRow map singleField   gt                  for removing the comma in the feild             return singleField split      join delimiter                    reduce  acc  value  key    gt            acc   acc   Array isArray value             value reduce  acc1  val   gt                acc1   acc1  val                   return acc1                          n           return acc              x000D   x000D   x000D   The function returned can be passed into the csvtojson library and thus the result can be used   x000D   x000D  const csv   require  csvtojson    let csvString    address  pincode  nfoo  bar  123456  n  let jsonArray      modifiedCsvString   removeComma csvString  csv      fromString modifiedCsvString     on  json   json   gt  jsonArray push json      on  end        gt           do any thing with the json Array         x000D   x000D   x000D   Now you can get the output like       address   foo  bar     pincode  123456

[javascript] How can I parse a CSV string with JavaScript, which contains comma in data?

Examples related to javascript

Examples related to regex

Examples related to split