How to get row count of attached excel sheet in catalog form in service now

[dell3]

How to get row count of attached excel sheet in catalog form in service now

[nikhil_001]

How would you calculate the number of rows in the attached excel in serviceportal for catalog item. We need to display the number of rows in one of the variables which we can show them in RITM also. No need to display and calculate on the form

[Ram050670]

Exactly what i was looking for Thanks @Jaspal Singh .

[nikhil_001]

HI everyone,

I have a requirement in which we need to check first check the mandatory attachment in serviceportal, which could be done OOB now. Along with that we need to check whether the attachment is excel or not , and if it is excel we need to count the filled rows in it. Any help will be appreciated. I have the code for checking whether the attachment is an excel or not, but its not working in portal as we are using Dom manipulation there . Below is the working code in the Service-now console and also i have the code which counts the excel rows but not able to achieve in portal.. Below are both the codes.

Excel mandatory working in console but not in portal:

Client script

function onSubmit() {
var cat_id = sysparm_item_guid.value;
var attObj = new GlideRecord('sys_attachment');
attObj.addQuery('table_name', 'sc_cart_item');
attObj.addQuery('table_sys_id', cat_id);
attObj.query();
if (attObj.hasNext()) {
while (attObj.next()) {
var typeMatch = 'ms-excel,spreadsheetml';
var typeStr = attObj.content_type.toString();
var typeChk = (typeStr.indexOf(typeMatch[0]) > -1 || typeStr.indexOf(typeMatch[1]) > -1);
var fName = attObj.file_name.toString();
var regex = /\.xlsx$/g;
var extChk = regex.test(fName);
if(!typeChk || (typeChk && !extChk)) {
alert("You must use an Excel spreadsheet for uploading, please remove the existing file and re-attach");
return false;
}

}
}
}

Counting nunmber of rows

Background script

var count=0;
var parser = new sn_impex.GlideExcelParser();
var attachment = new GlideSysAttachment();
// use attachment sys id of an excel file attachment
var attachmentStream = attachment.getContentStream('6e9c7aad1b772850c1534159cc4bcb5d'); //sys_id of record from sys_attachment table
parser.parse(attachmentStream);

//retrieve the column headers
var headers = parser.getColumnHeaders();
gs.print("Apoorva"+headers );
var key = headers[0];
var value = headers[1];
while(parser.next())
{
count++;
var row = parser.getRow();
//print row value for both columns
gs.print(row[value]) ; //Uncomment this to get actual data.
}

gs.print('Number of Rows in excel attached are '+count);

[Allen Andreas]

Hi,

Please create your own question so the conversation can be tracked appropriately.

You're posting this across numerous other threads.

https://community.servicenow.com/community?id=community_question&sys_id=13f687e5db1cdbc01dcaf3231f96...

https://community.servicenow.com/community?id=community_question&sys_id=4216f05adb3590106c1c02d5ca96...

Over 6+ threads at this point.

Thanks!