[Music] i'm chris sanford from glidefast consulting and today i'm going to show you some tips for loading external data into a servicenow import set like a lot of things in servicenow there's actually several ways to do it each with their own advantages and disadvantages today i'm going to start with the simpler methods and then touch on some of the more complicated ones 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 servicenow platform will have some data they want loaded into a data table the excel sheets 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 that can help you meet the requirement in this example i have a small spreadsheet with a few users which i want loaded into the servicenow user table as an administrator you can easily upload an excel spreadsheet by navigating to system and port set's load data on the left nav 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 i'll choose an existing one i'll leave the rest of the fields populated with the default values and upload my spreadsheet i can see that the import uploaded four records which based on the data in the spreadsheet is the expected result from here i'll click loaded data to see the raw data in the import set table clicking into each row shows me the details which match what i expected to see in the spreadsheet now before i proceed to create a transform map i'll take a look at the user table and see which of the users if any for my spreadsheet already exists doing a filter by email tells me 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 i'll click the link that says transform maps then click new i'll give my transform map a name and set the target table to be sysuser then i'll save then i'll click auto map matching fields which 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 i'll make email the coalesce field for this example then i'll click transform and then i'll click transform again now looking at the transform history i can see that there were three inserts into the user table and one update this is the expected result if i 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 i have a coalesce on the email field the import set table load i just demonstrated 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 i 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 my transform map that i 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 i did earlier of loading the data into the import set table you'll also notice on the data source form that there are quite a few other file formats and also quite a few other types of data sources and i'll cover a few of the other useful ones in today's demo a common integration requirement for servicenow customers is the need to automatically load data from another web services rest api which will typically provide what's called a payload in json format which looks something like this the payload is actually providing some vehicle manufacturer data in this example which is publicly available through this url as i demonstrated in postman so first i'll show you how this file can easily be imported through a data source if i have the file stored locally on the machine all i need to do to import it into servicenow is create a new data source so type file and set the file format to json i'll give my new data source a name and then i'll leave the type as file 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 as you can see in this file here should correspond to a row so for the instructions i'll enter slash results slash results then i'll upload my file and click load all records now looking at the loaded data i can see that this successfully loaded each of the results with its data into the import set table this example is simple enough if i have a file that i 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 i'll need to create another data source of type rest integration hub i would set the format to json as of the previous example and enter the same values below it as in the previous example for the request action i'll need to create a new data source integration hub action through the flow designer interface in the rest step i'll populate the necessary information for my rest api endpoints i can do this by copying the base url as well as the resource path from my 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 i would then need to publish my action and go back to the data source and save it when i load the records you should see that i have the same data that i had in the previous example with these 93 rows and the data on the vehicle manufacturers that's pulled from the payload but since this data source is dynamically getting the information from the rest endpoint i could 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 my 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 could achieve similar results with a little bit of code using a data source type called custom load by script and i have an example of that so here with a little bit of javascript code i 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 i would need to create what's called a rest message first and i can do that by navigating to system web services rest message and i would provide similar information as i did in the previous integration hub example with the endpoint url the full endpoint url with the resource path and i also want to take note of this rest message name as well as this http method name so then when i go back and create my data source when calling my script i'll need to provide the rest message name and the http method name from the previous screen then i'll provide that same resource path of slash results results and then this import set table object which is passed into the script template automatically and then when i load the data i should see similar expected results in my import set as in previous examples with the 93 rows and the vehicle manufacturer data from the payload in each row i hope this information will help you get started with importing data into servicenow or taught you something new if you've already imported data into servicenow before thanks for watching and be sure to subscribe to the channel for future servicenow content