How to replace commas within strings when converting a CSV to JSON (JS)

Question

I'm having a mapping issue when trying to convert a client supplied CSV into JSON data using JS. One of the columns in the CSV contains address data which will contain commas. I've tried changing the delimiter but for some reason when i read the CSV JS ignores the set delimiter and converts them to commas. This means the mapping when converting the data to JSON is incorrect. code and outputs below :-

JS :-

    $(document).ready(function() {
    $.ajax({
        type: "GET",
        url: "result.csv",
        dataType: "text",
        success: function(data) { $("body").append(csvJSON(data));}
     });
});

function csvJSON(csv){

    var lines=csv.split("\n");

    var result = [];

    var headers=lines[0].split(",");

    for(var i=1;i<lines.length;i++){

        var obj = {};
        var currentline=lines[i].split(",");


        for(var j=0;j<headers.length;j++){

            obj[headers[j]] = currentline[j];
        }

        result.push(obj);

    }

    return JSON.stringify(result); 
  }

CSV DATA :-

"type","geometry__type","geometry__coordinates__001","geometry__coordinates__002","properties__name","properties__address","properties__address2","properties__city","properties__state","properties__postal","properties__country","properties__phone","properties__phoneFormatted","properties__email","properties__web"
"Stockist","Point",-110.788,43.4705,"Whitechapel Ltd","Box 11719, 1135 Maple Way","Jackson,","Wyoming","WY",83002,"US",13077399478,"+1 307 739-9478","[email protected]","www.whitechapel-ltd.com"
"Stockist","Point",103.82705,1.30637,"Thrive Design & Trading","19, Tanglin Road, #03-35","Tanglin Shopping Centre","Singapore",,247909,"Singapore","65-67357333","65-67357333","[email protected]",

CURRENT RESULT :-

   {  
      "type":"Stockist",
      "geometry__type":"Point",
      "geometry__coordinates__001":"-110.788",
      "geometry__coordinates__002":"43.4705",
      "properties__name":"Whitechapel Ltd",
      "properties__address":"\"Box 11719",
      "properties__address2":" 1135 Maple Way\"",
      "properties__city":"\"Jackson",
      "properties__state":"\"",
      "properties__postal":"Wyoming",
      "properties__country":"WY",
      "properties__phone":"83002",
      "properties__phoneFormatted":"US",
      "properties__email":"13077399478",
      "properties__web\r":"+1 307 739-9478"
   },

DESIRED RESULT :-

  {  
      "type":"Stockist",
      "geometry__type":"Point",
      "geometry__coordinates__001":"-110.788",
      "geometry__coordinates__002":"43.4705",
      "properties__name":"Whitechapel Ltd",
      "properties__address":"Box 11719, 1135 Maple Way",
      "properties__address2":"Jackson,",
      "properties__city":"Wyoming",
      "properties__state":"WY",
      "properties__postal":"83002",
      "properties__country":"US",
      "properties__phone":"13077399478",
      "properties__phoneFormatted":"+1 307 739-9478",
      "properties__email":"[email protected]",
      "properties__web":"www.whitechapel-ltd.com"
   },

Answer 1

Since all your values are wrapped in quotes, you should change your split parameter to a regular expression that only splits when a comma is outside of a pair of quotes.

Because of the way your data is formatted you should keep in mind that you'll have a lot of unnecessary escaped quotation strings that you'll need to clean up. But you have to make sure you don't accidentally clean up any quotes that were already escaped in the csv to begin with. But I suppose these edge cases are why people use a prebuilt library.

EDIT

In regards to my previous statement. You might be able to get away with simply removing only the quotes at the beginning and end of the string. But I'm just gonna throw in a disclaimer and say your mileage with this result may vary depending on your data.

const csv=`\"type","geometry__type","geometry__coordinates__001","geometry__coordinates__002","properties__name","properties__address","properties__address2","properties__city","properties__state","properties__postal","properties__country","properties__phone","properties__phoneFormatted","properties__email","properties__web"
"Stockist","Point",-110.788,43.4705,"Dummy address","Box 11719, 1135 Maple Way","Jackson,","Wyoming","WY",83002,"US",12313213213213,"+111111111","dummy ","dummy web address"`

console.log(csvJSON(csv))

function csvJSON(csv){

    var lines=csv.split("\n");

    var result = [];
    
    var commaRegex = /,(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)/g
    
    var quotesRegex = /^"(.*)"$/g

    var headers = lines[0].split(commaRegex).map(h => h.replace(quotesRegex, "$1"));

    for(var i=1;i<lines.length;i++){

        var obj = {};
        var currentline=lines[i].split(commaRegex);


        for(var j=0;j<headers.length;j++){

            obj[headers[j]] = currentline[j].replace(quotesRegex, "$1");
        }

        result.push(obj);

    }
    return result;
    //return JSON.stringify(result); 
  }