Automated Bulk Data Import from Excel Using Service Catalog

tharun24
Tera Contributor

5 hours ago - last edited 5 hours ago

Hi everyone,

I would like to share a practical use case that we recently implemented in ServiceNow. This solution helps users create or update Asset records in bulk by simply submitting a Service Catalog Item with an Excel attachment.

This approach is especially useful when asset teams receive bulk data from external vendors or internal teams and want to avoid manual record creation.


Use Case Overview

The objective is to allow end users to upload an Excel file through a Catalog Item, and automatically:

  • Create new Asset records
  • Update existing Asset records (if already present)
  • Track the results clearly for the requester
  • Provide clickable links to created/updated records
  • Send a summary email with the outcome

All of this is achieved using Flow Designer, Data Sources, Import Sets, and Transform Maps.

Solution Approach

The overall approach consists of the following steps:

  1. Create a Service Catalog Item with an Excel upload option
  2. Configure a Data Source and Import Set
  3. Build a Transform Map with required field mappings and scripts
  4. Create a Flow that triggers on Service Catalog submission
  5. Load and transform the Excel data programmatically
  6. Capture results (Inserted, Updated, Skipped, Ignored records)
  7. Update the RITM with a summary and clickable record links
  8. Send an email notification to the requester

Implementation Steps

  1. Create the Catalog Item

Create a new Catalog Item with the following setup:

  • A reference or link to download the Excel template
  • One mandatory variable:
    • Attachment variable (Example label: “Upload Asset Excel File”)

tharun24_0-1766389492722.png
The requester downloads the template, fills in asset details, uploads the file, and submits the request.

  1.  Create a Data Source
  • Navigate to System Import Sets → Administration → Data Sources
  • Create a new Data Source
  • Select type as File
  • Use the same Excel template structure

    tharun24_1-1766389492725.png

     

This Data Source will be used later to load the attachment into an Import Set table.

  1. Configure Import Set and Transform Map

Import Set Table

  • Create a new Import Set table using the Excel template
  • (In my case, I selected an existing Import Set table as it was already created)

tharun24_2-1766389492729.png

 

Transform Map

  • Create a Transform Map for the Import Set table
  • Configure required Field Mappings
  • Add Transform Scripts to:
    • Create Asset records if they do not exist
    • Update Asset records if they already exist
    • Handle validation and error scenarios

This logic ensures clean and controlled data processing.

tharun24_3-1766389492734.png

 

  1. Create the Flow in Flow Designer

Create a new Flow with the trigger:

Service Catalog → Requested Item Submitted

Flow Steps:

tharun24_4-1766389492738.png

 

  1. Look Up Attachment Record
  • Fetch the Excel file attached to the Requested Item (RITM)
  1. Copy Attachment
  • Create a copy of the attachment to safely process the file without impacting the original upload
  1. Load Data into Import Set (Custom Action)
  • Trigger a custom Flow Action that:
    • Uses the Data Source
    • Loads Excel data into the Import Set table
    • Associates Import Set rows with the RITM for traceability
    • Runs the Transform Map
    • Returns the Import Set Sys ID

Custom Action: Run Transform Map & Capture Results

Step 1 – Inputs

  • Data Source Sys ID

    tharun24_5-1766389492742.png

Step 2 – Script Logic

  • Load the attachment into Import Set
  • Execute the Transform Map
  • Capture record counts based on transform state:
    • Inserted Records
    • Updated Records
    • Skipped Records
    • Ignored Records
      tharun24_6-1766389492746.png

       

(function execute(inputs, outputs) {
// ... code ...

  processDataSource(inputs.dataSource);

function processDataSource(dataSource) {
  //var dsUtil = new sn_impex.GlideDataSourceUtility();
  //dsUtil.refreshDataStreamSchema(inputs.dataSourceSysid);
//gs.warn(dataSource);
var dataSource1 = new GlideRecord("sys_data_source");
if(dataSource1.get("import_set_table_name", inputs.dataSource)){

var loader = new GlideImportSetLoader();
var importSetGr = loader.getImportSetGr(dataSource1);
loader.loadImportSetTable(importSetGr, dataSource1);

doTransform(importSetGr);
}
}

function doTransform(set){

var importSetRun = new GlideImportSetRun(set.getUniqueValue());
var importLog = new GlideImportLog(importSetRun, set.data_source.name);
var ist = new GlideImportSetTransformer();

ist.setLogger(importLog);
ist.setImportSetRun(importSetRun);
ist.transformAllMaps(set);
//outputs.ImportSetSysId="WELCOME";
outputs.importsetsysid=set.getUniqueValue();
  
  var isetRowGr = new GlideAggregate('sys_import_set_row');
  isetRowGr.addQuery('sys_import_set.sys_id',set.sys_id);
  isetRowGr.groupBy('sys_import_state');
  isetRowGr.addAggregate('COUNT','sys_import_state');
  isetRowGr.query();
  while(isetRowGr.next()){
    //outputs.total += parseInt(isetRowGr.getAggregate('count','sys_import_state'));
    if(isetRowGr.getValue('sys_import_state') == 'inserted')
      outputs.insert = isetRowGr.getAggregate('count','sys_import_state');
    if(isetRowGr.getValue('sys_import_state') == 'updated')
      outputs.update = isetRowGr.getAggregate('count','sys_import_state');
    if(isetRowGr.getValue('sys_import_state') == 'ignored')
      outputs.ignored = isetRowGr.getAggregate('count','sys_import_state');
    if(isetRowGr.getValue('sys_import_state') == 'skipped')
      outputs.skipped = isetRowGr.getAggregate('count','sys_import_state');
  }
  outputs.total = parseInt(outputs.insert + outputs.update + outputs.ignored + outputs.skipped);
}
})(inputs, outputs);

​

 

Step 3 – Outputs

The action returns:

  • Insert Records count
  • Update Records count
  • Ignored Records count
  • Skipped Records count
  • Import Set Sys ID

tharun24_8-1766389492753.png

 

  1. Update Requested Item (RITM)

Using the outputs from the custom action:

  • Update the RITM comments or work notes
  • Display results in a table format
  • Include clickable links to newly created or updated Asset records

This gives the requester a clear and structured summary directly on the request.

Sending Email Notification to the Requester

Fetch Import Set Rows

  • Look up Import Set Row records using:
    • Import Set Sys ID
    • Transform state (Inserted, Updated, Ignored, Skipped)

      tharun24_9-1766389492759.png
    • Repeat this step for each state.

       

Generate Record Links (Custom Action)

Create another custom Flow Action that:

  • Import Set Row records as input

    tharun24_0-1766390333686.png

     



  • Generates clickable record URLs

(function execute(inputs, outputs) {

  var instance_url = gs.getProperty('glide.servlet.uri');
  var link = "";
  var insert_link = "";
  var update_link = "";
  var ignore_link = "";
  var skipped_link = "";
  
  if(inputs.Count != 0){
    link = "<a href="+ instance_url + "sys_import_set_row_list.do?sysparm_query=sys_import_set=" + inputs.SysId + " target=\"_blank\">"+ inputs.Count +"</a>" ;
  }else{
    link = inputs.Count;
  }
  
  if(inputs.insertCount != 0){
    insert_link = "<a href="+ instance_url + "sys_import_set_row_list.do?sysparm_query=sys_import_set=" + inputs.SysId + "^sys_import_state=inserted  target=\"_blank\">"+ inputs.insertCount +"</a>";
  }else{
    insert_link = inputs.insertCount;
  } 
  if(inputs.updateCount != 0){
    update_link = "<a href="+ instance_url + "sys_import_set_row_list.do?sysparm_query=sys_import_set=" + inputs.SysId + "^sys_import_state=updated   target=\"_blank\">"+ inputs.updateCount +"</a>";
  }else{
    update_link = inputs.updateCount;
  }
  if(inputs.ignoreCount != 0){
    ignore_link = "<a href="+ instance_url + "sys_import_set_row_list.do?sysparm_query=sys_import_set=" + inputs.SysId + "^sys_import_state=ignored   target=\"_blank\">"+ inputs.ignoreCount +"</a>";
  }else{
    ignore_link = inputs.ignoreCount;
  }
  if(inputs.skippedCount != 0){
  skipped_link = "<a href="+ instance_url + "sys_import_set_row_list.do?sysparm_query=sys_import_set=" + inputs.SysId + "^sys_import_state=skipped   target=\"_blank\">"+ inputs.skippedCount +"</a>";
  }else{
    skipped_link = inputs.skippedCount;
  }
  outputs.url = link;
  outputs.insert_url = insert_link;
  outputs.update_url = update_link;
  outputs.ignore_url = ignore_link;
  outputs.skipped_url = skipped_link;
})(inputs, outputs);
​


tharun24_1-1766390450060.png

  • Returns formatted links

Send Email

  • Use the Send Email action in Flow Designer
  • Populate the email body with:
    • Summary counts
    • Direct navigation links to records

tharun24_2-1766390487788.png


Final Output

Requested Item (RITM)

  • Automatically updated with:
    • Result summary in table format
    • Clickable links to Asset records

tharun24_3-1766390534648.png


Email to Requester

  • Clear overview of processing results
  • Direct links to created/updated Assets
  • Easy navigation without manual searching
    tharun24_4-1766390562652.png

     

Conclusion

This solution provides a user-friendly and scalable approach for bulk Asset creation and updates in ServiceNow. By combining Catalog Items, Flow Designer, Import Sets, and Transform Maps, we can significantly reduce manual effort and improve data accuracy.

I hope this use case helps others who are working on similar requirements.
Happy to hear your thoughts or answer any questions! 😊

  • 66 Views
0 REPLIES 0