How to validate if a file is in excel format from flow designer action?

[User205031]

Hi All,

I have one excel file which will be attached to a RITM. I need to validate if the file attached is in excel format or not and also need to validate the headers.

I have written an action in flow designer but it is returning 'The file is not in excel format'

I am not sure what is wrong in the code.

(function execute(inputs, outputs) {

var attachment_sysID = '';

var gd = new GlideRecord('sys_attachment');

gd.addQuery('table_name',inputs.table);

gd.addQuery('table_sys_id',inputs.tablesysid);

gd.query();

if (gd.next()) {

    attachment_sysID = gd.sys_id;

}

try{

    var parser = new sn_impex.GlideExcelParser();

    var attachment = new GlideSysAttachment();

    // use attachment sys id of an excel file

    var attachmentStream = attachment.getContentStream(attachment_sysID);

    parser.parse(attachmentStream);

    if(!parser.parse(attachmentStream)){

    throw 'The file is not in excel format'

    //gs.info('The file is not in excel format');

    }

    var headers = parser.getColumnHeaders();

    if(headers.length != 9){

    gs.info('The excel is not in correct format');

    }

    if(headers[0] != 'TOWER' || headers[1] != 'ENTERED_HOSTNAME' || headers[2] != 'SWITCH_IP' || headers[3] != 'NETMASK' || headers[4] != 'MARSHA_CODE' ||

       headers[5] != 'MODEL' || headers[6] != 'SNMP_CONTACT' || headers[7] != 'SNMP_LOCATION' || headers[8] != 'ISE_LOCATION'){

    throw 'The Excel files has incorrect headers'

    }

    gs.info('SS1' + headers);

var count1 = 0;

var count2 = 0;

var arrofarr = [];

while (parser.next()) {

    count1++;

    var arr = {};

    var row = parser.getRow();

    if (row['TOWER'] != null) {

        count2++;

        arr.hostname = row['ENTERED_HOSTNAME'];

        arr.switchip = row['SWITCH_IP'];

        arr.netmask = row['NETMASK'];

        arr.marsha = row['MARSHA_CODE'];

        arr.model = row['MODEL'];

        arr.snmp = row['SNMP_CONTACT'];

        arr.snmploc = row['SNMP_LOCATION'];

        arr.iseloc = row['ISE_LOCATION'];

    arrofarr.push(arr);

}

}

if(count1 == 0){

    throw 'The excel uploaded is empty'

}

if(count2 == 0){

    throw 'The excel uploaded has incorrect data'

}

}

catch(err){

 outputs.error = err;

}

outputs.jsonoutput = JSON.stringify(arrofarr);

outputs.count1 = 'Total rows ' + count1;

outputs.count2 = 'Total rows after parsing ' + count2;

})(inputs, outputs);

Please advice what to change in the code.

Thanks in advance!

[Ankur Bawiskar · Accepted solution]

@User205031 

try this and check the logs

1) I removed parser.parse() and kept only once

(function execute(inputs, outputs) {
    var attachment_sysID = '';
    var gd = new GlideRecord('sys_attachment');
    gd.addQuery('table_name', inputs.table);
    gd.addQuery('table_sys_id', inputs.tablesysid);
    gd.query();
    if (gd.next()) {
        attachment_sysID = gd.sys_id;
    }

    try {
        var parser = new sn_impex.GlideExcelParser();
        var attachment = new GlideSysAttachment();
        var attachmentStream = attachment.getContentStream(attachment_sysID);

        if (!parser.parse(attachmentStream)) {
            throw 'The file is not in excel format';
        }

        var headers = parser.getColumnHeaders();

        if (headers.length != 9) {
            throw 'The excel is not in correct format';
        }

        if (headers[0] != 'TOWER' || headers[1] != 'ENTERED_HOSTNAME' || headers[2] != 'SWITCH_IP' || headers[3] != 'NETMASK' || headers[4] != 'MARSHA_CODE' ||
            headers[5] != 'MODEL' || headers[6] != 'SNMP_CONTACT' || headers[7] != 'SNMP_LOCATION' || headers[8] != 'ISE_LOCATION') {
            throw 'The Excel file has incorrect headers';
        }

        gs.info('SS1' + headers);

        var count1 = 0;
        var count2 = 0;
        var arrofarr = [];

        while (parser.next()) {
            count1++;
            var arr = {};
            var row = parser.getRow();
            if (row['TOWER'] != null) {
                count2++;
                arr.hostname = row['ENTERED_HOSTNAME'];
                arr.switchip = row['SWITCH_IP'];
                arr.netmask = row['NETMASK'];
                arr.marsha = row['MARSHA_CODE'];
                arr.model = row['MODEL'];
                arr.snmp = row['SNMP_CONTACT'];
                arr.snmploc = row['SNMP_LOCATION'];
                arr.iseloc = row['ISE_LOCATION'];
                arrofarr.push(arr);
            }
        }

        if (count1 == 0) {
            throw 'The excel uploaded is empty';
        }
        if (count2 == 0) {
            throw 'The excel uploaded has incorrect data';
        }

    } catch (err) {
        outputs.error = err;
    }

    outputs.jsonoutput = JSON.stringify(arrofarr);
    outputs.count1 = 'Total rows ' + count1;
    outputs.count2 = 'Total rows after parsing ' + count2;

})(inputs, outputs);

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

[User205031]

Hi Ankur,

I am still getting the same error. 

I have even added the logs as below:

(function execute(inputs, outputs) {

var attachment_sysID = '';

var gd = new GlideRecord('sys_attachment');

gd.addQuery('table_name',inputs.table);

gd.addQuery('table_sys_id',inputs.tablesysid);

gd.query();

if (gd.next()) {

    attachment_sysID = gd.sys_id;

    gs.info('SS2' + attachment_sysID);

}

try{

    var parser = new sn_impex.GlideExcelParser();

    var attachment = new GlideSysAttachment();

    // use attachment sys id of an excel file

    var attachmentStream = attachment.getContentStream(attachment_sysID);

    gs.info('SS3' + attachmentStream);

    //parser.parse(attachmentStream);

    //gs.info('SS4' + parser.parse(attachmentStream));

    if(!parser.parse(attachmentStream)){

    throw 'The file is not in excel format';

    //gs.info('The file is not in excel format');

    }

    var headers = parser.getColumnHeaders();

    if(headers.length != 9){

    throw 'The excel is not in correct format';

    }

    if(headers[0] != 'TOWER' || headers[1] != 'ENTERED_HOSTNAME' || headers[2] != 'SWITCH_IP' || headers[3] != 'NETMASK' || headers[4] != 'MARSHA_CODE' ||

       headers[5] != 'MODEL' || headers[6] != 'SNMP_CONTACT' || headers[7] != 'SNMP_LOCATION' || headers[8] != 'ISE_LOCATION'){

    throw 'The Excel files has incorrect headers'

    }

    gs.info('SS1' + headers);

var count1 = 0;

var count2 = 0;

var arrofarr = [];

while (parser.next()) {

    count1++;

    var arr = {};

    var row = parser.getRow();

    if (row['TOWER'] != null) {

        count2++;

        arr.hostname = row['ENTERED_HOSTNAME'];

        arr.switchip = row['SWITCH_IP'];

        arr.netmask = row['NETMASK'];

        arr.marsha = row['MARSHA_CODE'];

        arr.model = row['MODEL'];

        arr.snmp = row['SNMP_CONTACT'];

        arr.snmploc = row['SNMP_LOCATION'];

        arr.iseloc = row['ISE_LOCATION'];

    arrofarr.push(arr);

}

}

if(count1 == 0){

    throw 'The excel uploaded is empty';

}

if(count2 == 0){

    throw 'The excel uploaded has incorrect data';

}

}

catch(err){

 outputs.error = err;

}

outputs.jsonoutput = JSON.stringify(arrofarr);

outputs.count1 = 'Total rows ' + count1;

outputs.count2 = 'Total rows after parsing ' + count2;

})(inputs, outputs);

in log SS3 I am getting : com.glide.communications.GlideScriptableInputStream@44e20ea

[Ankur Bawiskar]

@User205031 

are you adding the correct file type? the logs didn't come after that line?

GlideExcelParser -> supports only .xlsx

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

[User205031]

Hi Ankur,

Yes i was uploading csv file hence i was getting the error.

Thanks