Need to convert the REST message response from CSV to JSON

[neha501]

Hi All,

I am getting the response body from REST message in text format(CSV). Can anyone please help me in converting the csv response to JSON.

Thanks in Advance.

[Neha15]

Hi Deepak,

Thanks for your reply. I have modified some part of the code to remove extra characters \" from header and data. Now I am able to convert the data from .csv to json for most of the files. But still getting the incorrect data into variables for some of the files.

Below is my code and sample output that I am getting. Can you please help me with that.

Code:

var csvToJSON = [];
var headers = [];

dataAsString = responseBody;

gs.print("Original CSV Content " + dataAsString);

var patternString = /\r+/;
var arrayString = dataAsString.split(patternString);
var numberOfRows = dataAsString.split(patternString).length;


headers = [];
headers = arrayString[1].split(",");
gs.print("headerss " + headers);
headers = headers.map(function(h) {
h = h.trim()
h = h.replace(/\"/g, '');
return h;
});
for ( var i = 2 ; i < numberOfRows-1 ; i++ ) {

var row = {};
gs.print("row: " + i + ": " + arrayString[i]);
var rowStringArray = arrayString[i].split(/,(?![^"]*"(?:(?:[^"]*"){2})*[^"]*$)/);
//var rowStringArray = arrayString[i].split( /(".*?"|[^",\s]+)(?=\s*,|\s*$)/g);


for ( var m = 0 ; m < rowStringArray.length ; m++) {
var content = rowStringArray[m].trim();
content = content.replace(/\"/g, '');
row[headers[m]] = content;
}
csvToJSON.push(row);
}

gs.print(JSON.stringify(csvToJSON, null , 4));

Sample CSV Response:

"number","caller_id","location","sys_created_on","closed_at","short_description","category","priority","state","Type","Severity","Port","Protocol","FQDN","SSL","assignment_group","assigned_to"

"INC0010005","Alissa Mountjoy",,"2016-04-15 12:34:10",,"Unable to get to network file shares","Network","5 - Planning","New",,,

"INC0000002",,, "2014-07-18 15:30:06",,"Unable to get to network, file shares", "Network", "1 - Critical", "Awaiting Problem", "Network", "Howard Johnson"

Sample JSON Response:

*** Script: [
{
"number": "INC0010005",
"caller_id": "Alissa Mountjoy",
"location": "",
"sys_created_on ": "2016-04-15 12:34:10",
"closed_at": " ",
"short_description": "Unable to get to network file shares",
"category": "Network",
"priority": "5 - Planning",
"state": "New ",
"assignment_group": "",
"assigned_to": ""
},
{
"number": "INC0000002,,,2014-07-18 15:30:0,Unable to get to network",
"caller_id": "file shares,Network,1 - Critical,Awaiting Problem,Network,Howard Johnson,\n \nBelow are the sample versions: \n1.29,
"location": "32.45",
"sys_created_on": "67.89",
"closed_at": "96.34",
"short_description": "21.35",
"category": "67.112",
"priority": "23.12",
"state": "122.2",
"assignment_group": "59.30",
"assigned_to": "23.89",
"description": "13.76\n Please Note:\n This needs to be reported ASAP"
}
]

[Neha15]

Sorry, I provided the wrong one for INC0000002. Please see the csv response below.

"INC0000002",,, "2014-07-18 15:30:06",,"Unable to get to network, file shares", "Network", "1 - Critical", "Awaiting Problem", "Network", "Howard Johnson"

[Deepak Ingale1]

Hello,

You can refer below and create your own code

https://stackoverflow.com/questions/1293147/javascript-code-to-parse-csv-data

Note: Please mark reply as correct / helpful if it answers your question.

[Deepak Ingale1]

I have created a blog which has a script include to convert CSV to JSON

CSVtoJSON

Note: Please mark reply as helpful or correct if it answers your question.

[Yogish Naik1]

var CSVtoJSON = Class.create(); CSVtoJSON.prototype = { initialize: function() { }, /* @parm1 : sys_id of attachment return : JSON from CSV; reference : https://stackoverflow.com/questions/1293147/javascript-code-to-parse-csv-data */ getJSONFromCSVFile: function(sysId) { var attachmentGr = this.getAttachmentGR(sysId); if (JSUtil.nil(attachmentGr)) { return; } var gsa = new GlideSysAttachment(); var bytesInFile = gsa.getBytes(attachmentGr.table_name, attachmentGr.table_sys_id); var dataAsString = Packages.java.lang.String(bytesInFile); dataAsString = String(dataAsString); return this.getCSVtoJSON(dataAsString); }, getCSVtoJSON: function(strData, strDelimiter) { strDelimiter = (JSUtil.nil(strDelimiter)) ? "," : strDelimiter; gs.print(strDelimiter); // Create a regular expression to parse the CSV values. var objPattern = new RegExp( ( // Delimiters. "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" + // Quoted fields. "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" + // Standard fields. "([^\"\\" + strDelimiter + "\\r\\n]*))" ), "gi" ); var arrMatches = null; gs.print(arrMatches); var dataObject = []; var headers = []; var rowCount = 0; var overAllElementsCount = 0; var columnCount = 0; var isHeaderBuilt = false; var rowInformation = {}; // Keep looping over the regular expression matches // until we can no longer find a match. while (arrMatches = objPattern.exec(strData)) { // Get the delimiter that was found. var strMatchedValue = ''; var strMatchedDelimiter = arrMatches[1]; // Check to see if the given delimiter has a length // (is not the start of string) and if it matches // field delimiter. If id does not, then we know // that this delimiter is a row delimiter. if (strMatchedDelimiter.length && (strMatchedDelimiter != strDelimiter)) { // Since we have reached a new row of data, // add an empty row to our data array. // reset column count and initialize the rowInformation object isHeaderBuilt = true; columnCount = 0; rowInformation = {}; } // Now that we have our delimiter out of the way, // let's check to see which kind of value we // captured (quoted or unquoted). if (arrMatches[2]) { // We found a quoted value. When we capture // this value, unescape any double quotes. strMatchedValue = arrMatches[2].replace(new RegExp("\"\"", "g"), "\""); } else { // We found a non-quoted value. strMatchedValue = arrMatches[3]; } //Build keys based on header row if (!isHeaderBuilt) { headers.push(strMatchedValue); } // Once header is built, now start creating object for each row if (isHeaderBuilt) { // If column count reaches last key in header if (columnCount == headers.length - 1) { dataObject.push(rowInformation); } gs.print(" strMatchedValue " + strMatchedValue); if (JSUtil.nil(strMatchedValue)) { rowInformation[headers[columnCount]] = ''; } else { rowInformation[headers[columnCount]] = strMatchedValue; } //gs.print(JSON.stringify(rowInformation, null, 4)); } columnCount++; } // Return the parsed data. return dataObject; }, getAttachmentGR: function(sysId) { var gr = new GlideRecord("sys_attachment"); gr.addQuery("sys_id", sysId); gr.query(); if (gr.next()) { return gr; } return; }, type: 'CSVtoJSON' };