Simplifying Data Imports from Third Parties

///Simplifying Data Imports from Third Parties

Simplifying Data Imports from Third Parties

Recently along with my Crossfuze colleagues Josh McMullen and Scott Cornthwaite, I performed work for a client that needed to import asset data from several third party vendors. The company, a global manufacturer of household appliances, has dozens of vendors around the world, which supply data (CSV and Excel files) using proprietary formats and column names. The client’s desired future state is to enforce a single format for use by all vendors. But to control their hardware and software assets today, they needed a solution to work with multiple vendors and data formats.

We faced a few challenges. Our solution needed to:

  • Allow data imports without elevated roles or system privileges
  • Handle the same kind of data from multiple vendors (e.g. hardware asset imports)
  • Handle data in a variety of file formats including text (.csv) and MS Excel
  • Provide feedback to the client’s IT asset management (ITAM) team
  • Check data quality and handle exception conditions defined by the client
  • Run with minimal input or intervention

We started to create custom inbound email actions to process emails with attached data files as they were sent from each vendor. But we discovered some big downsides with that approach. The most serious was that there’s no way to validate the accuracy or completeness of data being sent by the vendor. Inbound email processing also leaves the client at the mercy of the vendor for the timing of the imports. Finally, it isn’t very scalable, since a new inbound email action must be created for every additional vendor and/or data source.

What the client needed was a simple way to perform a data import via a Data Source with a file attachment. They were already aware of the Load Data wizard that ServiceNow provides on the System Import Sets application menu, but that solution isn’t very user-friendly, and it requires a lot of manual input each time new data are imported.

Asset Import Wizard

To make it easy for the client’s ITAM team to import their data in to ServiceNow, we leveraged the power of the Service Catalog. Specifically, we created a Record Producer to provide a simple front-end for importing vendors’ data files. Record producers can be used by anyone. Their visibility can be limited to only interested parties. Files attached to the record producer are automatically attached to the record that is created. And the record producer’s Script field enables powerful data processing.

We configured the record producer as follows:

  • Name: Asset Import Wizard
  • Table: Data Source (sys_data_source)

We added a single variable to the record producer, a lookup select box to allow the ITAM team member to specify the type of import they were performing.

Import Wizard Configurations

The variable gets its list of options from a custom table called Import Wizard Configurations. This table allowed us to build a flexible framework for defining different types of file imports from any vendor.
This table has many fields that are similar to those on a Data Source record. That’s because the record producer’s script queries the Wizard Configuration table for values to use when it inserts a new record in the Data Source table. Here’s how this form breaks down.

  • Vendor references records in the Company (core_company) table where the Vendor field is set to true.
  • Import Type is a choice list of options that describe the kind of asset data being imported (e.g. Hardware, Software, HW end of life disposal, or Lease Contract)
  • Expected Data Format is a choice list that allows either CSV or Excel (.xls) formats to be specified.
  • Data Source Name Prefix is a text field for naming the data source. The record producer’s script automatically appends the current time/date stamp to the prefix when each Data Source record is created
  • Import Set Table and Transform Map are fields that reference records in those tables. The import set and transform map to be used for these data imports must be created in advance.
  • Header Row and Sheet Number are used to specify values when an MS Excel file is the data source. For CSV files, the client just sets these to 1.
  • Active allows the client to deactivate the Wizard Configuration record if it is no longer needed. The Lookup Select Box variable on the record producer displays only active records in this table.

Record Producer Script

The record producer’s script is where the wizard’s “magic” happens. The script does several things:

  1. It validates that the wizard is submitted with an attachment of the correct format.
  2. It queries the Wizard Configuration table for the record that is selected in the Import Type variable, and inserts a new record into the Data Source table using values from the Wizard Configuration record.
  3. It imports the data into the import set table and executes the specified transform map, using ServiceNow helper functions GlideImportSetLoader and GlideImportSetTransformerWorker
  4. It provides feedback to the user upon successful execution of the script, or displays appropriate error messages if the script encountered errors. Error conditions cause the record producer to abort without creating the Data Source record.

Here is the code we used in the record producer script:

// Verify attachment is included and in correct format
var gr2 = new GlideRecord("sys_attachment");
gr2.addQuery("table_sys_id", current.sys_id);
var oC = gr2.addQuery("table_name", "sys_data_source");
oC.addOrCondition("table_name", "sc_cart_item");
gr2.query();
if (!gr2.next()) {
gs.addErrorMessage("You must attach a file to submit. Your import submission has been aborted.");
current.setAbortAction(true);
producer.redirect="com.glideapp.servicecatalog_cat_item_view.do?v=1&sysparm_id=<SysID of the Record Producer>";
}
else{
//Get the glide record for the selected import type
var gr = new GlideRecord('u_pmy_imp_wiz_cfg');
gr.addQuery('sys_id',producer.import_type);
gr.query();
if(gr.next()){
if(gr2.getRowCount() > 1){
gs.addErrorMessage("You may only attach one file at a time for this import wizard. Your import submission has been aborted.");
current.setAbortAction(true);
producer.redirect="com.glideapp.servicecatalog_cat_item_view.do?v=1&sysparm_id=<SysID of the Record Producer>";
}
//check to make sure the file format is correct on the attachment
var passedFormatCheck = false;
var errorCaught = true;
if (gr.u_format == 'CSV'){
if (gr2.file_name.endsWith('.csv') == true){
passedFormatCheck = true;
}
else{
gs.addErrorMessage("This import type is expecting submission of a CSV file (.csv), but a different file format was attached. Your import submission has been aborted.");
current.setAbortAction(true);
producer.redirect="com.glideapp.servicecatalog_cat_item_view.do?v=1&sysparm_id=<SysID of the Record Producer>";
}
}
else if (gr.u_format == 'Excel'){
if(gr2.file_name.endsWith('.xls') == true){
passedFormatCheck = true;
}
else{
gs.addErrorMessage("This import type is expecting submission of an Excel file (.xls), but a different file format was attached. Your import submission has been aborted.");
current.setAbortAction(true);
producer.redirect="com.glideapp.servicecatalog_cat_item_view.do?v=1&sysparm_id=<SysID of the Record Producer>";
}
}

if(passedFormatCheck == true){
// Create data source record (based on form import type selection record)

current.name = gr.u_ds_naming + '_' + gs.nowDateTime();
current.format = gr.u_format;
current.import_set_table_name = gr.u_import_set.name;
current.header_row = gr.u_header_row;
current.sheet_number = gr.u_sheet_number;
current.file_retrieval_method = "Attachment";
current.type = "File";

//Data source needs to be created before we can trigger the commands below, so we create the record outside of the normal record producer method
current.insert();

// Process file into data source record
var loader = new GlideImportSetLoader();
var importSetRec = loader.getImportSetGr(current);

// Import data from data source to import set table (based on form import type selection record)
var ranload = loader.loadImportSetTable(importSetRec, current);
importSetRec.state = "loaded";
importSetRec.update();

// Start appropriate transform map (will have the logic for logging exceptions within the transform map scripts, and will trigger an email once complete to the import submitter with an outline of the logged errors and warnings)
var transformMapID = gr.u_transform;
var transformWorker = new GlideImportSetTransformerWorker(importSetRec.sys_id, transformMapID);
transformWorker.setBackground(true);
transformWorker.start();

//Inform the user that a email outlining the status of the import will be sent once the import is complete
gs.addInfoMessage("Your import file has been submitted. An email will be sent to you once the import is completed to outline any errors or warnings encountered while importing.");
producer.redirect="home.do";
}

}
else{
gs.addErrorMessage('Something went wrong with the import. Please contact a system admin to investigate.');
}

// Since we inserted the data source already, abort additional insert by record producer
current.setAbortAction(true);
}

The Miracle of Transform Map Scripts

If the wizard works magic on the front end, then Transform Maps do the same on the back end. Even when the client’s ITAM team have an opportunity to review the vendors’ data before importing it, there can still be errors. We identified all of the potential failure points in each vendor’s data.

We used onBefore transform scripts to check each source row for exceptions before the source fields are mapped into the target table. We grouped these exceptions into two categories; Errors and Warnings. The transform scripts use log.error() and log.warn() to write exceptions to the import set log for each import run. Error exceptions cause the source row to be skipped by setting the ignore variable to true. Warning exceptions are logged, but the source row is transformed.

We also determined whether other tables required records to be updated or inserted as a result of the data import. For example, lease contract imports contain information about the hardware assets that are under lease. As each hardware record is updated, a related record has to be created in the Assets Covered table, in order to associate that asset with its lease contract.

We used onAfter transform scripts to handle these secondary table updates. OnAfter scripts run after the source record has been transformed into the target table. These scripts also logged exceptions if any were encountered during the update.

After all of the source rows have been evaluated and/or transformed, an onComplete script compiles the exceptions from the import set log in a block of text, then queues a system event. The user who initiated the import receives a notification containing that block of text. The notification provides feedback in near-real time, and lists exceptions from the import set log that would normally only be available to administrators.

The Universal Translator

The final cog in this machine is the Vendor Model Translation table. We created this custom table because the client found that their vendors used model identifiers that did not match the names in the client’s Model (cmdb_model) table. The lack of a common name, model number or some other identifier in the vendor’s data makes it impossible to match asset models.

The vendor translation table is a simple cross-reference table that associates a vendor’s name for a given model with the model record in ServiceNow. It contains just four fields:

  • Vendor is a reference to a vendor record in the Company (core_company) table
  • Active is a true/false value that can be used to filter records during queries
  • Vendor Model is a text field that stores the identifier used by the vendor to reference the model
  • ServiceNow Model is a reference to a record in the Model (cmdb_model) table

With this simple table it is possible to establish aliases for any number of models and/or vendors. It can also be used as a kind of normalization table, listing several vendor models that all refer to the same model in the client’s Model table. In the example below, three mobile phone models provided by the vendor are all associated with a single model in ServiceNow:

We put this table to use in a couple of ways. The transform map for hardware asset imports used an onBefore script to set values in the target Hardware (alm_hardware) table based on a match in the vendor translation table. This script also illustrates some of the exception logging we performed for the client:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
var errorCondition = false;
var itemModel = '';
var modelDisp = '';
var vendorName = 'Name of the vendor company';
//Set the sourceRow variable to allow for input into the log statements the row from the source that failed
var sourceRow = source.sys_import_row + 2;
var excPrefix = 'Exception: Asset Import HW ASN ' + vendorName + ': Source Data Row ' + sourceRow + ': ';
// Do not transform the source record unless the source u_inventory_category field contains the text string "computer.system". This filters out non-computer hardware that may be included in the ASN. Log an Error if the inventory category value does not contain ".Computer Systems.".
if (source.u_inventory_category.indexOf('.COMPUTER SYSTEMS.') == -1){
//log.error(excPrefix + 'Item not a hardware asset (inventory category field does not contain ".computer system.")');
errorCondition = true;
}
else{
// Check for empty vendor model number in source, Log ERROR with message per error exception
if(JSUtil.nil(source.u_mfg_part_num) == true){
log.error(excPrefix + 'Manufacturer part number (mfg part num) missing in source data');
errorCondition = true;
}
else{
// Perform lookup against custom translation table, using vendor and vendor model key as the unique identifiers
//query the table for model translations for the selected vendor
var gr2 = new GlideRecord('u_vendor_translation');
gr2.addQuery('u_vendor', 'SysID of the vendor's record in the core_company table');
gr2.addQuery('
u_vendor_model', source.u_mfg_part_num);
gr2.addActiveQuery();
gr2.query();
// Confirm if match found, if not, raise ERROR exception into import log
if(gr2.next()){
//Set the MODEL FIELD on the record so we don'
t need to query the table again in a field map script
source.model = gr2.u_sn_model.sys_id;
target.model = gr2.u_sn_model;
//set the itemModel field for use in another query later
itemModel = gr2.u_sn_model;
modelDisp = gr2.u_sn_model.getDisplayValue();
target.model_category = gr2.u_sn_model.cmdb_model_category;
}
else{
log.error(excPrefix + 'Source model ' + source.u_mfg_part_num + ' does not match a model in Service-Now. Check vendor translation table to ensure a translation is set up.');
errorCondition = true;
}
}

// Check for empty serial number, if empty, Log ERROR with message per error exception logging section below
if (JSUtil.nil(source.u_serial_num) == true){
log.error(excPrefix + 'Serial number missing in source data');
errorCondition = true;
}
else{
// Confirm that the serial number does not exist in the alm_asset table already as that same model, if so, raise ERROR exception
var modelDesc = '';
var gr1 = new GlideRecord('alm_asset');
gr1.addQuery('serial_number', source.u_serial_num);
//If we found a model through the translation table record, perform an additional filter on the model to make sure we don't have a duplicate model + serial number combo
if (itemModel != ''){
gr1.addQuery('model', itemModel);
//set the modelDesc variable to include that optionally in an error code if applicable
modelDesc = 'with model number ' + modelDisp + ' ';
}
gr1.query();
if(gr1.next()){
log.error(excPrefix + 'Serial number ' + source.u_serial_num + ' ' + modelDesc + 'matches existing hardware asset record ' + gr1.getDisplayValue());
errorCondition = true;
}
}

if(JSUtil.nil(source.u_customer_po_num) == true){
log.error(excPrefix + 'Customer PO Num field is empty on this record.');
errorCondition = true;
}
else{
// Perform lookup on the PO Line item table based on the PO provided and the model of hardware *Assume no more than one line item per model
var enc = 'purchase_order.u_po_number='+ source.u_customer_po_num +'^purchase_order.status!=canceled^model=' + itemModel;
var pol = new GlideRecord('proc_po_item');
pol.addEncodedQuery(enc);
pol.orderByDesc('sys_created_on');
pol.query();
if(pol.next()){
//Set the source record's Purchase Line to the sys_id of the Purchase Order Line Item
source.purchase_line = pol.sys_id;
}
else{
log.error(excPrefix + 'Unable to find a PO Line item under PO number ' + source.u_customer_po_num + ' for product model ' + modelDisp + '. This could be an issue with the vendor translation table record, or the wrong model was selected on a line item.');
errorCondition = true;
}
}

// If the Vendor is not found in ServiceNow's core_company table, log a Warning
var gr3 = new GlideRecord('core_company');
gr3.addQuery('sys_id', 'bbb81b896f8641009e4decd0be3ee4b1'); //sys_id of the vendor from the core_company table
gr3.query();
if(!gr3.next()){
log.warn(excPrefix + 'Vendor ABCDEFG (bbb81b896f8641009e4decd0be3ee4b1) does not match a record in the ServiceNow.');
}

// Check if the source record's quantity is greater than 1, if so, Log ERROR with message per error exception logging section below
if(source.u_qty_shipped > 1){
log.error(excPrefix + 'QTY shipped is greater than 1.');
errorCondition = true;
}
}

//Skip importing the source record if the transfer map is attempting to update an existing record
if (action == "update" && errorCondition != true){
log.error(excPrefix + 'Record is attempting to update an existing record.');
ignore = true;
}
// Skip importing the source record if any exceptions are found (ignore = true )
if(errorCondition == true){
ignore = true;
}

})(source, map, log, target);

For mobile device imports we wrote an onStart transform script that uses the Vendor Model Translation table to update source rows before any source rows are transformed. That reduces the ITAM team’s administrative task load by leveraging information already in ServiceNow’s Model table.

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
/***
* Before transforming any source rows we'll try to match them to models using the
* Vendor Model Translation table or the ServiceNow Model table
***/

log.info('import set ' + import_set.sys_id.toString());
//Query the import set table for rows in the import set we're transforming
var row = new GlideRecord('u_mobile_device_import');
row.addQuery('sys_import_set', import_set.sys_id.toString());
row.query();
while(row.next()){
if(!row.u_device_model_.nil()){
//Check the Vendor Model Translation table for a record that matches the source's Device Model
var gr2 = new GlideRecord('u_vendor_translation');
gr2.addQuery('u_vendor_model', row.u_device_model_);
gr2.addActiveQuery();
gr2.query();
if(gr2.next()){
//Set the MODEL FIELD on the row so that we can coalesce
row.u_device_model_ = gr2.u_sn_model.sys_id.toString();
row.update();
}
else{
//No match in the Vendor Translation table, so check ServiceNow's Model table for a match
var gr3 = new GlideRecord('cmdb_model');
gr3.addQuery('display_name', row.u_device_model_);
gr3.addQuery('status','In Production');
gr3.query();
if(gr3.next()){
//Set the MODEL FIELD on the row so that we can coalesce
row.u_device_model_ = gr3.sys_id.toString();
row.update();
}
}
}
}
})(source, map, log, target);

Putting It All Together

The Import Wizard gave the client a simple way to initiate data imports from any number of vendors at a convenient time, after they verified the quality of the data. The Wizard Configuration table provided a means to extend the wizard’s functionality for multiple vendors and data imports. And the Vendor Model Translation table allowed the ITAM team to associate a vendor’s model information with Model records in the client’s ServiceNow instance.

The client’s ITAM team reports that the wizard has already paid off.  In a few short days it has simplified their work, reduced errors, and made processing asset data much more efficient. And project managers are making plans to bring more vendors on board as they work toward enforcing common data formats across all of their vendors.

Standing on the Shoulders of Giants

This article wouldn’t be complete without acknowledging the assistance of others. We based our design for the Import Wizard record producer on this ServiceNow Community post by Michael Ritchie and endorsed by everyone’s favorite bow-tie wearing technical genius, Chuck Tomasi. The post is well worth reading, since Michael leads you through the steps you’ll need to follow before using the record producer to perform a data import.

By | 2018-07-09T16:05:09+00:00 March 1st, 2017|Categories: Imports|Tags: , |14 Comments

About the Author:

Jacob is the co-founder of Crossfuze, a ServiceNow implementation product and services company. He is one of the foremost ServiceNow integration experts in the world and designed/wrote many of the plugins found in the ServiceNow product today. Prior to co-founding Crossfuze he worked at ServiceNow during its startup years to write the SSO solutions and 3rd-party integrations.

14 Comments

  1. Per Vildstrand March 2, 2017 at 1:09 am - Reply

    Great article, I must try this. So far, we have relied on the mail import feature, but for sure this is more reliable and intuitive.

    Thanks Jim!

    //Per Vildstrand

  2. Brent Sutton March 7, 2017 at 1:42 pm - Reply

    What a fantastic article! I have a similar requirement to collate any import set errors into a notification for the person doing the load. Are you able to share how you achieved this for the example in this article? Very much enjoy your work and the new site revamp.

    • Jim Pisello March 7, 2017 at 6:57 pm - Reply

      Hi Brent,
      Glad you enjoyed the article. We used a Notification Mail Script to compile all of the entries that we wrote to the logs. We used an onComplete transform script to queue a system event which sends the notification to the person who initiated the import. I’ve posted code for both of those below.

      Notification Email Script

      (function runMailScript(current, template, email, email_action, event) {
         
          // Set email subject to: "Asset import wizard <import_set_number> has completed"
          email.setSubject('Asset import wizard ' + current.number +  ' has completed');
          // Set into the email body a greeting to the user outlining that the import is completed
         
          template.print('Hello, <br/>Your asset import wizard run ' + current.number + ' has completed.<br/>The import log from the import is as follows:<br/>');
         
          // Perform a lookup against the import log to get all errors and warnings raised via the import run
         
          // Get the most recent import set run
          var gr1 = new GlideRecord('sys_import_set_run');
          gr1.addQuery('set', current.sys_id);
          gr1.orderByDesc('sys_created_on');
          gr1.query();
          if(gr1.next()){
             
              //Get the import logs for this run
              var gr2 = new GlideRecord('import_log');       
              gr2.addQuery('run_history', gr1.sys_id);
              gr2.orderBy('message');
              gr2.query();
              template.print('<table><th>Exception Level</th><th>Message</th>');
              //Loop for each error or log message:
              while (gr2.next()){
                  // Set into the email body the level of the log (Error or Warning) and show the log message (reason for the error)
                  template.print('<tr><td>' + gr2.level.getChoiceValue() + '</td><td>' + gr2.message + '</td></tr>');
                  //End loop
              }
              template.print('</table>');
          }
      })(current, template, email, email_action, event);

      onComplete Transform Script

      // Send the Import Wizard Complete email notification to the person who initiated the import
      var grUser = new GlideRecord('sys_user');
      grUser.addQuery('user_name', import_set.sys_created_by); //the import set record is created by the user who initiates the import
      grUser.query();
      if (grUser.next()){
          // Trigger the email
          gs.eventQueue('import_wizard.complete', import_set, grUser.sys_id);
      }

      You’ll also need to create an email notification that contains the notification email script, along with any other information you’d like to include.

      • Brent Sutton March 14, 2017 at 4:00 pm - Reply

        Thanks Jim, your code worked perfectly! My CMDB team are ecstatic with the ease of the new load. It makes their lives a lot easier with the error checking we’ve incorporated and the instant feedback contained in the email. Thanks again for all your efforts that go into making SN|Guru such a valuable resource.

        • Jim Pisello March 14, 2017 at 5:44 pm - Reply

          That’s fantastic Brent, thanks for letting us know!

  3. Vinoth Kumar May 30, 2017 at 8:18 am - Reply

    Thanks for sharing with us. Excellent idea and really helpful.

  4. Arun October 3, 2017 at 3:43 am - Reply

    Could you please share a update set…

    • Jim Pisello October 3, 2017 at 6:35 am - Reply

      Hi Arun,

      We elected not to provide an update set for this. The use case for our client was very specific, and we believe that each user of this solution will have requirements that are distinct enough to make a generic update set impractical. You can copy the scripts from the article and paste them into the various records in your instance, then make modifications as needed.

  5. Sean Kerwick October 26, 2017 at 9:12 am - Reply

    I’m stuck on one particular part of this: The proper reference to the import set tables within the Import Wizard Configuration. Would you please explain how you set up that field?

    • Sean Kerwick October 26, 2017 at 12:48 pm - Reply

      I think I got it. I referenced Table (sys_db_object) with a reference qualifier on the Extends Table to Import Set Row.

    • Jim Pisello October 26, 2017 at 1:07 pm - Reply

      Hi Sean, The solution we documented here assumes that you have previously created both the import set and the transform map, ideally by manually creating a data source record with an attached file. This solution is intended to address follow-on data imports. It’s not designed to initially create an import set or transform map.

  6. Mehmood February 1, 2018 at 4:54 pm - Reply

    Hello Jim,

    You have wrongly mentioned above that link “https://community.servicenow.com/community/develop/blog/2016/09/21/consumerize-data-imports-to-non-servicenow-administators” is created by Chuck.

    It is posted by Michael Ritchie

    Thanks,
    Mehmood

    • Jim Pisello February 2, 2018 at 9:09 am - Reply

      Oops! Thanks for catching that Mehmood! I’ve updated the post to give credit where it’s due!

Leave A Comment