This post is the first in a series about Import/Export topics. The goal is to get you up to speed on Import Set concepts and to walk you through the process of setting up an Import Set from scratch. While this initial post is focused on building an import set, we’ll dig into the theory and what happens behind the scenes in future posts.
If at any time you get lost on a step, skip ahead and watch the animated GIF summary for that section! I don’t want to bore you with a wall of text, so I’ve kept the instructions as brief as possible and each section will include an animation that pulls everything together.
1. What Exactly is an Import Set?
In short, it’s a staging table combined with some transformation logic that allows you to import data into your ServiceNow instance. This happens in two stages:
- Load the data into a temporary/staging table
- Transform the data and push it into the desired target table
Let’s jump right into building one.
2. Let’s Load some Data!
We’re going to use a publicly available dataset from Data.gov throughout this tutorial, meaning you can follow along and work with the same data. We’ll use a CSV file containing Chicago Park District Buildings. You can obtain the file here - download the Comma Separated Values version of the data.
There is more than one way to get started, but we’re going to start with the Load Data option available by navigating to System Import Sets in the navigator.
Click Load Data and fill out the fields on the resulting form with the following values:
- Import set table: Create table
- Label: Import Chicago Park District Buildings
- Name: Automatically populated
- Source of the import: File
- File: Click Choose File and select the CSV file we downloaded earlier
Click Submit, and wait for the initial data load to complete. Behind the scenes, this is creating a Data Source and Import Set.
Here’s a quick animation that walks through everything we’ve done so far.
3. Create a Transform Map
Even though the data is now in the system, it’s not actually in a useful place yet. Import Set Tables are periodically purged, so we must Transform the data and push it to the table we actually want it to live in. After we loaded the building file, we were given a series of links under the Next steps… section of the screen. Click Create transform map and fill out the form with the following values:
- Name: Transform Park District Buildings
- Traget Table: Location [cmn_location]
Right-click the form header and click Save.
3.1. Create Field Maps
A field map associates a column in the source data with a column in the target table. When a transform runs, the data in the source field is copied to the target field. If we don’t add any field maps or transform scripts to our Transform Map, the map won’t do anything!
There is more than one way to create a field map, but we’ll use the Mapping Assist feature to quickly add the first one.
- Click Mapping Assist from the Related Links section of the Transform Map form
- Drag BLDG_NAME from the list of source fields on the left into the Field Map box in the middle of the screen
- Drag Name from the list of target fields on the right into the Field Map box in the middle of the screen
Your Mapping Assist screen should now look like this:
When finished, click Save. This generates our first field map and you should now see it listed in the Field Maps Related List on the Transform Map form:
We can also create field maps one at a time (not using Mapping Assist). To do this, click New on the Field Maps related list and fill out the form with the following values:
- Source field: ADDRESS
- Target field: Street
Click Submit you should now see two Field Maps in the related list:
3.2. Choose a Coalesce field
A Coalesce field allows us to tell the Import Set engine how to reconcile data. To demonstrate why this is important, what happens if we load the Park District Buildings more than once? The first time, things will probably work just fine, but what about the second time? And the third? If we don’t specify a Coalesce field, the data will be duplicated each time the import runs.
A Coalesce field tells the import engine: “If nothing exists with the value in this field, create a new record. If something does exist, update that record instead”.
For our Park District Building import, we’ll coalesce the BLDG_NAME -> Name mapping. To do this, double click the false value in the Coalesce column of the u_bldg_name row, and change the value to true.
Here’s an animation that shows all of the steps we’ve covered since the last animation.
4. Let the Transformation Begin!
Now that we have a Data Source, Transform Map, Field Maps and have chosen a Coalesce field, we can transform the data. Click Transform from the Related Links section of the Transform Map form. Make sure the Transform Park District Buildings map is in the Selected maps list and then click Transform.
If everything worked, you should see a screen that indicates success!
Go look at the records in the Location table, and you should see the newly created Park District Building records.
This just scratches the surface of Import Sets, so look for more posts in the near future! Potential topics include:
- Just what exactly is happening behind the scenes when you use the Load Data module?
- Various ways to load data into your import sets
- How to understand the Import Log and Transform History
- Writing Transform Scripts and Scripted Field Mappings
- Dealing with complex coalesce values
- And more!
If you run into issues with any of these steps, comment on this post and I’ll take a look. Happy importing!