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
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