GlideExcel Parser not Working

[Kusuma2]

Hi Team

I am trying to restrict the Catalog Item submission if the Mandatory Columns on the Excel is not filled with data.
I am Using the GlideExcel Parser and the Mandatory Columns I am getting from the system property.
It is Strange the Excel Parser is not working on the Excel where the columns have the restricted choices. However, it is parsing and working as expected on string 

I am checking if the row is empty using the JSUtil.nil but the for loop continues..
Below is the script :

var output='';

var attachmentSysID = this.getParameter('sysparm_field');

var attachment = new GlideSysAttachment();

var attachmentStream = attachment.getContentStream('6512a9e21bab0e5403d842609b4bcb39');

var parser = new sn_impex.GlideExcelParser();

parser.setSheetNumber(0);

parser.parse(attachmentStream);

var headers = parser.getColumnHeaders();

//var mandatoryHeaders = ["Model", "Model category","State"];

var getWrkAssetInsert=gs.getProperty('Asset.Delivery_Note');

var mandatoryHeaders = getWrkAssetInsert.split(",");

var isValid = true;

while (parser.next()) {

var row = parser.getRow();

for (var header in mandatoryHeaders) {

var currentHeader = mandatoryHeaders[header];

// Check if the header is present in the column headers

if (headers.indexOf(currentHeader) === -1) {

isValid = false;

gs.addErrorMessage("Missing mandatory column: " + currentHeader);

output="Header";

}

gs.print(row[currentHeader]);

// Check if the value in the mandatory column is empty

if (JSUtil.nil(row[currentHeader])) {

isValid = false;

gs.addErrorMessage("Invalid data, fill all the mandatory rows for Column " +currentHeader);

output="Missing";

}

}

}

if (isValid) {

    output="Filled";

gs.addInfoMessage("All mandatory columns are filled.");

}

Kindly Need your help.

[Bhavya11]

Hi @Kusuma2 

Please try below script and making some modification

var output = '';
var attachmentSysID = '53a9b2f2c32b4a10c7311412b4013125'; // Provided sys_id instead of this you can pass dynmaically 
var attachment = new GlideSysAttachment();
var attachmentStream = attachment.getContentStream(attachmentSysID); // Use the provided sys_id

// Check if attachmentStream is not null
if (attachmentStream) {
    var parser = new sn_impex.GlideExcelParser();
    parser.setSheetNumber(0);

    try {
        parser.parse(attachmentStream);
        
        // Initialize headers
        var headers = parser.getColumnHeaders();

        // Log the headers to check their format
        gs.print("Headers raw data: " + headers);

        // Check if headers is an array and contains data
        if (Array.isArray(headers)) {
            if (headers.length > 0) {
                gs.print("Headers: " + headers.join(", "));
            } else {
                gs.print("Headers array is empty.");
            }
        } else {
            gs.print("Headers is not an array or is null.");
        }

        var getWrkAssetInsert = gs.getProperty('Asset.Delivery_Note');
        var mandatoryHeaders = getWrkAssetInsert.split(",");
        var isValid = true;

        // Iterate through each row in the Excel file
        while (parser.next()) {
            var row = parser.getRow();

            // Log the entire row to understand its structure
            gs.print("Row Data: " + JSON.stringify(row));

            var rowValid = true;

            // Check mandatory headers
            mandatoryHeaders.forEach(function(currentHeader) {
                var header = currentHeader.trim();

                // Check if the header is present in the column headers
                if (headers.indexOf(header) === -1) {
                    isValid = false;
                    gs.addErrorMessage("Missing mandatory column: " + header);
                    output = "Header";
                } else {
                    // Check if the value in the mandatory column is empty
                    var value = row[header];

                    // Log the value for debugging
                    gs.print("Value for " + header + ": " + value);

                    // Use JSUtil.nil() to check if the value is null, undefined, or empty
                    if (JSUtil.nil(value) || value.trim() === '') {
                        isValid = false;
                        gs.addErrorMessage("Invalid data, fill all the mandatory rows for Column " + header);
                        output = "Missing";
                        rowValid = false; // Mark the row as invalid
                    }
                }
            });

            // Optional: Log row validity
            gs.print("Row Valid: " + rowValid);
        }

        // Final validation message
        if (isValid) {
            output = "Filled";
            gs.addInfoMessage("All mandatory columns are filled.");
        } else {
            gs.addErrorMessage("Some mandatory columns are missing or have invalid data.");
        }

    } catch (e) {
        gs.addErrorMessage("Error parsing Excel file: " + e.message);
        gs.print("Error parsing Excel file: " + e.message);
    }
} else {
    gs.addErrorMessage("Attachment stream is null or not accessible.");
}

i tried above code in the background script it worked fine, if any empty date it will show that mandatory need to be filled 

If my response proves useful, please indicate its helpfulness by selecting "Accept as Solution" and " Helpful."

Thanks,

BK