CSV to JSON Converter

[Deepak Ingale1]

Many a times we require to convert a CSV formatted data to JSON. This CSV data can come from CSV file inside ServiceNow or it may come from WebService response as well.

Below script Include will convert the CSV data and will return JSON formatted object out of it.

Enjoy CSV Parsing !!!

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'
};