[Music] in the previous videos we were able to import a spreadsheet to an existing table we were also able to update existing records from a spreadsheet and create a new table and new records in this video we're going to get a little more advanced to cover those situations where you need to create new records and update existing ones at the same time for this we need what's called an import set let's look at a diagram from our doc site that helps explain the flow on the left we have what's known as a data source this is where our raw data comes from this may be a spreadsheet a web service jdbc connection and so on the data source puts the data into a servicenow table called an import set table it's the data sources data represented in servicenow tables and fields for example a spreadsheet row is turned into a record and each cell value goes into the respective records fields however the data sources data doesn't always represent the data we need in servicenow we may have to change a date format capitalize a name or map a site column to a location field this is the job of the transform map it cleans up the raw data from the data source it also identifies the target table where to store the cleaned up data such as users locations and more let's go through a simple exercise to see how this works for this exercise we've been asked to update our linux server table we've got a few new servers to add and the os has been upgraded on the existing ones we've got a linux server spreadsheet here with 10 rows of data before we start importing let's take a look at the original data by typing linux in the navigation filter and navigating to configuration servers linux we can see we have four servers each with an os version starting with 2.6.9 from the left navigation menu we'll type import set and navigate to system import sets load data the system displays a page with some options we'll start by telling it we want to create a new import set table if we were doing this exercise again in the future we could use the same one again by choosing it here we'll provide a label for our import set table in this case import linux server note the table name is automatically created for us and to avoid confusion we've added the word import into the label so we don't confuse it with a potential target table we'll leave the source of the import as file because we haven't defined a data source yet we'll cover that in our next exercise now we choose the downloaded spreadsheet of linux servers verify the data is on sheet 1 and the header row is one then click submit the system then imports the spreadsheet information into the import set table we can see from the status message that 10 rows were inserted which is exactly what we expected remember we just created 10 import set rows we haven't added anything to the target linux server table yet the next step is to create a transform app to tell the system how to get our import set data to the target table let's click create transform map we'll give it a descriptive name like linux server transform our import set table is already set for us and we'll set the target table to linux server table and instead of submitting the record let's save it to continue working on some related information in the related links we click auto map matching fields this tells the system to do its best to automatically match the import set field names derived from the spreadsheet header row to the existing fields on the target table and notice that the field maps related list is populated with five entries looks pretty good name os os version yeah good now this is an important part because if you skip it you could get duplicate records we're going to tell the system how to identify new records from existing records typically this is a serial number or other unique id in our spreadsheet we have the asset tag so let's go to the asset tag row in the field maps related list and change the coalesce field to true if the imported spreadsheet has an asset tag that matches an existing records asset tag the data is updated if there's no match the system creates a new record if we have more than one field to set coalesce true then all fields must match in order to do an update this might be the case where we are importing people and there's a chance we have multiple people with the same address and even multiple people with the same first and last name but it's highly unlikely that we have people at the same address with the same first and last name so in that case we would set coalesce to true on first name last name and address okay now that our transform map is created let's click transform the system identifies the latest import set at the top and the only transform map that goes with the import set so there's really nothing to do here except click transform let's go take a look at our linux server table under configuration servers linux again and now we see 10 servers six of which are new and the four originals have their os updated nice work if we get another spreadsheet we can skip the part about creating the import set table and use the same transform map import sets are very powerful and easy to configure they're very common way to import data from a variety of sources i hope you'll join me for the next video when we'll do another import set with a bit more complexity