Excel file attached is not opening (file format changed)

[Rahul Raja Sami]

Hi,

I am using Glide excel parser to fetch the data from the excel sheet and with the help of cart api , creating requests as per the sheet and also tried to attach the same sheet for every request.

sheet is getting attached but the file is not opening and displaying a message as below 

" Excel sheet cannot open the file "name" because the file format or file extension is not valid. Verify that file has not been corrupted and that the file extension matches the file format."

this is the after update BR written on attachent table

 var headerRowNumber = 12;

    var attachmentStream = new GlideSysAttachment().getContentStream(current.sys_id);
    var parser = new sn_impex.GlideExcelParser();
    parser.setHeaderRowNumber(headerRowNumber);
    if (!parser.parse(attachmentStream)) {
        throw "Attachment could not be parsed as an Excel Spreadsheet " + current.sys_id;
    }
    //retrieve the column headers
    var headers = parser.getColumnHeaders();
    var header1 = headers[0];
    var header2 = headers[1];
    var header3 = headers[2];
    var header4 = headers[3];
    var header5 = headers[4];
    var header6 = headers[5];
    var header7 = headers[6];
    var header8 = headers[7];
    var header9 = headers[8];
    var header10 = headers[9];
    var header11 = headers[10];
    var header12 = headers[11];
    var header13 = headers[12];
    var header14 = headers[13];
    var header15 = headers[14];
    var header16 = headers[15];

    //print headers
    //     gs.info(header1 + " " + header2 + " " + header3 + " " + header4);
    var mt = 0;
    while (parser.next() && mt == 0) {
        var row = parser.getRow();
        if (row[header2] != 'Total') {

            var cartId = GlideGuid.generate(null);
            var cart = new Cart(cartId); //calling the cart API  
            //var cart = new Cart();   //calling the cart API  
            var item = cart.addItem('52d4943187b6e810d0d4ca27cebb3521');

            var message = "";
//             var gr1 = GlideRecord('sys_user');
//             gr1.addQuery('email',row[header16]);
//             gr1.query();
//             if (gr1.next()) {
                cart.setVariable(item, 'requested_by', '55061d071b8209105cf40f6cdc4bcb9a');
				
//             }
            var gr2 = GlideRecord('sys_user');
            gr2.addQuery('name', row[header11]);
            gr2.query();
            if (gr2.next()) {
                cart.setVariable(item, 'reporting_manager', gr2.sys_id);
            }

            var gr3 = GlideRecord('cmn_location');
            gr3.addQuery('name', row[header14]);
            gr3.query();
            if (gr3.next()) {
                cart.setVariable(item, 'location', gr3.sys_id);
            }
//             var success = true;
            cart.setVariable(item, 'first_name', row[header2]);
            cart.setVariable(item, 'last_name', row[header4]);
            cart.setVariable(item, 'off_user_email', row[header16]);
            cart.setVariable(item, 'access', 'GEAC');
            var gd = new GlideDate();
            gd.setDisplayValue(row[header7], "dd/MM/yyyy");
            var myDate = gd.getByFormat("yyyy-MM-dd");
            cart.setVariable(item, 'effective_dates', myDate);
            cart.setVariable(item, 'old_value', row[header9]);
			cart.setVariable(item, 'new_value', row[header10]);
            cart.setVariable(item, 'team', row[header13]);
            cart.setVariable(item, 'function', row[header12]);
            cart.setVariable(item, 'business', row[header15]);
            if (row[header11] != 'hr') {
                var rc = cart.placeOrder();

				current.table_name = 'sc_request'; // attaching the sheet to every request
            current.table_sys_id = rc.sys_id;
            current.insert();
			
				
                var tsk = new GlideRecord('sc_task');
                tsk.addQuery('request', rc.sys_id);
                tsk.query();
                while (tsk.next()) {

                    tsk.assignment_group = '39ca3dc087f6e410d0d4ca27cebb359c'; // add your value

                    tsk.short_description = 'Role Changes for IT'; //add your value
					tsk.variables.description = 'Location is' + " " + row[header14];


                    tsk.update();
                }
            }

        } else {
            mt = 1;
        }
    }

can anyone explain what did I miss?

[Tony Chatfield1]

Hi, unfortunately with no details of your attachment file it is not possible to simply reproduce your issue.
Based on your description of your issue\fault I would suspect that the problem is with the sys_attachment record, either with the file upload\save or the format of the file being uploaded.

- GlideExcelParser() supports XLSX format only not XLS, so you need to be sure the file is XLSX type.

GlideExcelParser - Scoped, Global | ServiceNow Developers

What were the results of your debugging?

Testing in PDI using your code to instantiate an XLSX attachment as 'parser'
I find no issues and the code runs successfully, but it errors if the file type is XLS.

 - Note I assume that 'throw' is a result of you not sharing your full code and is not a syntax error?

var headerRowNumber = 1;
var current = new GlideRecord('sys_attachment');
current.get('sysIdOfXLSXAttachment');
//current.get('29eb4e8c0a0a0b2e00ca52d175891526'); // OOB XLS attachment

var attachmentStream = new GlideSysAttachment().getContentStream(current.sys_id);
var parser = new sn_impex.GlideExcelParser();
parser.setHeaderRowNumber(headerRowNumber);
if (!parser.parse(attachmentStream)) {
gs.info("Attachment could not be parsed as an Excel Spreadsheet " + current.sys_id);
} else {
gs.info("nothing wrong here");
}

Perhaps you could check the sys_attachment record is correct file type and is being saved correctly.
If you require additional assistance, then please update this thread with details of your attachment file and attachment process.

[Rahul Raja Sami]

Hi Tony,

I have shared you the full script.

I will tell the complete scenario, here it goes like

whenever a mail is sent with a particular attachment we are creating it as an Interaction, once the Interaction is created from there we are using BR to create Requests using above script.

and the excel format is xlsx only , what I found is in the logs it is displaying as ''Attachment could not be parsed as an Excel Spreadsheet 1ed168601b902590fe087518dc4bcbe5''.

and I am not able to attach anything here the ss or images to this thread.

[Rahul Raja Sami]

Hi Tony 

I even tried to attach the sheet by changing its format to xls as well.

But when I sent a mail with xls format, it is not even creating the Requests.

But when I sent with the xlsx format, it is creating requests using the data from the sheet and also attaching to the Requests but not be able to view it when we download the sheet from the Request.

[Tony Chatfield1]

Perhaps start by adding an xlsx file manually to your instance and test to see if this works as expected - it worked for me in a PDI.