You are currently viewing How To Parse Excel Sheets In ServiceNow?

How To Parse Excel Sheets In ServiceNow?

Parsing things can be a pain but it is one of the daily exercise for any developer. ServiceNow developers are also getting requirements where there is a need to parse excel, JSON or any other data source and finally update the stuff in servicenow tables.

Keeping this in mind servicenow provided an API called GlideExcelParser to sort-out the problem of parsing excels. Parse .xlsx formatted Excel files and access file data in script.

The GlideExcelParser methods can be used in global and scoped scripts. Use the sn_impex namespace identifier to create a GlideExcelParser object.

Example

In the example, I am parsing an excel named “DemoExcel“. It has two sheets named “DemoSheet1” & “DemoSheet2” respectively. Both excel sheets has 3 rows of data

Checkout the excel here – Click Here

And finally, I am displaying the excel content in the background script as shown in the image below.

Steps to Parse The Excel

We have used the functions of out of box GlideExcelParser class using below script.

You can use this script directly, just change the sysID of the attachment from your end.

var parser = new sn_impex.GlideExcelParser();
var attachment = new GlideSysAttachment();
// Use the attachment sys_id of an Excel file . 
// Returns a GlideScriptableInputStream object given the sys_id of an attachment.

var attachmentStream = attachment.getContentStream("8a826e5d0798e1105ff1f9fc7c1ed075");
// Set the source to be parsed
parser.setSource(attachmentStream);
// Get the worksheet names to be parsed in the Excel document
var list_sheet_name = parser.getSheetNames();
gs.info(" Sheet Names " + list_sheet_name.join(", "));
// Iterate over each worksheet in the Excel workbook
for (var i = 0; i < list_sheet_name.length; i++) {
gs.info("**************************************************************************************");
gs.info("Sheet name: " + list_sheet_name[i]);
// Set the worksheet name to be parsed
parser.setSheetName(list_sheet_name[i]);
// Parse each worksheet set using setSheetName()
if (parser.parse()) {
//retrieve the column headers
var headers = parser.getColumnHeaders();
var header1 = headers[0];
var header2 = headers[1];
var header3 = headers[2];
//print headers
gs.info(header1 + "\t||" + header2 + "\t||" + header3);
// Iterate over each row in the worksheet
while (parser.next()) {
var row = parser.getRow();
//print row value for both columns 
gs.info(row[header1] + '\t|| ' + row[header2] + '\t||' + row[header3]);
}
} else
gs.info(parser.getErrorMessage());
}

//Output
*** Script:  Sheet Names DemoSheet1, DemoSheet2
*** Script: **************************************************************************************
*** Script: Sheet name: DemoSheet1
Both sheetname and sheetnumber are set, sheetname will take precedence
*** Script: Header1	||Header2	||Header3
*** Script: Row1A	|| Row1B	||Row1C
*** Script: Row2A	|| Row2B	||Row2C
*** Script: **************************************************************************************
*** Script: Sheet name: DemoSheet2
Both sheetname and sheetnumber are set, sheetname will take precedence
*** Script: Header1	||Header2	||Header3
*** Script: Row1A	|| Row1B	||Row1C
*** Script: Row2A	|| Row2B	||Row2C

Video Tutorial

Leave a Reply