hi everyone i'm assuming that by watching this video you've already imported spreadsheets into servicenow sometime in your life it's a bread and butter operation and we do it all the time well did you know that you can also import spreadsheets using integration hub and you can also apply data transformation to your data without scripting let's find out how you may or may not know that servicenow is moving a million miles an hour towards no code and local development and the feature of importing spreadsheets using integration hub is part of this general movement now we are looking at a relatively new feature in servicenow it was introduced in rome so there are some advantages and some disadvantages as well in this current release which we're looking at in san diego but i'm expecting in the next releases whether it's tokyo or utah or whatever release that comes after that we're going to see some significant improvements to this feature okay so now having said that let's find out how this feature works let's go to integration hub import one word still even though officially it's two words integration space hub but in the ui it's still one word and integration hub import right here now you will need a subscription for this particular feature for integration hub and you'll need to plug in the data stream plug-in for integration hub so depending on your subscription you may or may not have this plug-in as part of your subscription so once we do have it open let's go ahead and create a new integration okay so what we're going to do is import a spreadsheet it looks a little bit like this here so it's just a simple list of service desks with uh name location parent cost center type and manager so we'll give this a name and just copy that into the description as well because it is a mandatory field save it okay so one of the good things about using integration hub to import your spreadsheets is that you can actually check that you're importing the right data before you go ahead and create your transform maps so we'll select the data source as the file that we want to upload so it won't be a an integration hub spoke it won't be a data stream we'll select file and then select excel and then we'll go ahead and just drag and drop that spreadsheet over okay so the good thing here as i said is that you can actually preview the data structure before you proceed so if i were to do the following and select the sheet number as one and also the header row as one let's have a look at our spreadsheet here the header row is actually two not one so if we just come up here to the top and select load source data structure we'll get a preview here of the fields that we've collected and they are not the ones that we want so we've got an opportunity now to correct that and specify two as the header row and then load the data structure once more there we go that looks a little bit better okay so let's go ahead now and move to our transform app you can see on the left hand side here there's like a little menu to follow so we've specified the data source already now and now the next step is to go ahead and create the transform app so let's click on add a mapping specify the target table name there we go we will select groups so similar to when you create uh transform maps uh in the regular interface you have the opportunity to turn off business rules if they are hampering or slowing down the speed of the import especially if you've got a large number of records to import it may be wires to disable business rules and you also have the opportunity to import synchronously as well but by default we are importing asynchronously so let's save that and then go ahead and open up our transform map okay so on the right hand side we've got all the fields from our target table our group table and on the left hand side we've got the data structure from our spreadsheet so now it's just a simple operation of dragging and dropping across the fields from the source table to the target table and doing a few other things which we'll look at we'll look at data transformations in particular that's one of the best things about using this interface um it resembles a little bit uh flow designer because you've got these kind of data pills so you can just drag and drop them over so we've got the name that will just go over to the name field here i'll do all the simple ones first so we'll take parent and put that down the bottom unfortunately these fields here are not in alphabetical order so sometimes you have to search a little bit manually for the correct field uh location we're going to deal with a little bit later manager i will just pop that in there and you'll notice for all the reference fields and choice fields just like in a regular transform app you have the opportunity here to select what happens or what to do in the case where there is no exact match for that value whether you still want to proceed and create the record and create the value if you want to ignore the field or if you want to actually skip importing that entire record so that works the same way here okay so the next field i will take is type and pop that down there let's try that again there we go and cost center i'm going to push this over here as well so i think we've nearly got most of our fields and data there okay so the really big thing about using this interface is that you can apply inline data transformations let's take a look for all these pills that we've got here you can see similar to flow design you've got this inbuilt function or transform button here if you select that you get this little library of transformations to perform okay so it's not as big as what you would see in flow designer but nevertheless it is a start and you can perform some of the most common transformations right here without having to script because if you were to have to do this in the regular interface building your transform maps there would be no option other than to script okay so things like moving or converting text to all uppercase rounding off numbers converting two numbers replacing text either using reg x or just a regular find and replace search here you can trim the spaces of your strings that appear at the front and the end of your strings and you can also convert to boolean and you can also convert to a date if you're not really sure or you know what's going to be what's going to happen with these operations just click on the little help there and sometimes you actually get examples as well okay all right so let's take a look at how we can use some of these transformations right now the first one we're going to look at is concatenation so we can actually take uh or populate more than one data pill in this field here okay we're not restricted just to one value so for the description field down the bottom here i'm actually going to use a combination of the group name as well as the location so let's just pull the name and bring that down to the description here i'm going to add a comma so you can actually add and hard code text values here and then after the comma i will add the location right there and yeah that's it so the description of our new group will be the group name followed by the location let's take a look at another example so if we go to our spreadsheet here we can see there's actually a couple of fields here that have some white space at the beginning service desk admins here the group name as well as the itool type here now what you can do with string fields is trim white spaces from the front and the end of those strings so so just to be on the safe side we could come to our name field here click on the function button there and then select trim okay there's no other parameters you have to specify you can just select okay and that will just as it is described here just trim the the white space from the beginning and the end okay however you will find that servicenow is actually smart enough to kind of do this for you for most field values so i think even if i didn't specify that transform uh there for that particular field uh we will be just fine i think for reference fields it's even smarter because it will just say okay is there a match for the value if so let's just reconcile it to that value and remove that white space in doing so okay so the next transform will perform is on the cost center so in my spreadsheet here i've got some cost centers here 2600 25 and 24 hundred but let's assume that the cost sentence here or the 2600 ones were incorrect or they needed updating and we needed to specify 2700 instead well that is real easy to do here we just select our transform button here for our cost center go to replace and then it's just a simple find and replace operation here so i can find all instances of 2600 and then just replace them with 2700 click okay and that's it okay notice also that if we wanted to perform further transforms here we can do that we're not just limited to one here another operation we can perform to replace text is by using regex so what i'm going to do here is to populate the email field now if we look at our spreadsheet we actually don't have an email address here but if we were to assume that the email address is actually the name of the group minus all the white spaces plus at company.com we could do that quite easily here as well so let's come back here to our email now the first value that we need is the name of the group again so let's come over to our name and drag and drop that over let's try that one more time there we go okay so as we already mentioned we can actually hard code values here so i can actually append to this the at company.com here domain okay so the email address at the moment would look like the group name at company.com but we know that our group names actually have white spaces so we need to get rid of those so we can come again to our transform select regex replace and then if you know a little bit of regex you may know already that the backslash s will match all white spaces we can leave the replacement here blank so we can just select okay and that will just clear all the white spaces between the group names okay and i think we are just about done okay so before we move on let's speak about some of the limitations that i know of at least with this interface so one thing that you cannot do here is employ your own scripts what you put in here is text only it will be interpreted as text even if you were to write javascript in here followed by a colon and then code something uh it wouldn't work so this is something that you can do in certain field properties in service now you can just add the javascript name there and it will interpret what follows is javascript but you can't do that here so if you want to do any kind of scripting any kind of more advanced operations for your transformations then you'll have to use the regular transform maps or that regular interface and one final limitation i've noticed as well is if we just go ahead and save that first of all and then move to i'll come back to our transform maps here it's actually not a list and what i mean by that is that you can only have one transform map here okay in other words one data source one transform map not like you could previously in or in the regular interface where you can have one data source with multiple transform apps you can't have that here so this button here on the top right add a mapping it's grayed out okay again this is a relatively new release feature uh the final step is now to go ahead and schedule an import so you can actually go ahead and click here to create a new schedule for this import but because it's just a spreadsheet it's kind of a one-off import at the moment so i'm not going to worry about setting up a schedule instead i'm just going to click on run import okay so that's already started so in your executions here everything is kind of blank but there is a refresh button here if you click on that okay we can see here it's executed and if we open up that record we can see some reports on progress and the results of that transformation okay so let's go to our target table let's come to our groups here i've got a filter here pre-configured for updated today so let's click on it to refresh and now we've got our six records so we can see here let's open up one of these records here we've got the name we've got the manager we've got the description that concatenation of the service desk name plus the location of the group we have the group email okay we didn't remove or convert to lowercase but we could also do that as well but that email will function just fine we've got the parent we've got the cost center and we've got the type itel if we go to one of the other groups one of the actual country groups for sweden denmark or norway we should find that the cost center is now 2700 instead of 2600. okay so let's open up denmark and the cost center is 2700 as expected so there you have it so that's how you go ahead and import spreadsheets using integration hub as you can see the user interface is a lot easier and cleaner you can just use those data pills to drag and drop from your source table to your target table to create field maps and in particular the big feature is is that you can create data transformations without needing to script okay so thanks very much for watching and see you next time