How to validate data in a CSV file ?

[User205031]

Hi All,

Need a help in validating the CSV file so I need to check whether the mandatory columns & rows are filled. I have written the code and it is fetching the mandatory column header but if 2 column headers are missing, it is showing only 1. Also, how can I validate all the row values so that it contains data?  

Below is the script:

var AttachmentSize = Class.create();

AttachmentSize.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    checkAttachment: function() {

        var login = this.getParameter('sysparm_cart_item_id');

        var table_name = "sc_cart_item";

        gs.info('SS7' + table_name);

        var gr = new GlideRecord('sys_attachment');

        gr.addQuery('table_name', 'sc_cart_item');

        gr.addQuery('sys_created_by', login);

        gr.orderByDesc('sys_created_on');

        gr.setLimit(1);

        gr.query();

        if (gr.next()) {

            if (gr.getValue("size_bytes") > 10000) {

                return "File size must be less than 10kb."; // Too big

            } else if (gr.getValue("size_bytes") < 10000) {

                if (table_name == "sc_cart_item") {

                    var a = [];

                    var table_sys_id = gr.table_sys_id;

                    gs.info('SS28' + table_sys_id + gr.size_bytes + gr.sys_created_on);

                    var gsa = new GlideSysAttachment();

                    var bytesInFile = gsa.getBytes(table_name, table_sys_id);

                    gs.info('SS33' + bytesInFile);

                    var originalContentsInFile = Packages.java.lang.String(bytesInFile);

                    gs.info('SS35' + originalContentsInFile);

                    originalContentsInFile = String(originalContentsInFile);

                    var fileData = originalContentsInFile.split('\n'); //split for new line

                    gs.info("SS38:" + " " + fileData);

                    var csvHeaders = "Headers" + " " + fileData[0];

                    gs.info('SS40' + csvHeaders);

                    a = csvHeaders.split(',');

                    gs.info("length" + a.length);

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

                        gs.info("Array element: " + i + ", value: " + a[i]);

                    }

                    var fileLength = fileData.length;

                    gs.info('SS57' + fileLength);

                    for (i = 1; i < fileData.length - 1; i++) {

                        var rowDetails = fileData[i];

                        gs.info("Values: " + i + " : " + rowDetails); //this prints all the rows in the .csv file

                        var mandatoryHeaders = ["TOWER", "ENTERED_HOSTNAME", "SWITCH_IP", "NETMASK", "MARSHA_CODE", "MODEL", "SNMP_CONTACT", "SNMP_LOCATION", "ISE_LOCATION"];

                        var isValid = true;

                        for (var csvHeader in mandatoryHeaders) {

                            var currentHeader = mandatoryHeaders[csvHeader];

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

                                isValid = false;

                                return "Missing header :" + currentHeader;

                            }

                        }

                        if (isValid) {

                            return "All mandatory columns are filled";

                        }

                    }

                }

            }

        }

    },

});

If 2 columns are missing, it displays only one column name.

And, how can I validate all rows should contain data and if it doesn't it should throw an error?

Thanks in advance

[Ankur Bawiskar · Accepted solution]

@User205031 

try this

var AttachmentSize = Class.create();
AttachmentSize.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    checkAttachment: function() {
        var login = this.getParameter('sysparm_cart_item_id');
        var table_name = "sc_cart_item";
        gs.info('SS7' + table_name);

        var gr = new GlideRecord('sys_attachment');
        gr.addQuery('table_name', 'sc_cart_item');
        gr.addQuery('sys_created_by', login);
        gr.orderByDesc('sys_created_on');
        gr.setLimit(1);
        gr.query();
        if (gr.next()) {
       
            if (gr.getValue("size_bytes") > 10000) {
                return "File size must be less than 10kb."; // Too big
            } else if (gr.getValue("size_bytes") < 10000) {
                if (table_name == "sc_cart_item") {
                    var a = [];
                    var table_sys_id = gr.table_sys_id;
                    gs.info('SS28' + table_sys_id + gr.size_bytes + gr.sys_created_on);
                   
                    var gsa = new GlideSysAttachment();
                    var bytesInFile = gsa.getBytes(table_name, table_sys_id);
                    gs.info('SS33' + bytesInFile);
                    var originalContentsInFile = Packages.java.lang.String(bytesInFile);
                    gs.info('SS35' + originalContentsInFile);
                    originalContentsInFile = String(originalContentsInFile);
                    var fileData = originalContentsInFile.split('\n'); //split for new line
                    gs.info("SS38:" + " " + fileData);
                    var csvHeaders = fileData[0];
                    gs.info('SS40 Headers: ' + csvHeaders);
                    a = csvHeaders.split(',');
                    gs.info("length" + a.length);
                    for (i = 0; i < a.length; i++) {
                        gs.info("Array element: " + i + ", value: " + a[i]);
                    }

                    var fileLength = fileData.length;
                    gs.info('SS57' + fileLength);
                    
                    // Check for missing headers
                    var mandatoryHeaders = ["TOWER", "ENTERED_HOSTNAME", "SWITCH_IP", "NETMASK", "MARSHA_CODE", "MODEL", "SNMP_CONTACT", "SNMP_LOCATION", "ISE_LOCATION"];
                    var missingHeaders = [];
                    for (var i = 0; i < mandatoryHeaders.length; i++) {
                        var currentHeader = mandatoryHeaders[i];
                        if (csvHeaders.indexOf(currentHeader) === -1) {
                            missingHeaders.push(currentHeader);
                        }
                    }
                    if (missingHeaders.length > 0) {
                        return "Missing headers: " + missingHeaders.join(', ');
                    }

                    // Validate each row
                    for (i = 1; i < fileData.length; i++) {
                        var rowDetails = fileData[i].trim();
                        if (rowDetails) { // Check if the row is not empty
                            var rowValues = rowDetails.split(',');
                            gs.info("Row: " + i + " : " + rowDetails);
                            gs.info('Row 1' + rowValues);
                            gs.info('Row 2' + rowValues.length);
                            if (rowValues.length !== mandatoryHeaders.length || rowValues.includes('')) {
                                return "Row " + (i + 1) + " is incomplete or missing data.";
                            }
                        }
                    }

                    return "All mandatory columns are filled and all rows contain data.";
                }
            }
        }
    },

});

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

[User205031]

Hi @Ankur Bawiskar ,

Thanks a lot for your help and I have accepted the previous solution as correct.

Can you please let me know how we can check for an empty file.

Thanks! 

[Ankur Bawiskar]

@User205031 

I shared below

Please mark the below as correct answer as well.

[Ankur Bawiskar · Accepted solution]

@User205031 

to enhance further as per your new requirement use this

var AttachmentSize = Class.create();
AttachmentSize.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    checkAttachment: function() {
        var login = this.getParameter('sysparm_cart_item_id');
        var table_name = "sc_cart_item";
        gs.info('SS7' + table_name);

        var gr = new GlideRecord('sys_attachment');
        gr.addQuery('table_name', 'sc_cart_item');
        gr.addQuery('sys_created_by', login);
        gr.orderByDesc('sys_created_on');
        gr.setLimit(1);
        gr.query();
        if (gr.next()) {
       
            if (gr.getValue("size_bytes") > 10000) {
                return "File size must be less than 10kb."; // Too big
            } else if (gr.getValue("size_bytes") < 10000) {
                if (table_name == "sc_cart_item") {
                    var a = [];
                    var table_sys_id = gr.table_sys_id;
                    gs.info('SS28' + table_sys_id + gr.size_bytes + gr.sys_created_on);
                   
                    var gsa = new GlideSysAttachment();
                    var bytesInFile = gsa.getBytes(table_name, table_sys_id);
                    gs.info('SS33' + bytesInFile);
                    var originalContentsInFile = Packages.java.lang.String(bytesInFile);
                    gs.info('SS35' + originalContentsInFile);
                    originalContentsInFile = String(originalContentsInFile);
                    var fileData = originalContentsInFile.split('\n'); //split for new line
                    gs.info("SS38:" + " " + fileData);
                    
                    // Check if the file is empty
                    if (fileData.length === 0 || (fileData.length === 1 && fileData[0].trim() === '')) {
                        return "The file is empty.";
                    }

                    var csvHeaders = fileData[0];
                    gs.info('SS40 Headers: ' + csvHeaders);
                    a = csvHeaders.split(',');
                    gs.info("length" + a.length);
                    for (i = 0; i < a.length; i++) {
                        gs.info("Array element: " + i + ", value: " + a[i]);
                    }

                    var fileLength = fileData.length;
                    gs.info('SS57' + fileLength);
                    
                    // Check for missing headers
                    var mandatoryHeaders = ["TOWER", "ENTERED_HOSTNAME", "SWITCH_IP", "NETMASK", "MARSHA_CODE", "MODEL", "SNMP_CONTACT", "SNMP_LOCATION", "ISE_LOCATION"];
                    var missingHeaders = [];
                    for (var i = 0; i < mandatoryHeaders.length; i++) {
                        var currentHeader = mandatoryHeaders[i];
                        if (csvHeaders.indexOf(currentHeader) === -1) {
                            missingHeaders.push(currentHeader);
                        }
                    }
                    if (missingHeaders.length > 0) {
                        return "Missing headers: " + missingHeaders.join(', ');
                    }

                    // Validate each row and accumulate errors
                    var rowErrors = [];
                    for (i = 1; i < fileData.length; i++) {
                        var rowDetails = fileData[i].trim();
                        if (rowDetails) { // Check if the row is not empty
                            var rowValues = rowDetails.split(',');
                            gs.info("Row: " + i + " : " + rowDetails);
                            gs.info('Row 1' + rowValues);
                            gs.info('Row 2' + rowValues.length);
                            if (rowValues.length !== mandatoryHeaders.length || rowValues.includes('')) {
                                rowErrors.push("Row " + (i + 1) + " is incomplete or missing data.");
                            }
                        }
                    }

                    if (rowErrors.length > 0) {
                        return rowErrors.join('\n');
                    }

                    return "All mandatory columns are filled and all rows contain data.";
                }
            }
        }
    },

});

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

[User205031]

Hi @Ankur Bawiskar , thanks a lot for your help. It is working now.

[Ankur Bawiskar]

Glad to help