How to Compare Excel Sheets in UiPath (Full Tutorial)

comparing excel sheets in uipath is very easy if you know how to do it this is the complete guide for comparing excel sheets in uipath first we will start by using the uipath activities those ones have the advantage that they are very easy to figure out they are low code and you can easily add other activities based on your needs then we will go on to sharp link link is a component for querying it's very similar to sql and it's so so powerful master this and you'll become a great rpa developer my name is anna jensen i'm a uipath most valuable professional and i'll teach you everything about comparing excel sheets in uipath make sure you open up uipath and do the exercises with me in that way you will learn the most if it goes too fast just pause or rewind the video is the sign for that let's get started again you gotta promise me that you open up your own uipath studio and do the exercises with me in that way you will learn the most let's download the course materials you'll find the link in the description below and that will take you to this page here i have created a lot of course exercises for this video first we will use the native uipath activities those are the ones that you can drag and drop in it has a huge advantage that they are very simple we can just drag and drop them in you'll learn a lot about uipath and you can easily add other activities to the flow then we have the more advanced ones those ones are link and they are code heavy in fact they are almost all code but they are very simple so they are usually just one or two lines of code and then we'll get a result i'll recommend that you go through the entire video because we'll touch a lot of important concepts now scroll a little bit up and download the excel book that we'll use in this video so click this download excel book here i mark this and then i click download now let's place the excel book here on the desktop or whatever you want mine is on my desktop now i can close down this one drive here and let me maximize still the course exercises because we will go back to these exercises when we have finished one first we need to find the matching rows using uipath activities we will find matching values of the two excel sheets and update a column and afterwards we will update to a new sheet let's inspect the data first so i'll open up the exercise data here we have it let me enlarge it a bit i have two sheets i have one sheet with id and name in and three rows one two three alpha bravo charlie i have an almost similar sheet two with id name two 234 bravo charlie delta and let me enlarge this a bit as well so you can see that there's a little difference in each one of them here i have an alpha one i don't have it here but i have a four delta which is not present in sheet1 so let's create an automation that compare sheet1 and g2 and then tell us how we can find the matching rows of course we can see it with our eye here that is bravo and charlie but imagine this is an excel sheet with a thousand rows then it's nice to create an automation in uipath do also pay attention to the column headers because we will use those that those ones are id and name now we can close the excel sheet but it's still present at our desktop and we because we will use it in uipath let's shift right click here copy as path then we will use it in our automation in uipath so let's start up a new process so just click process here we can call it something i will call mine compare xlsx like this call us whatever you want we will use the default settings make sure that you have the language vb that is the default so then we click create and why i say that make sure you have that language is because all the supporting documents all videos on the internet or almost all videos those ones are in vb.net it's quite similar to c-sharp but still so let me maximize this and i open my main workflow we will create new sequences for each one of these tasks so you can easily go back later on keep this one as a reference in a folder at your computer after this session and then use it over and over when you need to create the automation so first let's go to project one thing that i want to make sure is that you click settings here then you scroll a little bit down make sure the modern design experience is enabled this one is introduced in the ui path 20 21 10 update and we'll make sure that we use the same activities so i just click cancel because mine is enabled like this first we will create a new sequence for our first task so i click new up here i say sequence i will call mine one enter sect uipath activities update column those ones are good because we will make 11 exercises so we keep a nice reference so make sure you take this name or something similar then i click create here we have it here we have a sequence let's just delete that and start completely from blank now we need to read the two excel sheets into data tables and let me just refresh a data table is nothing but an excel sheet in the memory that is we have rows and columns exactly like an excel sheet but it only exists while our robot runs data tables has the huge advantage that they are easy to manipulate and that one will be doing here in this course so first let's go to activities and then we'll find a read range so i'll search for read range choose the one on the workbook that they go those ones work without having excel installed now we need the workbook path and since we already copied that from the excel icon here on the desktop i can just choose to create a variable here so i'll press ctrl k always create variables where they belong so up here instead of here then right now it's not a big issue but usually that one will create a variable of the right type so this one will create a string it will also have done it if you clicked here but in case you have a more difficult variable type then ui will automatically create it right so here i'll say str excel path like this and then i click enter again you can call it whatever you want and now we go down to variables because this one is empty so i give it a default value i simply just ctrl v paste in the path that i just copied for before then i can close the variable manager what happened here it will read from this variable and since i gave it this value here it will just take this value and use it so we will use a sheet 1 first you remember that one was the name of our excel sheet i will read the entire thing so here i'll just delete everything that's in it now i just need an output so over here in output again press ctrl k and now the clever thing begins because here i can say dt sheet1 and click enter and now if i go down to variables then it says dt sheet1 and now you see it created automatically the variable type as data table that is fine so now we have readsheet1 into a data table let's read sheet2 as well so if i just ctrl c go down here and paste it in because we will use the same read range so here i just change this one here to sheet2 and then i go to output delete it here press ctrl k dt g2 press enter that's it so now we have read both our excel sheets in two data tables now we can start the process or the real process since we want to update sheet 1 with information about if it's a duplicate column relative to c2 then we'll make a new column in sheet 1. so if i go down here to my activities i'll find a add data column you can do this manually as well in your excel sheet but i just like to have it in the robot but feel free to do it that way so the name of my my new column i go down here in quotation marks then just give it your name i'll just say match and here i'll choose which data table i want to add this column to that one was this data table up here dt sheet1 so now we added a data column to it we can start comparing them and update this column here so we will loop through sheet1 and then we will say for each one of the rows in sheet1 we will take a look at each one of the rows in g2 it seems very complicated but it's very easy we will make a nested loop so if i go up here and then i'll say for each data row in data table and drag it down here what this does it just loop through each row of the data table we just converted our excel sheet into a data table and now we're looping through it down here so i'll say for each cue and row this is just for reference you can call it whatever you want so we just leave it here and then i'll say dt sheet1 this is intellisense that shows up if viewer doesn't show up it just looks like this press ctrl space it will show up that one is nice to have so then i'll say dt sheet1 so we are looping through every value of sheet1 but so every value in sheet1 we want to see if that is present over here so we take we say first one is the first round sheet one then we wanna look in sheet two so now we make a nested loop i'll drag in another for each row because this one will loop through sheet two and for reference since this one is for for each cue and row i will call this for each current line again i could call it whatever i want it's just for reference so change it to something like this i'll just recommend you do this because then you can follow the exercise in this video and then i'll say i want to loop through dt c2 there you go so now we're looping true so we take each value in sheet 1 and then we take each value in g2 now we want to make the comparison i want to ask so for each one of the values in sheet 1 the ids those ones are unique numbers then i want to ask is that present here in the queue and row in g2 so i'll find an if i'll say if and then i drag it in so inside the inner loop here that for each row in dtc2 i'll make a condition and if always always ask a question that can be answered as true or false so we make a condition and then based on that condition then we go if it's true we go to then and let me click show else here if it's not true we go to else but let's just hide else because we will not use it this time so now i just need to make the comparison to target this q and row here and the id column i'll say again i press ctrl space i'll say cue and row then i'll say item that one is the column name parentheses then quotation marks then i give you the column name that is id then i convert it to a string like this so i take the string value of the id column that is one two three four and then i want to compare it to the dissimilar one here in the current dtc2 again if you don't understand these nested loops try to rewind the video a bit because what we do here is that we take each row in sheet one then i take for that row i take each row in g2 and look for that value so i'll say equal to and since i'm very lazy and you should be that as a bot developer i just copy this one here paste it over here and simply just change it to current line like this so now we have the condition and let's just recap what we want to do we want to update the value in the corresponding row of the sheet1 in the match column so what i will do here is that i'll find an assign i'll have it here in my favorites otherwise search for it so now i just need to say where do i want to add that one too i want to add it into the cue and row so again control space cue and row like this then i'll say item because now i want to it's still in the same row as we are making this comparison on i'll say item parentheses quotation marks and then i'll say match because we want to update the match one here now i just need to give it a value since it's a string i'll just say in quotation marks i'll say yes that's it now we are ready to go but since we just want to so whenever we find this match there's no reason to loop through all the other rows we already established that we find a match we can save ourselves or our butt some muscles so i'll find a break activity and put it in here what a break activity does it simply just it breaks the loop that it's in so it will break this loop and go back to the outer loop and take the next row now we just need to write out the results because we have updated dt sheet 1 up here we've updated that with a yes but it's still remember it's still a data table and it still only exists in the memory we want to update the excel sheet as well so go out here in activities and find a read write range sorry it's down here make sure you are outside all these loops we will only do it once otherwise we will keep writing to it over and over so i drag the right range out here in the end now it's clever that we used a variable for the workbook path remember we took one the str excel path i just picked that one where do i want to write i think we will write to new excel sheets and not overwrite our existing data so for each one of these exercises we will have a new sheet one results and then i will delete this one here what data table do i want to write i want to write a dt sheet 1 since that one is the one i'm updating i just need to click add headers over here since my data table has headers and now we can run our automation so let's try to run it there you go we have completed it in one second let's look at the exercise data we go to run result and here you can see that we have this is just sheet one with a new column in we have bravo and charlie so bravo charlie those ones are present in c2 there you go you completed your first exercise let's move on to the next so i close this one here and i maximize uipath and for the second exercise let's scroll a bit up and we since we will be doing the same thing we just make control c we will copy this sequence because we will reuse a lot of the things that we just did so so make sure you are in project down here we make a new sequence that one will be named two and it will be named almost the same i'll say enter section uipath activity activities new sheet so now let me create this one first and let's just delete this and copy in our just or paste in our just copied solution so this one is just solution one these two solutions looks very similar but while the first one we just updated the same sheet i know we copied it out to a new sheet but what we did was just updating sheet 1. say that you want to copy out the matching rows to a new data table or sheet then it will look like this so we will not be using the match column anymore i'll delete that we will still read the two ranges here and we will go a little bit down we will not use this assigns image we will not updating cell anymore so i'll be deleting this and we will still use everything here but let me just take this one as two results we'll write to a new sheet in our excel book let me scroll up let's just talk what we want to do here i want to create a new data table for the results and write that to a new excel sheet so what i will do here i need the headers of sheet1 that's all i need and to get those ones here i just copy this one here i go down here below sheet2 and paste it in because i will read sheet1 and let me go to properties over here i will retreat one but i'll read it into a result data table so i delete this press ctrl k dt results and press enter so i do this because i want the headers again and i want to use them to have a result data table that i can use to copy my rows to and let's just go to activities and i think we will make a sequence just here and drag this one in here we can give our sequence a name this is just for an easier understanding of our workflow i will just say here create result data table so what i do here is that i copy each one of these rows into this data table and now i repeat things a lot but i want to make sure that you get the logic it can't be a little bit complicated but it's not if it's too complicated just refine the video a bit so now i've read the range but i don't need the data to it i just need the headers here that's all i need for my result data table so i'll find a clear data table search for clear and drag it in here what data table do i need to clear that one will be the dt results again we will just use the headers and this one will just clear and keep the headers on for us because now we will do the clever thing of this exercise so exercise 2 is all about copying the rows to another data table so what i want to do inside the inner loop here we're still here now we found a math then we want to copy it to dt results so i'll find the add data row so even though you don't need to compare excel tables these exercises are so good you will learn a lot so i'll add in this add data row this add a data row into a data table so here in the array row we will add the queue and rule because we know that q and row that one was a match in in c2 at between sheet 1 and g2 so what i do here is that i can press ctrl space again i'll say q and row and then because i want the entire row i'll say item array like this that's it now i just need to say where do i want to copy this row 2 i'll copy it to dt results that one was our result data table and now we just need to make sure that we write out the actual dt results that we just created so i say dt results like this and we can run the flow so i click run file there you go you might wonder why i sometimes click debug and sometimes run file there's no difference and in this we use the debug when we want to find the arrows in our robots here there'll be no arrows in my robots today or at least i think so i will just use the debug and run file here and there but usually i use the debug when i build these things myself that ones will sometimes are more complicated it's nice to debug these things but let's inspect the data so i go to exercise data and now i have a two results there you go we have copied the matching rows to our two results so so here we just updated the column say yes yes and here we copy the rows to a new data table and a new sheet that's it so that was exercise two let me close it let's go to the course page scroll a little bit down because we have completed this one here we find matching rows now we will find unique rows those ones are the ones that are present in sheet one and not g2 we'll create two automations we will create one first where we just update the row in sheet 1 again just as we did with in another column and then we will create one that will update to a new excel sheet so let's create it in uipath we will use the one here we will copy that one so make sure you copy your solution that we created in the beginning so mark everything ctrl c then we will go up here to new sequence and we can call it tree except uipath activities update column like this again we keep good descriptions so we can easily find it afterwards for reference in the future i just delete this and then i paste in the sequence that we just copied from the one intersect so now we have it we can modify this a bit and then we will get going if you like this video it will really help me a lot if you gave it a thumbs up that will improve my reach of this channel and maybe someday i can make a living out of making these youtube videos so thank you now we need to add a column it will not be a match column but we will add a we will call it unique that ones are the one that are unique values from sheet1 and we could see it it was only the first one that was one alpha but again we don't know so now we want to update this unique column but since we can't do it here because unique value there will be no match so we need to have our flow looping through all these ones here and then we will say oh well there was no match in all the lines so we know to find a unique value this one will not be true so we create a supporting variable go down to variables then i'll create a variable called bool match and in the variable type change it to boolean a boolean is just a variable that can take the two values true or false it can never take any other value so again we ask a question and these booleans they hold the results that we can have so we create that one since now here and a boolean is always false by default so we don't need to give it a value this one is false remember that so what we will do here we will make sure that the scope is for each one of these current rows and then the scope of the booleans that will be this outer body so let me just rename it so we can easily see it and that also means if we give it this scope we haven't gave it to it yet or at least i think then it gets reset and that is quite handy because we want it to be false whenever we take a new row and then instead there's a new situation so again go to variables and here we'll say pool match scope sequence we want it to be the outer body so click this scope here and change it to outer body that one will work now we just need to say if we found a match then we know that this bool match should be true and then we can break it because then that cue and row is not a unique value so let's just change this assign a bit so i'll say bool and then i press ctrl space because i'm lazy press the tab this one is here you can give it a string value as i just told you we can give it two values i'll give this one as true so now we'll say well we found a match that one is true we can break the loop this is not a unique value and then we come out to the outer loop we also come out to the outer loop if we loop through all of these current lines in the c2 and there's no match so now we can just look at the bool match whenever we're done here in the inner loop and say is this pool match false or true if it is false then it's a unique value we didn't find a match so in the outer body we will add an assign so dragging the assign here just make sure we're here it also it always works well to just mark it and then you can see these blues one blue ones here those ones are here we are in the outer one now we just need to update the newly created unique column so i go down here and then i'll say cue and row so i'll press ctrl space like this then i'll say item parentheses quotation mark let me move the mouse a bit that one can be annoying then i'll say you need make sure you spell it right and then i'll say in quotation marks yes but now those ones will add unique value to all our items we only want them when whenever they are unique so i'll find an if or maybe you have it in the reasons you probably have so i've dragging this if and i can just give it up here now we just need to make the condition and that is just if this pool match is false then it means that we didn't find any matches so i go down here say bull match then i'll say equals false remember we in an if we make a question that can be answered but true or false here is the boon match equals false that one can be either true or false if it's true that means that the bool message false then we are updating this one here now let's go a little bit down here so we will write out to the three results and since remember here we only have two ones we have the dt sheet one that one is our data table are we writing to that sure we can run our workflow so i'll just click run file there you go let's just inspect our data it's always nice to do so in three results there's the alpha that one has a unique value bingo we completed exercise three now we just need to copy the rows with unique values to a new data table and a new sheet so we will take this one here without the unique that is just id name alpha and copy that one to a new one again this is the simplification of real workflows that we do so in these ones are very easy to comprehend in your brain and my brain for that matter that's why we do it so let's close down the excel sheet and then go to uipath because we will use again we will just reuse reuse reuse always reuse as an rpa developer now we will copy project 2 this one here so i can either actually i can copy the sequence or let me go to project and show you another way that we can do we can go here i can right click i can copy i can right click again and again just paste it there you go just make sure that we actually have in the copy that one is here let's just rename it so i right click rename there you go so now let's give it a new name ours is four and it's called accept like this again you can use these ones here as a portfolio for future projects those ones are nice to have so i'll say for accept uipath activities oh i shouldn't have done this should i new sheet like this that is fine that is exactly what we want to do i just delete this copy here so now we have it and if we go to four i open this one here there you go that is a copy of sheet two now we can del we cannot delete we close down the tree here so i'll close that one here so we will change it a bit and we will change it a bit like we did in exercise three so we will create a bool match and then we will just do x the exact same things that we did before so i create a variable go down here and make sure you select the scope if you just see this just select the sequence there you go so i create a new variable called bool match make sure it's a boolean again this one is false but default as mentioned so i scroll a little bit down there you go so here what we will do here instead of this add data rule we will do that in an outer if but we will so this one will go away in a bit so what we will do in here if we found a match we will again update the value of the bool match so i go to activities then i'll find an assign and drag it in here so i'll say bool match equals 2 not in quotation marks since it's a boolean equals true that's it so it is is a copy of what we previously did this is just to show you how easy it is so now we know that doesn't match we will make an if in the outer loop and then we will use this at data row so don't delete it we will just control x we will cut it away so i go up here in the body and then i'll say maybe i'll just say outer body so i know that one is the case again make sure your boolean is defined in the scope so change the scope to outer body here like this that one will get reset every time we are running a new row in this iteration the outside iteration so scroll a little bit down now we'll drag an if like we did before so i'll drag in an if i hope you do these exercises with me that ones those ones are teaching you a lot in case you have any questions to these exercises simply just post them below here in the comments i'll get back to you as soon as possible if you have general ui path or rpa questions feel free to join my discord we are now more than 1700 rpa developers that are helping each other and networking around our careers the link is in the description below now let's get back so here we will say if the bool match equals false then we know that we have a unique row we will take this activity that we just copied and paste it in here that one is fine so it's the exact same thing as exercise two and three combine then we scroll a little bit down so we will take a new sheet to write to that one will be four there you go we have now created our flow that can compare to excel sheets and then copy all the unique rows so let's try to run it it runs in one second there you go we will go to the exercise data click the four results there you go that is the unique value of sheet one one alpha and it actually is right so here i have one two three alpha bravo charlie but we are comparing in the id and here i have bravo charlie delta let's say that we also want to compare on the name then i go in here then i can just say these two ids equal each other and and let me just copy this one here so in case we want to compare two column values i just delete these ones here name it will not make a difference here because the names are the same as well but we just do this if i click the three dots over here this one will be my condition to add more columns that we want to make the condition on but since we just won the id for now that one is fine later in this video i'll come back to how we can compare entire rows so i click ok so now we completed the first four exercises let's take a look at the two next ones those one are the last ones for the ui part native activities before we move to link now we want to make the union that is we want to find out which values that are in total that those ones are the one in sheet one and two and then we will remove the duplicates so as you see here this one will be one two three and four that one should be the result that goes into the next sheet and since it doesn't make any sense to update the same excel sheet with something we update a new excel sheet with the union results so there will only be one exercise for this one here so let me close it we go to uipath like this we will copy exercise one so again we will use this method that we did here so i'll just click copy here right click again and paste it in so this one here i'll rename so right click rename i will say 5. this is the last exercise before we move to link so make sure you stick this one will be very exciting so i'll union the uipath activities and then i will just say a new sheet like this then i click ok so now we have a five let's close down let's make sure we save the number four close it down and open up number five those ones are the one we need what we need here is simply just the two read ranges so delete this add data column we also don't need these two iterations we are not comparing here we have uipath activities for it so i just delete this one here then we need to write range but we will say we want the data we want to write it to five results so now let's make this one here we will merge it and then we will remove the duplicates so i'll go to activities then i find a merge data table like this and drag it in between the read ranges and the right range so here the source that is the one that we are taking from that one will be dt c2 it doesn't matter and here the destination will be dtc1 so now we merged it but there will be duplicate values so let's remove those by having a remove duplicate rows from the data table here so remove duplicate rows after the merge ones those ones will be removed from dt sheet one because that those are the one that we've written to so just remember one thing to out here in the output you can easily overlook this so over here in the output just say dt sheet one we are writing back to dt sheet one here let's do it and see that we can actually union all our rows from the two sheets so i'll open up the exercise data so in here in the five results we have union one two three four alpha brow charlie delta that's it that's it for the uipath native activities now the fun begin make sure you stick you will learn a lot here so it will be very easy but it will also be one or two lines of code just make sure that you copy this one you will learn it very very quick you don't need to be a programmer for doing this we are back in uipath let's create a simple solution a link solution but a solution that compare rows from two data tables and copy the matching rows into a new sheet what is important here is that we compare the entire row we will get back to in the advanced section we will get back to comparing the column names that we want but this one is for the entire range so i'll go to project go down here so we will again we will just reuse the ones from the one that is at least the read range so i'll copy here again then i'll paste it there you go so let's rename it so i right click rename and since this is exercise six and that is intersect that is right we will see what values what row values they have in common but now it's not uipath activities it is link so and we will not update a column we will a new sheet like this then we click ok so um since this one is number six let's close down number five and open up number six and let's just talk about what we want here you'll see that this is very very easy we don't need these for each here so i'll delete that one here the for each data table we also don't need a new column i'll delete that one we still need to read the two ranges into data tables we also need to write range but since it's exercise 6 we will do it here i also want to create a new data table for our results so i go down to variables then i'll say dt results like this make sure it's a data table there you go so now we can make the link expression you'll see that it's very very easy what we just need to do is find one activity and then we need to write something in and we're done so i drag in the assign so here i'll write to dt results that one was the data table that i just created down here it's empty and then i will write to it so what do i want to write i will say and let me open the expression editor over here so what i will do here is that i will have the dt sheet one i will use the as enumerable method this is just some sort of a list and then i will use the intersect method that's right we have a method just for doing this so i'll say intersect then parentheses and i'll do the same thing with g2 that is making it as enumerable so i'll say dt press ctrl space c2 as enumerable like this then i'll say it actually give us a lot of help here so in case you are in doubt you can always look at these ones here so i'll say comma and then i'll just say data row comparer so let me again press control space data row compare can you find it it is here so that one is here and then i will say dot default like this what i need to do here because now we have it as enumerable we will copy it back to a data table because that one dt results is a data table and we can write that one to excel so i'll just do this then i click ok we are writing to the six results but we also write to dt results not sheet one like this let's see if it works you can see it's very very easy there you go it's also faster than iterating true or each one of these excel rows here you go we have found a matching rows now it will get very easy because we want to find the rows that are present in sheet 1 and not g2 and to do so let me just go back to uipath again let's just copy it so i right click here copy paste it in just rename this so rename let me move this copy here and instead of intersect i will call it accept and of course it's exercise seven there you go so i'll say okay there you go so what i just need to do here in the assign i'll just go to properties click the three dots here so what i just need to do instead of intersect i just delete this press ctrl space and i'll find the accept that's it now we have completed exercise 7 wasn't that easy that is the power of link i will recommend you to start with link in case you want some guidance on it you can join my discord as previously mentioned we will have exercises course classes and you can ask questions there so i'll say i write to 7 results i will write out dt results that is fine let me run it again let's inspect our data so seven results there you go that those one that is the value that is present in sheet one and not g2 that is right we will have more values if we have more values from sheet1 and not in g2 now we want union so again i can close this one here go back to uipath so let's do that so i'll just right click here copy paste it in this one will be exercise 8 so i rename it again keep good order in your sequences so they are easy to find afterwards so now i'll say union and then i'll say link new sheet that's fine it's not a copy one but it is this one here so now we have the number eight we can open the eight and close number six because we just need to change one thing as mentioned like just go in here and change it to union there you go and now i just need to change it to eight there you go now we can write out the results so i run the file it will work open up the exercise data eight result there you go the union is there that is how easy link is when you compare entire rows in a data table now we will be more specific we will look at still we will compare excel sheets like these two with link but we will just take on individual columns just for now id but we can easily add more columns so i close this one here and i maximize uipath and now let's move on to the advanced link and don't worry it's not that advanced if you don't have given this video a thumbs up yet i'd really appreciate it that will help me and my channel a lot now let's get started we will create a new flow so let's just copy number eight here so i right click copy we're getting used to this and then we paste it it's this one here so i right click and rename i will call this 9 intersect advanced linked so here we're basing it on column values i'll show you in a bit this one will be to a new sheet as well then we will do this now i can close down number eight and open up number nine so what do i need here and need the exact same things i need two read ranges into sheet one and g2 and i need to write it out but now we will write it out to nine results make sure we do that and we will fix our expression we will make it a bit different not that much but i will click the three dots here what i will do is that i will still have the dtc as a numerable then we can work with it but i will delete this we will use the copy to data table but for easy inspiration i will delete it for now so i'll say where and then the parentheses this one will just have so we will pick all the things from sheet 1 and then we'll have a condition that is aware if you know sql or any other querying language it will be very similar then we will define a function that is the outer function so i'll say function this parentheses s this s is simply just a name i gave it you can give have it all other names so then i'll say function that one relates to the outer function and then i'll have my dt sheet 2. so i'll just say dt c2 again we will use the as innumerable we will use that in link so i'll say as innumerable then we will have another where so i'll ask where do these two equal each other so i'll again i'll say dot and i'll say where and again sometimes it's better just to do the incentives then it will be nice and readable with capital letters in the beginning so and then i'll say another parentheses i know this one gets a little bit but we'll do the same and then i'll define a new function this one is the inner function so now i can ask if the id column from dtc2 equals the d id column from a dt sheet 1 that was a little bit complicated but it will be very easy so i have a space here and then i'll say so now i'll say my function r that one was the inner function that one was the from the dtc2 that one is r then the parentheses quotation marks id it even helps us here that's sometimes very easy i will do a video about link that one will be a pay course but it will be complete link in uipath will be very advanced and longer video you can sign up to my newsletter that one will be in the description as well and you'll get notified when that is ready but for now this one is very good so i'll say id and i'll say to string like this and then i'll say equals equals that don't just mean that i will ask if this one our id equals the one in the function that we defined over here that relates to the dt sheet one so equals and then the parentheses and now i can give the value so what values you should this one equals well that one will just be the exact same thing as before that will or not the exact anything but that will be the s so i'll say the s then a quotation marks i'm very um excited to see if all the parentheses will match we will see so now i will say i d and then i will have it to string afterwards so i'll say to string like this so now i just need to this one will say where and this one will have either a true or false and since we need to pick up that value we will ask the any method that one will get us all the ones that are true those ones are the one that where these actually equal each other now we just need to copy it to a data table like this i will just have a breathe of relief we completed it again let's just see what's going on we have the dt sheet 1 as a numerable then we ask something we define two functions s that is the data from dt sheet 1 and r those ones are the data from dt c2 then we ask so these two functions with the column names id those ones are the same if you have two different column names you can change it those are this one true then we want to copy it to a new data table let's try to run it let's verify that we have nine results here and let's just run it there you go let's inspect the data so what we do here is that the rows we have in common so we have the nine results here two bravo 3 charlie as you can see link is very easy now again you can do the and operator so if we are here in the assign and go up here if we want multiple column names to compare on i just here in the rrd right here i just say and and then i can make a condition like the just copy this one and have the name in here and compare with that one too we will not do it this time but it's just so you know you can do it now we will move on to the next exercise because what will happen if we have no a matching data rows say that we have no combined things here then this one will actually draw an arrow and we will have a problem let me show you so we need to do a little bit of aero handling when we work with link because let's create a c3 i know we have a lot of sheets but just create a new sheet click the plus sign here that one will create g3 let's move it down here so what i want to do now is that i want to compare with sheet one and i'll find one row where i don't have any matching things and let me show you the problem so i'll copy the two headers here i'll copy those ones over to c3 then i'll look at c2 and pick this row here so i'll copy that one too and move it over here so now sheet1 and c3 doesn't have any erosion in common let's try to see what will actually happen so if i go down here and go back to ui path first let's write the results out to number 10 so we have a new one and now if i run this and let me just change this to sheet 3 since we are comparing sheet 1 and c3 and this one will still be saved into data table sheet dtc2 just for reference you might want to change it if you made a real automation but for now it's good now we have a narrow because the source contains no errors so we cannot copy to a data table with no errors we need to do something so the easiest thing would be to have a try catch a tricast catch is great for error handling and what we want to do here is that we go to activities and then we will have the try catch there you go and drag it in so a try cache which works like this sorry it tries something up here and then if an exception happens we can define what will happen down here in the catches so we try this assign and remember this one was where our exception was thrown then we want to define the catch so go down here to catch all sorts of exceptions you can always use system exception that one will catch everything and then so we catches everything here we just want to do nothing we just want to ignore this activity then that will happen so we'll try it it will not work and nothing will happen but since we need to initialize this dt results then otherwise it is not present we will have a build data table so now we're encountering for whenever we have no matching rows so we build this data table click the data table wizard then delete these things i know this is a bit complicated but try to get your head around it or just rewind the video a bit or ask me a question in the youtube comments below that will also help my channel so up here i'll say dte results i'll build this data table this is just to initialize it and that one is fine it is completely empty but we will if this works if we have any matching columns we will fill it up with the matching rows so let's try to run it now and see if we still have an error drone we will not believe me there you go let's see that we actually work in exercise 10. i have this one here let me go over here there you go we will not have any matching rows as there are no matching rows again if you wanted columns here like have a complete diff empty table you just need like we did before read the range from sheet dt sheet 1 and clear the content then you will have the headers but for now this one is good we did arrow handling and let's just see that it works when we actually have matching rows that is we want to compare dt sheet 1 the sheet 1 and c2 so let me close this one here let me open up uipath because now let's change this back to c2 and see that it actually works as well with this now i run the file and here we're trying to find out which one of the rows that are matching that will be two or three if i remember correctly so go to 10 there you go we have the matching rows now with nice error handling we can also easily change this to find a unique rows in number one and not that those ones are the one that are present in sheet one and not g2 let me close this one here so we just build a new flow because this one isn't new so we take a new sequence i just copy this one we will use it a lot we will actually use it almost exclusively and then i paste it in let's just rename it again i know this is a long session but i hope you enjoy it make sure to subscribe to the channel and stick to the end because this one is still going you will learn a lot so now i will not have the intersect advanced link sheet i will have the accept that is the unique row so i will click update and here we have the 10 and then that one we will open let me close this one down so what i need here let's just still do the arrow handling say that we don't have any unique rows we still want the error handling to sort that one i will write out to number 11 results here that's it and i just need to change one thing here in my assign so click the three dots here and what i need to do is just i will just reverse the statement i will say where and then i'll say let me have the intellisense i'll say not here and have this so now i just reverse the statement now i'll say where those ones that are present in sheet one and not in c2 let me show you so i'll click okay then i'll run the workflow there you go and this one should just be one alpha so let me have the 11 result there we go we have now find the except with link now you should learn about the ui pathway framework it will teach you everything about scaling your robots it's the topic that you will get question in in the uipath advanced certification and it's the topic that most companies will look for when they choose their developers so make sure you go learn that by clicking the video in the middle

Channel: Anders Jensen

Views: 10,907

Keywords: uipath compare excel, uipath compare excel files, uipath compare, uipath compare two datatables, uipath compare two excel files, uipath excel, anders jensen, how to compare excel in uipath, how to compare excell sheets in uipath, comparing excel sheets in uipath, uipath linq excel, excel linq uipath, uipath guide, uipath tutorials, uipath compare data table, uipath compare datatable, uipath compare datatables, compare excel

Id: 8V_DhzPsuAo

Length: 56min 57sec (3417 seconds)

Published: Fri Feb 04 2022