Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 39 ยท Integrations and Service Portal

Import Sets and Transform Maps in ServiceNow

Load data into ServiceNow with data sources, import set tables, field maps, coalesce and transform scripts, and learn to test, schedule and clean up imports.

Spreadsheets, HR exports and other systems often hold data you need inside ServiceNow: users, locations, assets, configuration items. Typing them by hand is impractical. Import sets load the data in two stages, and transform maps decide how each source column becomes a field in the real table. Understanding both stages prevents most import disasters.

Imports happen on an instance, so the menus here are described, not shown. The runnable blocks are stand-ins that perform the same stages in plain JavaScript.

The two-stage design

flowchart LR
    S["Source: CSV, Excel, JDBC, REST"] --> DS["Data Source"]
    DS --> IS["Import set table (staging)"]
    IS --> TM["Transform map"]
    TM --> T["Target table (for example sys_user)"]

Raw data first lands untouched in a staging table, then a transform map writes it to the real table.

Why stage? You can inspect the loaded rows, rerun the transform, and keep a history, without touching production data until you are ready.

Steps in the UI

  1. Data Source (System Import Sets > Administration > Data Sources): choose a type (File, JDBC, LDAP, REST/OData), upload or configure it, and set format options such as CSV delimiter and header row.
  2. Load Data (System Import Sets > Load Data): pick the data source, give the Import set table a name such as u_import_users, and load. The platform creates the staging table with columns from the header.
  3. Transform Map (System Import Sets > Transform Maps): choose Source table (the staging table) and Target table, then define Field Maps.
  4. Run Transform: from the import set record. Check the Transform History and the Import Set Rows for errors.

Field maps

A field map links a source field to a target field. Options that matter:

  • Auto map matching fields creates maps for same-named columns.
  • Use source script: compute the value with JavaScript, using source (the row) and returning the value.
  • Choice action: when the target is a choice field, create, reject or ignore values that do not exist yet.
  • Referenced value field name: for a reference field, which field of the referenced table to match (for example user_name on sys_user).
JavaScript
// Source script for target field "email"
answer = (function transformEntry(source) {
  return (source.u_first_name + '.' + source.u_last_name + '@example.com').toLowerCase();
})(source);

Coalesce: update, don't duplicate

Without coalesce, every run inserts new rows, so loading the same CSV twice doubles your users. Mark one or more field maps as Coalesce, such as employee_number. The transform then looks for an existing target row with that value: found means update, not found means insert. Pick a field that is unique and stable. Email addresses change; employee IDs rarely do.

Rules of thumb:

  • A coalesce field must be consistent in the source (no blanks, no trailing spaces).
  • Use two coalesce fields only when the key is truly composite.
  • Empty values can overwrite good data. Check Coalesce empty fields and field map settings deliberately.

Transform scripts

A transform map can run scripts at points of the process: onStart, onBefore (per row, before writing), onAfter (per row, after) and onComplete. Inside onBefore, ignore = true skips a row, and target and source are available.

JavaScript
// onBefore: skip rows without an employee number
if (source.u_employee_number.nil()) {
  ignore = true;
  log.warn('Row skipped: no employee number');
}

Common problems

Symptom Likely cause
Duplicates after reloading No coalesce field
References stay empty The referenced record does not exist, or the match field is wrong
Dates are wrong The data source date format does not match the file
Everything is rejected The choice action is reject, or a business rule aborts
Imported but nothing visible Wrong target table, or ACLs hide the rows

Always import a small sample first. Imports also run business rules, notifications and flows, so a big load can send thousands of emails; disable notifications or use a development instance while testing.

Scheduled imports

For recurring feeds, create a Scheduled Data Import with a data source and a transform. Pair it with a final clean up such as deactivating users who disappeared from the feed (done in an onComplete script or a separate flow).

The stages in JavaScript

The first stand-in loads rows into a staging area and then transforms them with coalesce on employee_number, so you can watch insert versus update.

JS Stand-in: stage, transform and coalesce

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Running the transform twice gives updates the second time instead of duplicates. The next stand-in shows what happens without a coalesce key.

JS Stand-in: the duplicate problem without coalesce

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the role of the import set table?

    The staging table holds the raw rows so you can inspect and rerun the transform.
  2. 2. What does marking a field map as Coalesce do?

    Coalesce turns the import into upsert behavior: found means update, otherwise insert.
  3. 3. How do you skip a row in an onBefore transform script?

    Setting ignore to true tells the transform to skip writing that row.
  4. 4. What is a sensible first step before loading a large file?

    Imports fire rules and notifications, so test small and check the results first.

Summary

  • Imports run in two stages: data source into an import set table, then transform map into the target table.
  • Field maps can use source scripts; choice actions and referenced value fields control reference and choice mapping.
  • Coalesce prevents duplicates by updating matching rows.
  • Test with small samples, watch rules and notifications, and schedule recurring feeds with clean-up.

Next: Service Portal, Widgets and Next Experience