Introduction
Have you ever wanted to know how to process and Excel file without using import sets, keep reading.. this article is going to rock your ServiceNow world.
Business Use Case
One of my customers wanted an easy way to load CIs in the Impacted Services/CIs related list within the Change form using an Excel file. We're not talking about few dozens or hundreds of CIs, but thousands.
They wanted the capability to choose an Excel sheet and use the name or serial numbers of the CIs to include them in the Change request.
OOB Import Sets in the only available choice to load data, but what IF we could parse the file using JavaScript in the client side...
The Solution
I found a JavaScript library called SheetJS which turnout to be very impressive in the way it handles an Excel file on the client side.
This library can actually parse an Excel file (xlsx) in the client script and it can be converted into JSON format
 |
|
SheetJS API
These are common functions used of the SheetJS API to parse the Excel sheet:
/* Read the file */
var reader = new FileReader();
...
/* get the workbook */
var workbook = X.read(arrayBuffer, {type:'array'});
/* get the sheet name */
var sheetName = workbook.SheetNames;
/* get the sheet */
var sheet = workbook.Sheets[sheetName];
/* obtain the JSON object of the sheet and stringify */
var data = JSON.stringify(X.utils.sheet_to_row_object_array(sheet));
How many data can be handle?
One of the biggest concerns about this approach is managing the big amount of data in the client and whether GlideAjax can handle that amount of data or not... however I did a reasonable stress test using an excel file with just three columns (Name, Serial numbers, Class) containing ~7300 CIs rows.
the result is impressive, it took only ~20 seconds to process the ~7300 CIs of data.
this approach also provides a list of data that was not able to be found in this case within the cmdb_ci table. This is a plus because the spreadsheet may have additional CIs that aren't still available in the CMDB yet and for my customer it was required to know what CIs could not be found.
In the next sections you will find a real-working use case to load CIs from an Excel file (.xlsx) and all the artifacts that you need to make it work and try it yourself.
I hope this solution expands your mind to apply and reuse all the code that I'm proving here to extend more features and widgets to your requirements.
In order to run the example just open any Change Request and go to the Related Lists at the bottom of the form, you'll see the button Load CIs
Please mark this article as useful and share it across your contacts if you find it was a great help to achieve other solutions of your needs.
Technical Solution
From this point to the bottom you'll find all the artifacts that you need to run this real-scenario requirement in your own instance.
Libraries Required
You can find the libraries required directly on the SheetJS project site in GitHub. Here are the resources needed:
XLSX
GitHub: https://github.com/SheetJS/sheetjs/blob/master/xlsx.js
Raw: https://raw.githubusercontent.com/SheetJS/sheetjs/master/xlsx.js
JSZIP
GitHub: https://github.com/SheetJS/sheetjs/blob/master/jszip.js
Raw: https://raw.githubusercontent.com/SheetJS/sheetjs/master/jszip.js
UI Scripts
For each JavaScript library you're going to create a UI Script to have something like this:
Script Include
Create an Script Include with the following information:
Name: AjaxExcelLoader
Client callable: true
var AjaxExcelLoader = Class.create();
AjaxExcelLoader.prototype = Object.extendsObject(AbstractAjaxProcessor, {
addData: function() {
/* obtain all the data from the AJAX call in UI Page: ExcelDataLoader */
var lookupTable = this.getParameter("sysparm_lookup_table");
var lookupField = this.getParameter("sysparm_lookup_field");
var targetTable = this.getParameter("sysparm_target_table");
var targetField = this.getParameter("sysparm_target_field");
var referenceField = this.getParameter("sysparm_parent_field");
var parentRecordId = this.getParameter("sysparm_parent_value");
var excelColumnName = this.getParameter("sysparm_excel_column_name");
var data = new JSON().decode(this.getParameter("sysparm_data"));
/* if there is no data, return error message */
if(data.length < 1) {
return new JSON().encode({
status: 'error',
message: 'No data to process in the file'
});
}
var foundList = [];
var notFoundList = [];
var targetValue = null;
var searchValue = null;
var rec = null;
for(var n=0; n < data.length; n++) {
/* get the value of the column from the 'n' row of the Excel sheet */
searchValue = data[n][excelColumnName];
/* query the target table to check if the given search value exists */
rec = this.findRecord(lookupTable, lookupField, searchValue);
if(rec) {
foundList.push(searchValue);
targetValue = rec.getValue("sys_id").toString();
this.createRecord(targetTable, targetField, targetValue, referenceField, parentRecordId);
} else {
notFoundList.push(searchValue);
}
}
/* the following code computes the results with custom messages */
var notFoundMessage = "";
var notFoundDisplay = "";
var notFoundVerbiage = ( (notFoundList.length == 1) ? " record" : " records" ) + " could not be found.";
if(notFoundList.length > 0) {
notFoundMessage = "; " + notFoundList.length + notFoundVerbiage;
/* convert array into multiline text */
notFoundDisplay = notFoundList.join("\n");
}
var foundVerbiage = ( (foundList.length == 1) ? " record" : " records" ) + " have been added.";
return new JSON().encode({
status: foundList.length == 0 ? "error" : "success",
message: foundList.length + foundVerbiage + notFoundMessage,
notFound: notFoundDisplay,
totalRows: foundList.length + notFoundList.length
});
},
/*
this function is use to find a record from a given value for a specific table and field
@params
table name of the table to search
fieldName name of the field where to search
value the value of the field that must be validated that exists
*/
findRecord: function(table, fieldName, value) {
var gr = new GlideRecord(table);
gr.addQuery(fieldName, value);
gr.query();
if(gr.next()) {
return gr;
}
return null;
},
/*
this function is use to create a records in a given target table
@params
targetTable the table name where the record is going to be created
targetField the field name that will be populated with the value
targetValue the value that populates targetField
referenceField the field name that will link to another record
referenceValue the value of the referenceField
*/
createRecord: function(targetTable, targetField, targetValue, referenceField, referenceValue) {
var gr = new GlideRecord(targetTable);
gr.initialize();
gr.setValue(targetField, targetValue);
gr.setValue(referenceField, referenceValue);
return gr.insert();
},
type: 'AjaxExcelLoader'
});
UI Page
Create an UI Page with the following details:
Name: ExcelDataLoader
HTML:
<?xml version="1.0" encoding="utf-8" ?>
<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">
<!-- Include Excel libraries to load files -->
<g:include_script src="jszip.jsdbx" />
<g:include_script src="xlsx.jsdbx" />
<!-- Get values from dialog preferences passed in -->
<g:evaluate var="jvar_lookup_table" expression="RP.getWindowProperties().get('ui_lookup_table')" />
<g:evaluate var="jvar_lookup_field" expression="RP.getWindowProperties().get('ui_lookup_field')" />
<g:evaluate var="jvar_target_table" expression="RP.getWindowProperties().get('ui_target_table')" />
<g:evaluate var="jvar_target_field" expression="RP.getWindowProperties().get('ui_target_field')" />
<g:evaluate var="jvar_parent_field" expression="RP.getWindowProperties().get('ui_parent_field')" />
<style>
.loader {
margin-top: 5px;
border: 5px solid #f3f3f3;
border-radius: 50%;
border-top: 5px solid #1F8476;
width: 28px;
height: 28px;
-webkit-animation: spin 1s linear infinite; /* Safari */
animation: spin 1s linear infinite;
}
/* Safari */
@-webkit-keyframes spin {
0% { -webkit-transform: rotate(0deg); }
100% { -webkit-transform: rotate(360deg); }
}
@keyframes spin {
0% { transform: rotate(0deg); }
100% { transform: rotate(360deg); }
}
.stats {
margin-top: 5px;
font-size: 11px;
}
.stats td {
padding-right: 5px;
}
.main td {
padding: 5px;
}
.excel-file-picker, #columnName {
margin-bottom:5px;
}
.processing {
font-style: italic;
color:blue;
}
.not-found-list {
background-color:#f2f3f4;
}
</style>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
</head>
<body>
<table class="main">
<tr style="height: 25px; background-color:#E6E9EB">
<td>Load data using an Excel file (.xlsx)</td>
<td>Values not found</td>
</tr>
<tr>
<td valign="top">
<label title="The label/name of the column used in the header row of the spreadsheet">1) Enter Column Label:</label>
<input class="form-control" type="text" value="" id="columnName" />
<label>2) Select an Excel file</label>
<input class="excel-file-picker" id="excelFile" type="file" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" />
<div id="loaderWidget" style="display: none">
<div class="loader"></div>
<div class="processing">Processing...</div>
</div>
<table class="stats" id="stats" style="display: none">
<tr>
<td>Total rows processed</td>
<td><span id="rowCount">1028</span></td>
</tr>
<tr>
<td>Time elapsed</td>
<td><span id="timeElapsed">5.361</span>$[SP] seconds</td>
</tr>
</table>
</td>
<td valign="top">
<button class="action_context btn btn-primary" style="position: absolute;right: 10px;top: 65px;" onclick="copyToClipboard()" title="Copy to clipboard">Copy</button>
<textarea class="form-control not-found-list" style="height: 300px !important" id="notFoundList" rows="25" cols="50"></textarea>
</td>
</tr>
</table>
<div id="statusPanel"></div>
<p></p>
<div style="width:20%; margin:auto;">
<button id="cancelbtn" class="form_action_button header action_context btn btn-default" onclick="closeForm()" title="Close this window">Close</button>
</div>
<!-- set hidden fields with values from parameters -->
<input id="ui_lookup_table" type="hidden" value="${jvar_lookup_table}"/>
<input id="ui_lookup_field" type="hidden" value="${jvar_lookup_field}"/>
<input id="ui_target_table" type="hidden" value="${jvar_target_table}"/>
<input id="ui_target_field" type="hidden" value="${jvar_target_field}"/>
<input id="ui_parent_field" type="hidden" value="${jvar_parent_field}"/>
</body>
</j:jelly>Client Script:
var X = XLSX;
$("notFoundList").value='';
function closeForm() {
GlideDialogWindow.get().destroy();
}
function copyToClipboard() {
var copyText = $("notFoundList");
/* Select the text field */
copyText.select();
/* Copy the text inside the text field */
document.execCommand("copy");
}
/*
this function converts the sheet into JSON format data
*/
function toJSON(sheet) {
return X.utils.sheet_to_row_object_array(sheet);
}
/*
this function obtains the Excel (.xlsx) and reads it's content to obtain JSON format data
*/
function parseExcelFile(e) {
if($("columnName").value == '') {
$('excelFile').value = '';
/* build the status message received from back-end */
$("statusPanel").update(buildStatus({status:'error', message:"You must provide 'Column Label' vaue"}));
setTimeout(function(){ $("statusPanel").update(''); }, 3000);
$("columnName").focus();
return;
}
/* Display the loader */
$("loaderWidget").show();
/* disabl the file picker */
$("excelFile").disable();
/* disable the Close button */
$("cancelbtn").disable();
/* disable Column Name field */
$("columnName").disable();
/* Get the files */
var files = e.target.files || [];
/* If no files exit the function */
if (!files.length) return;
/* obtain the first file */
var file = files[0];
/* Read the file */
var reader = new FileReader();
/* add event when the file load is completed */
reader.onloadend = function(event) {
var arrayBuffer = reader.result;
var options = {
type: 'array'
};
/* get the workbook */
var workbook = X.read(arrayBuffer, options);
/* get the sheet name */
var sheetName = workbook.SheetNames;
/* get the sheet */
var sheet = workbook.Sheets[sheetName];
/* obtain the JSON object of the sheet and stringify */
var data = JSON.stringify(toJSON(sheet));
var startTime = Date.now();
/* get the sys_id of the current record displayed on the form */
var parentValue = g_form.getUniqueValue().toString();
var ga = new GlideAjax("AjaxExcelLoader");
ga.addParam("sysparm_name", "addData");
ga.addParam("sysparm_lookup_table", $("ui_lookup_table").value);
ga.addParam("sysparm_lookup_field", $("ui_lookup_field").value);
ga.addParam("sysparm_target_table", $("ui_target_table").value);
ga.addParam("sysparm_target_field", $("ui_target_field").value);
ga.addParam("sysparm_parent_field", $("ui_parent_field").value);
ga.addParam("sysparm_parent_value", parentValue);
ga.addParam("sysparm_excel_column_name", $("columnName").value);
ga.addParam("sysparm_data", data);
ga.getXML(function(response) {
var oslova = response.responseXML.documentElement.getAttribute("answer");
var res = JSON.parse(oslova);
/* Hide the loader */
$("loaderWidget").hide();
/* enable Close buton */
$("cancelbtn").enable();
/* build the status message received from back-end */
$("statusPanel").update(buildStatus(res));
/* populate not found list if data is received */
if(res.notFound != '') {
$("notFoundList").value= res.notFound;
}
/* obtain new time to calculate duration */
var endTime = Date.now();
/* set the amout of seconds duration it took to load the data */
$("timeElapsed").innerHTML = (endTime-startTime)/1000;
/* set the total rows processed */
$("rowCount").innerHTML = res.totalRows;
/* display statistics */
$("stats").show();
/* Refresh the realted list */
GlideList2.get(g_form.getTableName() + '.task_cmdb_ci_service.task').setFilterAndRefresh('');
/* Reload the complete form */
/*window.location.reload();*/
});
};
reader.readAsArrayBuffer(file);
}
function buildStatus(res) {
var oslova = res.status == 'error' ? "red" : "green";
return "<div style='padding: 5px; color:white; background-color:"+oslova+"'>"+res.status+" :: " + res.message+"</div>";
}
var excelFile = $('excelFile');
/* add the event to process the file when the user has chosen an Excel file */
if (excelFile && excelFile.addEventListener) {
excelFile.addEventListener('change', parseExcelFile, false);
}
UI Button
Create a UI Button with the following details:
Name: Load CIs
Table: Impacted CIs [task_cmdb_ci_service]
Client: true
List banner button: true
Onclick: loadExcelCIList()
Condition: RP.isRelatedList()
Script:
function loadExcelCIList() {
var gdw = new GlideDialogWindow('ExcelDataLoader');
gdw.setTitle('Import Impacted Services/CIs');
gdw.setSize(550,300);
gdw.setPreference("ui_lookup_table", "cmdb_ci");
gdw.setPreference("ui_lookup_field", "serial_number");
gdw.setPreference("ui_target_table", "task_cmdb_ci_service");
gdw.setPreference("ui_target_field", "cmdb_ci_service");
gdw.setPreference("ui_parent_field", "task");
gdw.render();
}
You can fin attached to this article the Update Set with all the code.
ENJOY!!! and share the knowledge...
Regards,
Oscar Lopez
@oslovanet
