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.

[Ankur Bawiskar]

@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 = "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);
                    
                    // 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].split(',');
                        if (rowDetails.length !== mandatoryHeaders.length || rowDetails.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,

Missing header is working but not the data validation in each row.

//Validate each row

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

                        gs.info("Row: " + i + " : " + fileData[i]);

                        var rowDetails = fileData[i].split(',');

                        gs.info('Row 1' + rowDetails);

                        gs.info('Row 2' + rowDetails.length);

                        if (rowDetails.length != mandatoryHeaders.length && rowDetails.includes('')) {

                            return "The file has incomplete or missing data. Please validate the file";

                        }

                    }

Even if one cell is missing data, Row 2 is counting the blank cell.

[Ankur Bawiskar]

@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 = "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);
                    
                    // 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].split(',');
                        gs.info("Row: " + i + " : " + fileData[i]);
                        gs.info('Row 1' + rowDetails);
                        gs.info('Row 2' + rowDetails.length);
                        if (rowDetails.length !== mandatoryHeaders.length || rowDetails.includes('')) {
                            return "Row " + (i + 1) + " is incomplete or missing data.";
                        }
                    }

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

});

Changes Made:
1) Logical OR Operator: Changed the condition to if (rowDetails.length !== mandatoryHeaders.length || rowDetails.includes('')) to ensure it checks for either condition being true.

2) Logging: Added logging to help debug the row validation process

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

[User205031]

Hi Ankur,

I tried this but not working... My CSV file has 1 header & 3 rows.. One of the row has one data missing.

below are the logs:

Row: it is showing 4 rows where as the CSV file has 3 rows

Row 1:

Row 2:

Even if one cell is blank, it is counting.

MY CSV: