Working with External Data · Topic 1 of 2

Importing CSV and Excel Data

Import CSV and Excel data into a ServiceNow app for the CAD exam: data sources, Load Data, import set tables, transform maps, coalesce and scripts.

5 min

In a nutshell

Order: data source, import set table, transform map, target table.
Excel data sources need the sheet number and header row.
Test Load 20 Records loads rows without transforming them.

What it is

Applications often start with data that already lives in a spreadsheet, such as a list of assets, vendors or locations. ServiceNow imports a CSV or Excel file in two steps. The rows are first loaded into a temporary import set table, then a transform map copies them into the application's target table.

The raw file never goes straight into the target table. Staging lets you look at what arrived, fix the mapping and run the transform again without reloading the file.

How it works

The data source

A data source record describes where the rows come from. For a spreadsheet it has the type File and a format such as CSV or Excel. For Excel you also set the sheet number and the header row, so the platform knows which sheet to read and which row holds the column names.

Data sources live under System Import Sets. In a scoped application, create the data source and transform map in the application scope so they are packaged with it.

Load Data and Test Load 20 Records

System Import Sets > Load Data is the quick path for a one-time file. You choose a new or existing import set table, attach the file and submit. Column names in the file become fields on the import set table.

On the data source record, Test Load 20 Records loads the first 20 rows into the import set table without transforming them. Use it to check the columns and values before you build the transform map.

Transform maps and field maps

A transform map links one import set table (the source) to one target table. Its field maps say which source column fills which target field.

Option	What it does
Auto Map Matching Fields	Creates field maps where the column name matches a target field name
Mapping Assist	Lets you drag source columns onto target fields by hand
Run business rules	Decides whether business rules on the target table run during the transform
Choice action	For choice and reference fields: create, ignore or reject an unknown value
Field map script	Calculates the target value with a script instead of a straight copy

Coalesce

Coalesce decides whether a row updates an existing record or inserts a new one. Mark a field map as a coalesce field and the transform looks for a target record with the same value. A match updates that record; no match inserts a new one.

Setup	Result
No coalesce field	Every row inserts a new record, so a rerun creates duplicates
One coalesce field	Matching value updates, otherwise insert
Several coalesce fields	Update only when all coalesce fields match
Create new record on empty coalesce fields selected	A row with blank coalesce values inserts a new record each time

Pick a coalesce field that is unique and stable, such as an asset tag or an employee number.

Transform scripts

Transform scripts run at fixed points in the transform, such as onStart, onBefore (each row, before it is written), onAfter and onComplete. They receive source (the import set row), target (the target record), map, log, action (insert or update) and ignore.

// onBefore transform script: skip rows with no asset tag
if (source.u_asset_tag.nil()) {
  ignore = true;
} else {
  target.name = source.u_name.toString().trim();
}

Setting ignore = true skips that row only. The rest of the import carries on.

Checking the result

After Run Transform, open the import set. Each import set row shows its state, such as inserted, updated, ignored or error, and the target record it touched. The transform history and the import log show warnings and script messages.

Because imports do not use the form, UI policies and client scripts do not run on imported records. Put validation in a transform script, a business rule or a data policy.

How the exam asks about this

"Check the columns of a new spreadsheet without changing any application data." Use Test Load 20 Records.
"Each weekly load must update existing records matched on asset tag and serial number." Coalesce on both fields; a row updates only when both match.
"A UI policy makes a field mandatory, yet imported rows arrive with it empty." UI policies are client side and do not apply to imports; use a transform script or data policy.

What to remember for the exam

Order: data source, import set table, transform map, target table.
Excel data sources need the sheet number and header row.
Test Load 20 Records loads rows without transforming them.
Coalesce updates on a match and inserts otherwise; with several coalesce fields all must match.
With no coalesce field, every row inserts.
In an onBefore script, ignore = true skips the current row.

Watch out

Create new record on empty coalesce fields inserts a record for every row whose coalesce values are blank. A file with many blank keys can fill the table with near duplicates.

Common mistakes

Loading straight into the target table. There is no such option. Rows always land in the import set table first, and only a transform writes to the target.

Coalescing on a field that is not unique. Several target records may share the value, so rows update the wrong record. Use a true key, or several coalesce fields together.

Relying on form logic for imported data. Client scripts and UI policies only run in the browser. Server-side checks are the ones that apply during a transform.

Check yourself

What does Test Load 20 Records do?
It loads the first 20 rows of the data source into the import set table without running a transform, so no target data changes.
Two fields are marked as coalesce. When does a row update an existing record?
Only when both coalesce values match the same target record; otherwise the row inserts a new record.
How do you skip a bad row during a transform?
Set ignore = true in an onBefore transform script; only that row is skipped.

Topic 1 of 2 in Working with External Data

Test yourself on Working with External Data

The full ServiceNow CAD question bank has 600 questions, with timed practice exams and an explanation for every answer.

Get Full Access Try Sample Exam

This study guide is independent and is not affiliated with, endorsed by, or sponsored by ServiceNow. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.