Parsing the excel file and adding records thorugh flow designer

[Shalaka]

Hi All,

I have one excel file which will be attached to a RITM, I have to parse it and add data into one custom table row by row.

I have created one script include to return the valid data and consolidated errors and pasting them in RITM worknotes.

var ACN_Beatrice_Multichange_DataValidation = Class.create();

ACN_Beatrice_Multichange_DataValidation.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    /*

    attachmentValidation function validate attachment in request. Following fields evaluated and found any discrepancy in data.

    - Column name and order

    - User ID

    - Computer

    @attachmentSysId - attachment sys_id from the request(sc_req_item)

    */

    attachmentValidation: function(attachmentSysId) {

        var parser = new sn_impex.GlideExcelParser();

        var attachment = new GlideSysAttachment();

        // use attachment sys id of an excel file

        var attachmentStream = attachment.getContentStream(attachmentSysId);

        parser.parse(attachmentStream);

        //retrieve the column headers nam

        var columnInAttachement = parser.getColumnHeaders();

        var columnInTemplateList = ["UserId", "Computer"];

        var errorColumnInAttachement = '';

        var invalidAttachment = false;

        //Validate column order and spelling

        for (var i = 0; i < columnInTemplateList.length; i++) {

            if (columnInAttachement[i] != columnInTemplateList[i]) {

                errorColumnInAttachement += columnInAttachement[i] + ',';

            }

        }

        if (errorColumnInAttachement.length > 0) {

            invalidAttachment = true;

        }

        //List of all fields used for to capture output after evaluation purpose

        var correctValuesArr = [];

        var maincorrectValuesArr = [];

        var currentRow = 1;

        var incorrectUserID = '';

        var emptyUserId = '';

        var incorrectComputer = '';

        var emptyComputer = '';

        while (parser.next()) {

            var row = parser.getRow();

            // Validation//

            if (!JSUtil.nil(row['UserId']) || !JSUtil.nil(row['Computer'])) {

                var userID = row['UserId'];

                if (userID != null) {

                    var usrReturnValue = new ACN_Beatrice_SwInstalltion_log().getUserSysId(userID);

                    if (usrReturnValue == undefined) {

                        invalidAttachment = true;

                        incorrectUserID += +' ' + currentRow + '---------->' + userID + '\n';

                    }

                } else {

                    if (userID == null) {

                        invalidAttachment = true;

                        emptyUserId += currentRow + ',';

                    }

                }

                var computrID = row['Computer'];

                if (computrID != null) {

                    var compIDReturnValue = new ACN_Beatrice_SwInstalltion_log().getComputerId(computrID);

                    if (compIDReturnValue == undefined) {

                        invalidAttachment = true;

                        incorrectComputer += +' ' + currentRow + '---------->' + computrID + '\n';

                    }

                } else {

                    if (computrID == null) {

                        invalidAttachment = true;

                        emptyComputer += currentRow + ',';

                    }

                }

if (invalidAttachment == false) {

            correctValuesArr.UserID = usrReturnValue;

            correctValuesArr.ComputerID = compIDReturnValue;

            maincorrectValuesArr.push(JSON.Object(correctValuesArr));

            gs.log('maincorrectValuesArr=' + maincorrectValuesArr);

        }

            }

            currentRow++;

        }

        /* After evaluation consolidate all error message */

        var consolidateErrorMessage = '';

        if (invalidAttachment == true) {

            consolidateErrorMessage += 'Beatrice Multichange load request failed due to following reasons. Request you to do the changes in the template and raise another data load  request. \n';

            if (!JSUtil.nil(errorColumnInAttachement)) {

                consolidateErrorMessage += '* Attachment column header either incorrect order or misspelling. Following column having issue.' + errorColumnInAttachement + '\n';

            }

            if (!JSUtil.nil(emptyUserId)) {

                consolidateErrorMessage += '* Following row User ID was empty. ' + emptyUserId.substring(0, emptyUserId.length - 1) + '\n';

            }

            if (!JSUtil.nil(incorrectUserID)) {

                consolidateErrorMessage += '* Following User ID was not exist in Servicenow system.\nExcel row   APM ID\n' + incorrectUserID;

            }

            if (!JSUtil.nil(emptyComputer)) {

                consolidateErrorMessage += '* Following row Computer are empty. ' + emptyComputer.substring(0, emptyComputer.length - 1) + '\n';

            }

            if (!JSUtil.nil(incorrectComputer)) {

                consolidateErrorMessage += "* Computer option are incorrect in following rows " + incorrectComputer.substring(0, incorrectComputer.length - 1 + '.');

            }

        }

        gs.log('Output of BeatriceSI=' + maincorrectValuesArr + '+++' + consolidateErrorMessage);

        return maincorrectValuesArr + '+++' + consolidateErrorMessage;

    },

    type: 'ACN_Beatrice_Multichange_DataValidation'

});

But I am not getting this variable value maincorrectValuesArr. Can someone suggest what is wrong.