Audience: Intermediate to advanced developers/admins.
Recently I’ve been asked to explore the possibility of a scheduled import (e.g., once per month) of a file, which will be stored on an FTP server. As always, my one stop shop for information was the community. I found some pieces of information how to set up the data source, but I never found a full tutorial on how to setup and test the solution. And this is what you are currently reading.
Scenario
We are all tired of things named with the melodious name ‘Test’. Instead of that, I decided to you use my daily push-ups tracker (xlsx) and import it once a day. It is a very simple table with two columns – one is a date, and the other is the push-ups count for the respective date. Here we go.
Prerequisites
To make the scheduled import run smoothly, we need:
- File with data
- Target table
- Transform map
- FTP server to store our file
- Data source record
- Scheduled import record
For the sake of completeness, I will go through the steps to ensure that we have these in place. If you have experience with creating tables, transform maps and importing data, please proceed to FTP Server.
Where to start from?
There are few options for an entry point. I decided to create a table in ServiceNow first.
The data structure
Second, I created an excel template, which will be actually the file where I’ll be populating the data daily and will be the stored on the FTP server.
Go to the list view, right-click on any header -> Import. Leave the checkbox checked for Do you want to create an Excel template to enter data? then click Create Excel Template.
As we can expect, the excel template has two columns: Count and Date.
Populate some data, save the file, and upload it back to ServiceNow using the same approach used for the template generation.
Once the import is complete, click Preview imported data. Here I should mention that when you are importing data, ServiceNow is populating the data not directly to the target table, but instead, a staging table is created. This is to allow you to do some transformation before the data is inserted into the target table.
Back on our data preview, we see that the correct data has been imported into the staging table.
Once confirmed, we would like to go through some transformation (if needed) and proceed to the actual import to the target table. So, go back to the Import set:
See that the current state is loaded, and it is still in our import set table only:
Open the update set record and click Transform:
This will prompt you to create a transform map, defining how the excel columns map to the target table fields. Select a proper name and the target table and save:
You can either use Auto mapping or go advanced with the Mapping Assist. In our case, the excel column names and the target field names have complete match, so I will use the first option.
Having the transform map created, now we need to run the actual transformation:
If all goes well, you will be presented with a screen, confirming that the transformation is completed successfully.
Going to the Transform History, we see once again that the transformation is complete.
And the data is now stored into the target table.
With all that in place, let’s go to the main topic.
FTP Server
On the Internet, you can find a number of possibilities for public FTP Server, which can be used for testing. My choice was https://byet.host/free-hosting/. I created a free account which gave me also a free FTP Server. Once your account is created, you’ll be presented a screen with some information about it. It is important to remember your ftp host name, your username and password.
Using these, go to your file explorer and upload the source file that will be used as a data source.
Defining the FTP Data source in ServiceNow
Defining a data source in ServiceNow is pretty straightforward. Go to System import sets -> Administration -> Data sources and click New.
Complete all the fields, choosing the right file type and format, retrieval method, as well as the ftp server, file path and credentials. Click Submit.
Hold on, I know it is too long already, but we are almost there. Few more steps left.
Defining the Import schedule
As with the data source, defining the import schedule is also an easy thing to do: Go to System import sets -> Administration -> Scheduled Imports and click New.
The form asks for name, data source (the one we created in the previous step) and the import schedule (daily, monthly, etc.). There is a possibility to run po-import or post-import scripts, but we will not do it here.
Testing
Yes, we’ve gone that far. The setup is complete and now it is time for the most exciting part of the developer’s life – the testing.
I don’t want to wait till 3 o’clock (when the next import is scheduled) I will click Execute now.
All of the things that we did in the beginning of the article are now happening automatically in the background, so we don’t need to take care of them anymore.
Next step is to actually check if the records have been inserted properly. Here I should mention that before uploading the file to the ftp server, I added some more data entries:
And I can see that all of these have been successfully imported in the target table:
Success.
Thanks for reading. If you enjoy my content and find it useful, consider clicking the thumbs-up button, subscribing for my content and follow me in LinkedIn. Thank you!
Martin Ivanov
Community Rising Star 2022
