hi everyone and welcome to the witch doctor sky to service now let me just pull up the PowerPoint there we go episode number 23 today we're going to talk about just like that how to import CSV file using imported tables and data source and in this case is just something I stumble up myself that if everything is fine it works like a charm but what I'm going to show you is that if you have a CSV file that the header row isn't row number one then suddenly you have a lot of issues trying to get that into the import table and data set and all that stuff for those who don't know me my name is a Gorham non twist a Kade what witch doctor I've been working the wit service now for like five years now almost start out as a customer went into the partner sphere and now since April on you're working at service now as I internal developer trying to get the service now to have new service now as good as possible as well a lot of the community trying will trying to be active there right now there isn't that much time sadly trying to keep the YouTube channel up as well bean MEP for three years in a row said I can't be next year since an employee your service now but someone else will be that so you have my contact details on my I think it that way if you like to catch up your hitman LinkedIn YouTube or something like that I am on Twitter but I can't really say that I'm that active the easiest is to connect me with on LinkedIn I guess and I also wrote a book that came out generally this year where I try to take all my knowledge and shove that into a book to help you guys not doing the same mistakes as I did it's on Amazon you can buy it as a book and paperback as well here are few good points where you can see what is inside and actually one of the things that I learned or using today is from the book so I actually went and got my book to remember how I did it last time so what are we going to look for we're going to look how to handle CSV files where the head row is not the first row I'm going to show you some examples how or why there's and how does actually load data works if you have been familiar that because that one if you use load data it's not an issue you can just specify which other it is and it works and then I'm going to show you how your integrations can handle that for example if you are using an email integration so let's skip the powerpoints let me get rid of that one there we go and after this or when this YouTube is actually on YouTube for when this video is on YouTube you will let me fetch that me has to fix something which is easier for me to what I'm actually showing soon I don't know why this window went away for me there we go where is master yep so when you can watch this video on YouTube there will be a so 23 here on my github account where we can go in and download all the codes you don't really need to focus on what I'm writing you try to copy it will be downloadable here so quick tip on your PGI I recommend not using admin as their County or playing around with even if it's easy and fun just create another account and make give them all the rights because if you suddenly accidentally like check the web access only on your admin account then you can't log in anymore if I accidentally do that on Goran I can actually go back use that when I cannot to fix my account and you don't need to try to contact service now and we will help you of course but it's faster to do that the way so let's login so basically our issue is I'll show you we have a cc file where the header row in this case is on line number three overall number three so we want to import this one into ServiceNow so let's go back when we're talking about the load data you can go into load data and from here which is quite simple if you already have a table you can use the existing table like I have or you can say create and just create the table at the same time let's just take my table the source is then of course a file I select my file it that one and I just define that head row that's number three hit submit and voila you can see 13 inserts inserts and if I go to the table you can see that the date that's there are actually two times because I actually did one try earlier so you won't see me fail over over again or something like that so it works but let's go to the data source that was created sorry about that we were going to data source not that was created I'm going to show you now how does it work with the data source if you look at the data source you no longer can see there isn't any row header row or something like that but if we go into this one just to show it so well and I'll see where it pops up you can see that it says header Row 3 it still exists and this is what happens when you create the table there is a data source but already since I have created it we're going to directly so somehow it's safe so my first thought was okay when I create the data source I can add header row 3 or 5 where is and that will fix sadly it won't because what happens when you load the data it manipulates the attachment so if I download this one you will actually see and open up you can see that there isn't any row one or two anymore it has deleted those and just put the row number that you said was the head walk on top so actually when the data source is being created there isn't any empty rows above because if I would do something like this if I remove that let's see if this works I haven't tested everything this should work so this is the one within you the one that has the empty spaces and all those tests load boom we have an error suddenly it doesn't really work if I instead do the same thing it's trying to think which is the correct one so now we have and it works so when I uploaded one without spaces this means that the functionality that load data has that actually takes and remove rows it's not something we can use directly I'll try and look in the back and now that I'm in ServiceNow but felt a lot of work to try to build like back-end functionality in service now because this one is back back in where mainly can't touch between service now UI so what did I do in this case we are going to do the import through an email so it's coming inbound so I have created an inbound action and I'm not going to go through all these functionalities I hope you understand how impound email actions works and stuff like that yeah it's a base thing I can say that this is of course you probably will have a little bit better filter and so on so this one yours right now just takes everything that comes in creates a data sort out of it and of course subjects start with episode 23 so I actually was a little bit you know but I'm not going to do to send email because somehow it didn't fetch emails I don't know why but it's no trouble to death right now so action that's where the action happens so this is what we need to do so basically we need to do the same stuff that the load data do and to do that we're actually going to fetch the attachment and recreate the attachment and save that on the data source instead just like the load data actually works so I just put in a name on the data source then I go in and fetch that attachment in this case I know that the emails I'm getting there is only one attachment so I don't need to find out which one and so on I saved that Sicily for that attachment then I use something called the glide sees attachment get content stream and let me just do and show you you have it under server realizes attachments get content you also have get content but in my case sometimes the attachment was actually above five megabytes meaning that get contact doesn't really work but get content free I can't say there is still a limit because the problem is but it's pretty much higher and as you can see we have the can't come from string so we get is is that contention coming for us and then we use API called the glide text reader that actually means that we can read the context as well so let's find that one tonight and here you can see that I am actually with using a lot of the code in example which is quite good you just go to the documentation and reuse and tweak that example at all exists so what I do is I'd go in and look as long as I use the red line to go to the next time over and over again so if the reader isn't now meaning I got to the end of it I will continued while then is the length of that room if that's more than one which means that the first two won't happen then I'll use the news frame variable to just put in forever value or a solid line and add a line break and by that I'm building the whole new stream DCC file and that is from the example as well down here now that I got from the book as well when that's done I create a new record with the right put it in the current which is the data so freaking I actually renamed it just to know that I actually got the new one because I have some issues and that's the reason why I'm throwing a damp down here and that issue is that if the attachment was too big the deletion of the Oval attention because of course I don't want old one happened before actually the whole content stream somehow was done so some times I got the wrong attachments the wrong so in this case I just put it in then I actually fetch of course the whole file name put in that is a CSV and of course the content then a true in event saying that this import is done or create this event this object and they're not throw in the old attachment IDs or know which attachment is I'm going to delete so if I go to event you can see remember all this register events that you're all going to use if you create them yourself so I just created this one and pretty much just say what's it used for where is it fired from and so on then I have something called a script action which is code that it trigger on specific events and let's find that one here this so you can see that the triggers on the YouTube event and what it does is it goes in and delete the old attachment then I have created two custom script includes because in my case I'm doing this in a scoped application and some of this stuff needs to be run in the global scope otherwise it won't work so that's what basically put everything into the import set we'll go through the different things and then transform run put in percept with transforms and this spirit include is also available on the github to use it so let's go over to the script include so basically you get I will get a few more stuff that you can read use if you want we are looking for the load import set in this case we have stuff to where we can create input sets we can change the state of the approach that we can transform the intercept we have the loved one and for this one we're throwing in the data source so the data source that has the attachment that shall be loaded and here you can see just simple code you've probably seen this code logger out a blog post I think I got some of this stuff from a really good post form I think it was Michael Richie I think Cafayate on the community otherwise we're pretty much says create import set and then I returned the record team put set of record the whole object not only to see City or something like that when that's done I go in and lie to transform it so in that case we're just needing to the city hired outer curve where I can meet other fields on the input set record so that's why I'm thrown back the whole object instead of a lot of parameter so so you get the sis ID and I'll think the transform and the same thing here we're pretty much just running and this is a way to run here you can actually see that this example is actually running the self integration that are built and I won't go through all the different stuff but there is and that's how it works just to go through just to see what other stuff there is I have also some issues where I actually have dates coming in like and I needed to River convert them to this I don't know if they're any easier way I just rode in something like that to replace the data then I have some other stuff that I needed to do in s goat version as well but just feel free I think I've been good enough to document at least there what to do so hopefully it'll help you so if you need it go to the github you will find an episode 23 there hopefully this will help someone that had the same issues as I have and if you have any questions just write it in the YouTube comments and I'll try to reply when they pop up okay thanks for your time see ya