Convert excel to JSON using excelparser in servicenow

[rahulrockkk]

Hi Team, User will attach the excel sheet while submitting the catalog item. Once the catalog item submitted, i need to read the excel and do the validation of the data in the sheet. Can you check this and provide your inputs. In one column, i also have comma separate values.

Kindly check and provide your inputs.

@Ankur Bawiskar 

[Sanjay191]

Hello @rahulrockkk 
You Create a onSubmit Client and write script include and use the GlideExcelParserAPI  to convert the excel to json and validate those data according in your onsubmit client script 

Please take the help from below script include 

var AutoPopulateExcelData = Class.create();

AutoPopulateExcelData.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    excelDataPopulate: function() {

        var sysID = this.getParameter('sysparm_id');

        var parser = new sn_impex.GlideExcelParser();

        var attachment = new GlideSysAttachment();

        // use attachment sys id of an excel file

        var attachmentStream = attachment.getContentStream(sysID);

        parser.parse(attachmentStream);

        //retrieve the column headers

        var headers = parser.getColumnHeaders();

        gs.info('APP test 0 : ' + headers);

        var header1 = headers[0];

        var header2 = headers[1];

        var header3 = headers[2];

        //print headers

        //  gs.info(header1 + " " + header2);

        var excelObj = [];

        while (parser.next()) {

            var row = parser.getRow();

            excelObj.push({

                "first_name":row["first_name"],

                "last_name":row["last_name"],

                "number":row["number"]

            })

            //  var retvar = row[header1] + ' ' + row[header2]+' '+row[header3];

            //gs.info('APP test2 : ' + retvar);

        }

        return JSON.stringify(excelObj);

    },

    type: 'AutoPopulateExcelData'

});

If my answer has helped with your question, please mark my answer as accepted solution and give a thumb up.
Thank You