How to Load Data Into an Import Set
With Chris Sanford
In this article, we are going to show you some tips for loading external data into a ServiceNow import set. Like a lot of things in ServiceNow, there're actually several ways to do it, each with their own advantages and disadvantages. Today we are going to start with the simpler methods and then touch on some of the more complicated ones.
Use Case
One of the most common and basic business cases of importing external data is an Excel spreadsheet. Usually, a department or individual that may or may not be familiar with the ServiceNow platform, will have some data they want loaded into a data table. The Excel sheet's data may or may not have the same format or column names as what is actually in ServiceNow, and that is where the power of the ServiceNow import set and transform can help you meet the requirement.
In this example, we have a small spreadsheet with a few users which you want loaded into the ServiceNow user table. As an administrator, you can easily upload an excel spreadsheet by navigating to the system and port set's load data on the left navigator. You'll have the option to create a new import set table, or use an existing one to store the raw data. In this example you’ll choose an existing one; you’ll leave the rest of the fields populated with the default values and upload your spreadsheet. You can see that the import uploaded four records which, based on the data in the spreadsheet, is the expected result. From here, you’ll click “Loaded data” to see the raw data in the import set table. Clicking into each row shows the details, which match what we expected to see in the spreadsheet.
Creating a Transform Map
Before you proceed to create a transform map, you’ll take a look at the user table and see which of the users, if any, from your spreadsheet already exists. Doing a filter by email tells you that one out of these four users already has an account with the same email address. Now going back to the import set table, this contains the raw data, but in order for this data to be useful in the platform you'll need to create a transform map. There are several ways to do this, but for this example, you'll click the link that says transform maps, then click new. You'll give your transform map a name and set the target table to be sys_user, then you’ll save and click “Auto Map Matching Fields”. This creates field maps for all the columns in the spreadsheet which correspond to a column in the target table with a similar name.
Now, it's going to be important to set a coalesce field, which serves as a primary key to determine if a given import set row should insert a new record in the target table or update an existing one. We will make email the coalesce field for this example, then we’ll click transform and then we’ll click transform again. Now looking at the transform history you can see that there were three inserts into the user table and one update. This is the expected result. If you ran another transform with a spreadsheet that contained the same four email addresses in it, the existing users would be updated in the platform or skipped over entirely instead of creating duplicate entries, since you have a coalescence on the email field. The import set table load is closely tied to what are called data sources in ServiceNow. You can see this by navigating to System Import Sets > Data Sources. You'll notice that there is a data source with the name matching the name of the spreadsheet that was just uploaded.
What the load data module is actually doing behind the scenes is creating a data source with a type of file in a format of Excel. You can also see a link to your transform map that we created under the transforms related list. There's also related links for “Test Load 20 Records” and “Load All Records”. Clicking load all records will repeat the process you did earlier of loading the data into the import set table. You'll also notice on the data source form, there are quite a few other file formats and also quite a few other types of data sources.
Common Integration Requirements
A common integration requirement for ServiceNow customers is the need to automatically load data from another web service's REST API which will typically provide what's called a payload in JSON format. The payload is actually providing some vehicle manufacturer data in this example, which is publicly available through this URL. This file can easily be imported through a data source.
If you have the file stored locally on the machine, all you need to do to import it into ServiceNow is create a new data source — set the type as file and set the file format to JSON. You’ll give your new data source a name, and then leave the type as file and set the format to JSON., You'll see that there's a mandatory field Path for each row as well as instructions for how to populate this field above it. In this example, each of the objects in the results array should correspond to a row. For the instructions, enter /Results/Results, then you’ll upload the file and click load all records.
Now looking at the loaded data, you can see that this successfully loaded each of the results with its data into the import set table. This example is simple enough if you have a file that you just need to manually attach once, but oftentimes a data load from a REST API needs to be automatically loaded on a scheduled basis. This can easily be facilitated through Integration Hub. To do this you’ll need to create another data source of type: REST (IntegrationHub). You would set the format to JSON and enter the same values below it, as shown in the previous example. For the request action you’ll need to create a new data source Integration Hub action through the Flow Designer interface. In the REST step, you’ll populate the necessary information for your REST API endpoints; you can do this by copying the base URL as well as the resource path from your endpoint in Postman.
In a real world example, you'll often be able to find this information online by checking the publisher's REST API documentation. You would then need to publish your action, go back to the data source, and save it. When you load the records you should see that you have the same data that was in the previous example with these 93 rows and the data on the vehicle manufacturers that's pulled from the payload. Since this data source dynamically gets the information from the REST endpoint, you can schedule this to be done automatically on a scheduled basis by navigating to System Import Sets > Scheduled Imports. From here, it would be as simple as selecting your data source and providing the schedule details. Now it's important to note that that example requires a ServiceNow subscription to Integration Hub; for customers that don't have this, they can achieve similar results with a little bit of code using a data source type called custom load by script.
Custom Load by Script
Here, with a little bit of JavaScript code, you have this reusable script that can import data from a REST endpoint by providing similar information as in the previous examples. Now, before creating this data source you would need to create what's called a REST message. You can do that by navigating to the system web services REST message and providing similar information as you did in the previous Integration Hub example with the full endpoint URL with the resource path. We also want to take note of the REST message name as well as this HTTP method name. Then when you go back and create your data source, when calling your script, you’ll need to provide the REST message name and the HTTP method name from the previous screen. You’ll provide that same resource path of /Results/Results and then the import set table object, which is passed into the script template automatically. When you load the data, you should see similar expected results in your import set as in previous examples with the 93 rows and the vehicle manufacturer data from the payload in each row.
Did you find this How to Load Data Into an Import Set in ServiceNow article helpful? Are you ready to start your journey with ServiceNow? If you want to find out more information about GlideFast Consulting and our ServiceNow implementation services, you can reach out to us here.
About GlideFast Consulting
GlideFast is a ServiceNow Elite Partner and professional services firm that provides tailored solutions and professional services for ServiceNow implementations, integrations, managed support services, application development, and training. Reach out to our team here.