Validation after Excel upload

[Dipali_Pawar]

Hi, I have requirement to populate data on MRVS via excel upload that is working. but validation part I need to do suppose i have fields x , y and in MRVS x has regex which accept 9 digits and y has regex 2 decimal places so same validation should take place after uploading excel. 

suppose user enters only 3 digits in x column in excel then after uploading it should show error like please enter up to 9 digits. 

please let me know if anyone worked on similar requirement. 

Thanks

[Ankur Bawiskar]

@Dipali_Pawar 

share the script include, client script you are using for this logic

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Dipali_Pawar]

Script include 

 getExcelData: function() {

        var arrObj = [];

        var file_id = this.getParameter('sysparam_attSysId');

        var row = '';

        var att = new GlideSysAttachment().getContentStream(file_id);

        var parser = new sn_impex.GlideExcelParser();

        parser.parse(att);

        var headers = parser.getColumnHeaders();

        while (parser.next()) {

            var obj = {};

            row = parser.getRow();

            obj.item_number = row[headers[0]]; // first variable

            obj.height_cm = row[headers[1]]; // second variable

            obj.length_cm = row[headers[2]];

            obj.width_cm = row[headers[3]];

            obj.weight_kg = row[headers[4]];

            obj.case_height_cm = row[headers[5]];

            obj.case_length_cm = row[headers[6]];

            obj.case_width_cm = row[headers[7]];

            obj.case_gross_weight_kg = row[headers[8]];

            arrObj.push(obj);

        }

     return JSON.stringify(arrObj);

},

Client script 

function onChange(control, oldValue, newValue, isLoading) {

    if (isLoading || newValue == '') {

        return;

    }

    try {

        parseXLS();

    } catch (e) {

        alert("Exception caugght in " + e.message);

    }

    function parseXLS() {

        var att = new GlideAjax('sn_customerservice.ExcelParse');

        att.addParam('sysparm_name', 'getExcelData');

        att.addParam('sysparam_attSysId', newValue);

        att.getXML(excelParser);

        function excelParser(response) {

            var answer = response.responseXML.documentElement.getAttribute('answer');

            g_form.setValue('pack_dimensions_and_weight', answer);

        }

    }

}

[Shashank_Jain]

@Dipali_Pawar ,

Updated code. Try this!

Script include :

getExcelData: function() {
var arrObj = [];
var errors = [];
var file_id = this.getParameter('sysparam_attSysId');
var att = new GlideSysAttachment().getContentStream(file_id);
var parser = new sn_impex.GlideExcelParser();

parser.parse(att);
var headers = parser.getColumnHeaders();

var regexX = /^\d{9}$/; // 9 digits
var regexY = /^\d+(\.\d{1,2})?$/; // number with up to 2 decimals

var rowCount = 1; // for error tracking

while (parser.next()) {
var obj = {};
var row = parser.getRow();

obj.x = row[headers[0]];
obj.y = row[headers[1]];

// validate x
if (!regexX.test(obj.x)) {
errors.push("Row " + rowCount + " - Field X must be exactly 9 digits.");
}

// validate y
if (!regexY.test(obj.y)) {
errors.push("Row " + rowCount + " - Field Y must be a number with up to 2 decimal places.");
}

arrObj.push(obj);
rowCount++;
}

if (errors.length > 0) {
return JSON.stringify({ status: "error", messages: errors });
}

return JSON.stringify({ status: "success", data: arrObj });
}

Client script :

function excelParser(response) {
var answer = response.responseXML.documentElement.getAttribute('answer');
var result = JSON.parse(answer);

if (result.status === "error") {
alert("Excel Validation Failed:\n" + result.messages.join("\n"));
g_form.clearValue('pack_dimensions_and_weight'); // clear MRVS if needed
return;
}

// success → populate MRVS
g_form.setValue('pack_dimensions_and_weight', JSON.stringify(result.data));
}

[Dipali_Pawar]

Hi @Shashank_Jain , Thanks for sharing code , I tried but it's giving error like Exception caugght in parseXLS is not defined

[Dipali_Pawar]

sorry I commented the glideajax part that's why it was showing error , now alert i am getting but values are not auto populating I commented the clear value line for testing.

and I want error message below in MRVS not as an alert