split csv where fields contain commas-CodePudding

Thanks to the answer posted here: Put data from a csv file into an array (Javascript) I have been able to split a csv file in the manner I need to but I have come across a problem with the output. Nothing to do with the code as it works just as I wanted it to. The issue relates to the source data.

The csv files are provided to me as is so I did not realise that they had fields that contained commas. This means the split doesn't work as needed.

This is the code I am using:

$.get("export.csv", function process(dataString) {
    var lines = dataString
    .split(/\n/)
    .map(function(lineStr) {
        return lineStr.split(",");
    });
  
  var keys = lines[0];

  var objects = lines
    .slice(1)
    .map(function(arr) {
      return arr.reduce(function(obj, val, i) {
        obj[keys[i]] = val; 
        return obj;
      }, {});
    });
  
  console.log(objects);
})

This gives me the output in this format:

{
    "PROBLEM_NUMBER": "ticket_number",
    "CALLER_NAME": "\"surname",
    "PRIORITY": " forename\"",
    "CALL_TIME": "4",
    "CALL_DETAILS": "date",
    "RESOLVER": "group",
    "RESTORING_GROUP": "\"surname",
    "RESOLVING_GROUP": " forename\"",
    "RESTORATION_TIME": "group",
    "RAG_STATUS": "group",
    "CALL_STATUS": "date",
    "CALL_TYPE": "RED",
    "RESTORATION_CODE": "Closed",
    "SUBMITTER_GROUP": "Problem",
    "ASSIGNEE_GROUP": "resolution",
    "ASSIGNEE_NAME": "group",
    "RESOLVED_DATE_TIME": "group",
    "RESTORED_DATE_TIME": "",
    "TIME_WITH_TEAM": "date",
    "MONTH/YEAR\r": "date",
    "undefined": "Jan-21\r"
}

As you can see, the final field is "undefined" due to 2 fields containing a comma and splitting incorrectly.

I know I need to use regex to modify the split correctly however I don't understand or know where to put it. Is anyone able to assist me please?

Thanks

CodePudding user response：

I managed to solve the problem so thought I would post here for anyone else that may come across a similar issue:

To resolve it I declared the regex string as a variable and then called that in the .split() instruction.

The regex string I used was - /("[^"] "|[^,] )*,/g

My code now looks like this:

$.get("export.csv", function process(dataString) {
    var regex = /("[^"] "|[^,] )*,/g;
    var lines = dataString
    .split(/\n/)
    .map(function(lineStr) {
       return lineStr.split(regex);
    });
  var keys = lines[0];
  var objects = lines
    .slice(1)
    .map(function(arr) {
      return arr.reduce(function(obj, val, i) {
        obj[keys[i]] = val;
        return obj;
      }, {});
    });
  console.log(objects);
})

This gives me the correct output I need and maps all values accordingly