UiPath | How to Merge multiple Excel sheets and write to a new sheet or Excel | What and How Series

hi everyone my name is Mukesh kala welcome back to my YouTube channel and today we are going to see the next video in the word and the how series of uipath in the previous video of the Excel playlist I have shown you how you can divide a large Excel into a small small set of Excel and that we have done with the help of CSV that how can you split the CSV into multiple different Excel sheet now I was getting a continuous comment on that video over my email over my Instagram that okay this is done but how about merging a data so let's say I have an Excel which is having multiple differentiate sheet 1 sheet 2 sheet 3 and I want to merge all of them together into a single Consolidated Excel how do we do that right so that is what we are going to see in this video in case you are coming to the channel for the first time and you haven't already subscribed please consider subscribing to the channel to get the latest video in the what and the how series of uipath also in case if you have a similar kind of question which we want me to cover in this series feel free to drop me an email or you can also write your query in the comments as well having said that let's get started [Music] for the people who are looking this series for the first time in the series I usually answer the question which I receive over my WhatsApp over my email Instagram or over the YouTube comments prior to this video we have already covered these videos in the series in case you want any of this feel free to watch the videos and that should give you some insights so today I am going to talk about the merging of the Excel talking of the problem statement this I have an Excel the Excel name is new account data as you can see I have different Excel sheet here's home office corporate consumer and all of them has some data right but the good part is the home office has only the data for home office the corporate has data for corporate the consumer has data for Consumer right our objective is to consolidate all these three sheet into a new sheet let's call it final but the challenge here is today it is home office corporate consumer tomorrow it can be anything tomorrow it can be Insurance banking Finance but our end objective should be same the other challenge is we are not sure what would be the name of the sheet someday it might be home office corporate consumer or someday it can be sheet1 sheet to sheet 3 right so that is the problem statement and depending on how many sheets I have I have to consolidate all the data and put it into a new Excel sheet which is called final sheet or anything right whichever you want to call so this would be the sheet where we want to consolidate all the data but we are not sure of the name and this could be multiple sheets right so that is the problem statement we want to address today okay so I will go here and I'll delete this data I'll save this Excel this Excel is located in my D colon data this is the Excel new account data which I have kept okay so now let's straight away go to your airport studio and I will go here and I'll create a new process I'll call this process as Excel merger good practice always put the description and I will say create okay we'll just wait for the process to fire up and then we will start the coding okay so studio is ready I am in my main Dot xaml and here we are going to write the automation okay since I am dealing with Excel the first thing first I'll go to the project and we'll check whether the dependencies are already here so as you can see I have already got the uipart dot Excel activities that is the first thing because if you don't have this dependency here you won't get the activities here okay so now what is the first thing the first thing is to read this Excel now to read this Excel we can use the Excel application scope or we can use the read range right I already have Excel installed so I will prefer to use the Excel application scope drag and drop it here and here it is okay now Excel application scope is here we want to point it to this Excel so I can click on this file button browse for file it was available in my D colon data and new account data and I'll say open okay this path is hard coded guys but whenever you are doing a real-time automation please make sure that you do not hard code anything all of the things would be kept in a config file or an assets right we have already discussed so not in scope of this video so but the thing is we should not hard code okay so we have read the Excel but now this Excel can have multiple sheets 1 2 3 4 5 6 we don't know right now when this Excel application scope is reading this Excel file we need to read the data from the sheet to consolidate but I don't know how many seats are there so the first action for me is to get how many sheets you have in the Excel right because today it is three tomorrow it can be four five six seven any number right so first I need to know how many sheets are there and then we will write a logic to merge it okay so to do that I'll go to the activities I'll take an activity which is called assign drag and drop this activity here okay and here I am going to write an expression to know all the sheets which this Excel has now to get all the name of the sheets you can simply click on this Excel application scope go to the properties and you would have an output which is called workbook go here and create a new variable so I'll hit Ctrl k and I have created an object which is called WB underscore Excel now what is this workbook this is an output argument of the Excel application scope which will get me all these data in of the excel in this variable which is called WB underscore Excel if you go to the variable you would notice that uipath has automatically created a variable of type workbook application for you now with using this workbook application you could get all the sheets from the Excel how do you get it I'll open this assign in here we can simply use the variable which we have created which is WB Excel which is a workbook application type object and I can write something called get sheets and I'll save this now workbook object dot get sheets will give you all the name of the sheets but the question arises where should I store it so I'll go here I'll hit Ctrl plus k and I'll create a variable called Sheet's name okay so by default uipath will automatically create a variable for me which is sheet name but I am getting an error what does this error say if you see here it says that Mukesh I am not able to assign The Collection into a string so if you understand this error it is trying to say me I am reading all these names of the sheet home office corporate consumer and you are storing all these names in a string variable which I am not able to do because I am giving you a collection I am giving you a list of string names or I am giving an array it is telling me that these are three items one two three four five six and you have a single string variable and I won't be able to accommodate that so working as a developer try to understand the error to fix this we'll go here and we will change it to array of string where I am telling that no I am not having a single string but I am expecting a array of string and then I'll say okay still I have the error right so this is an array of string so I'll go here I'll reopen the expression like this and to convert this to array you can simply use something which is called to array and I say save and that way the error is gone okay so now what would happen whenever we run this automation we should get all the name of the sheets which are there in the Excel irrespective of the number of sheets whether it is one two three four five six ten we should get the sheets name right let's try to see that in action right so what I will do is I'll quickly go here and put a right line okay I'll put a break point here because I want you guys to understand it step by step that's why I am running this in debug mode and we'll just go here and I'll say debug the file okay I am using Excel application scope so I have a privilege of keeping this Excel open when I am running the automation in case you are new to excel automation we have a dedicated playlist where we have explained all the small small things about Excel Automation in details the link is available in the description okay now if you notice here I have triggered the automation the automation started and in the sheets name here is the variable if you see here I open the sheet name you can see that there are three items I can click on this pencil icon and you would notice that the string array has now got home office corporate consumer so tomorrow if I have 30 different Excel sheets I will get all of them in the array okay so I hope till this point it is clear that I have got all the name of the sheets now what is the next thing the next thing is we have to read all the sheets one by one and to read the sheets we use an activity which is called P range so I'll go here and I'll select the read range I am using the Excel integration so I'll use this Excel read range and I can drop it here Now read range is asking me Mukesh tell me the name of the sheet where is the sheet so if I go here this is the sheet name home office corporate and consumer I cannot hard code it right but where are the sheet names the sheet name are inside this array right string array so we have to pass all the sheet names one by one to read range and whenever we have to do anything one by one in repetition we are going to use something which is called Loops okay so now to get all these sheet name one by one and pass it to the read range I'll go to the activities and I'll take an activity called for each drag and drop it here and forage is asking me will minimize this minimize this as well okay forage is asking me Mukesh tell me where is your list where is your collection so where is our collection our collection is in the sheets name I can just pass here sheets name that's it now many of you had a question and I was getting this question already that where is the option to let this activity understand that this is a string this is an integer that was in the older version with the latest version of uipath Studio as soon as you pass the collection name here which is sheet name uipath understand that yes the sheet name is a collection of string so this current item would have the sheet name as string so no need to change the data type okay as soon as I use this activity what would happen uipath will give me sheets name one by one in this variable which is called current item okay so to better visualize this we can change this variable name to something like individual sheet name right so that you can understand you can always use the current item also that's not a problem now what we want to do this individual sheet name would be having first sheet name then the second then the third one by one and this read range will now be inside the body as simple as that right so get all the sheet name Loop through all the sheets name one by one you will get the individual sheet name and now this sheet one would be replaced by the individual sheet name like this that's it right so I'm instructing you about that whatever sheet name you get just read the data okay so for better understanding what I'll do is it's always advisable to have a log message activity that log message activity help you to understand what the automation is doing both in the background as well as at the time of debugging okay so if I go here in the log level I would say info and here I would say reading the sheet I'll correct the spelling reading the sheet individual sheet name so what will happen when I run this automation I will get the log reading the sheet this and then I'm instructing UI part to read the sheet whatever log message you print here would be visible for you in the orchestrator for those of you who want to understand more about the orchestrator and rpt beginner I just want to inform you that I have a dedicated course on orchestrator which is live on graphy and as of now we are also offering a discount coupon on the course you can check the description for further details okay so I have added a log message and here I am instructing UI part to read the data from this sheet now whenever we read the data from the Excel now here it is asking me do your data have headers yes my data has headers but am I really interested to get the headers that again depends on your implementation for me I simply want to get the data okay so what I'll do is in the read range here in the range I'll tell you to read it from A2 okay so if you notice in the Excel all the data is starting from A2 here also it is starting from A2 in the consumer also starting from A2 so I'm instructing uipath to read the data from Individual sheets from the range A2 and there is no header okay now it is asking me okay I'll go to this individual sheet I'll read the data from A2 I will not add Addle where should I store it so I say go here and store this in a data table now this I am calling as DT underscore in DV data so what would happen uipath will automatically create a variable which is called DT underscore individual data and which is of kind data table right so first time the automation will go it will read the first sheet which is A2 which is home office it will read all these data and it will store it in a data table now what do I want to do my final objective is to merge the data now there can be two approaches now you have read the data you can create a new sheet and keep writing the data one by one or the other good way would be first consolidate all the data and then write in a single go okay so there are two ways again I am repeating as of now we have read this data I have got this in a data table I can simply use the right range activity and keep appending the data directly in the Excel the other way would be first append all the data and then write in a single go so we are going by the second way where we are telling it to read the data and then we'll paste it only once okay so for that I am going to the variable and I will create here uh new data table I will call it at DT underscore final right DT underscore finally means that yes this is my final data table okay now once the data is read from the first sheet which is this one home office I will simply go here and I will use an activity which is called merge data table drag and drop it here it is asking me what is the source where do you want to merge the data see the data table object to be added what is the source so the source would be whatever you have read it from this one so I'll go here and I say that merge the individual data into the final data okay that's it and once the data is in the final sheet then we want to write it right but before directly writing I want to check it right whether it's working fine or not so I'll go here and I'll put a log message activity okay and here I would say info level log and I would like to print the count of the DT final so I would say that final count is DT final Dot rows dot count dot to string okay so I'll just open it for you guys so that you understand okay I am telling uipath that once we have merged all the data into the DT final give me the count that how many records you have got so that when I am running I can see it in the output panel right whether the merging was successful or not okay as of now we are not writing the data we are just trying to see whether merging is happening or not okay so I'll put a breakpoint on ah let us say this activity here forage okay we'll put a breakpoint and let's try to understand it in a step-by-step manner I'll click on this button which says debug the file okay so the automation started we have got the three sheet name I'll click on step into okay so the first log message guys reading the sheet which is called Home Office okay so that way we understand that yes now the robot is trying to read the data from home office it has got 47 records including the header okay so I'll read this data read was successful and then we have to merge this individual data into the final so as of now if you see here the final is null I'll do a step into and I got an exception object reference not set to an instance of an object very common exception lot of you are getting it and that's why I have intentionally kept this exception here so that I can make you understand that how do you fix this okay now what is happening here if you notice here we are trying to put the individual data into the final but have we initialized the variable the answer is no we have just created the variable we have not initialized it so that's why this DT final is just a variable it is not initialized and uipath is telling me that you are trying to assign a value to something which is not initialized it does not exist so how do you initialize I'll stop this I'll go to the variable DT final right which is this variable in the default value I will simply say new this is a data table so I would write system dot data Dot data table and now this variable is initialized now if I run this I should not get that error okay I'll put a breakpoint here and I'll just remove this one because this is working debug the file started reading the data from the home office we have read this data merge the data of individual into the final do a step into and you would notice that error was gone after we initialize and what I have done was I have made the count so I'll go and click on this button and you would notice that the final count is Now 46 because the first row was the header and now this is in for it so it is going to the second sheet so let me click on step into now it says reading the sheet corporate notice here I have nowhere put the sheet name right it would read the second sheet and then we are merging it to the final so the final had a previous count of 46 now let me do a step into and let's try to see the count now and count is now 73. let's see here quickly in the home office I have 46 and here I had 74. so what has happened right if you see here in the home office I had a count of 47 and here I have a count of 40 in the corporate sorry I had a count of 74. ideally 46 plus think it should be somewhere close to 100 but what has happened some of you might have guessed it it has overwritten the data the final count is 73 which means that it did not merge the data however it overrided the data and that is happening because of the scope of the variables okay let me explain I will stop this okay now try to understand the scope of the variables okay that would help you whenever you are writing a big automation okay so this is our Excel application scope where all the Excel is being read okay in the forage we are reading all the excels now if you see the variables the here in the body okay let me click on this body I am inside the body of the forage where all these variables are getting initialized so what is happening individual data I am getting it inside this body and I am merging the data into the final whose scope is only till this body but when it again go for the next item what is happening this variable is getting reset it is not able to hold the variable because it is scope is only till body so in case I want to retain the data I would have to increase the scope of this variable so as of now the final data is only having a scope of this body as soon as the automation leave this block which is the log message creates the blank data table and you lose everything so that's why in case I want to retain it I would have to increase the scope of the final outside this body right because that is something where I keep on accommodating simply go here and increase this to the main sequence okay so that means that what would happen I will just minimize this in your here in the main sequence which is this main guy here you have the complete data table now whatever happened inside this body that will keep merging the data but it will not impact the actual data table let's see that in action I'll save this so what I have done just to show you guys in the variables I have increased the scope of the DT final to the main sequence okay I'll save this and I'll say debug the file okay now the automation will again start and reading the home sheet done merge the data into the DT final merging done let's print the log message right final count is now 46. done now the next Loop reading the sheet corporate and now it should print and now merging the data into the DT final okay now if you see at the top here in the DT final it has already got the data from the previous one home office right so let's say merge the data merging is successful now if I print the count the count is 119 so 46 was the previous one of the final the new count is 119 right so if you see here first it was 73 and sorry first for the home office it was 46 and for the corporate it would be 73 total it's now 119. I'll do a step into and next it will read the sheet consumer it will read the consumer data and then it will merge it into the final final has already a count of 119 Now consumer has how many record let me quickly validate so consumer has 122 records right so that should get added to 119 merge the data and then 241 right so the final count which I am getting is 241 it's not overwriting okay so I'll do a step into and since I only had three sheets the automation is completed right now if you notice the output that is the benefit of using the log messages okay now all we have to do is just now write the data to the new sheet now to write the data back to the Excel you can have two options one option is to write the data into this same Excel with a new sheet name or let's say I want to write it in a completely new Excel right let's see both of them so first I want to add the data into here only in this this Excel so I want to write the data into the same Excel so I will use this same Excel application scope I do not have to write it in the forage because otherwise it will keep running in the loop I want to write it once everything is done here I'll just go to the activities and use an activity called right range Excel drag and drop it here like this okay now it is asking me where do you want to write the data so I say that I want to write the data into the final sheet what is the cell A1 what is the data table DT final I'll save this okay and here now when I run this automation I should get a new sheet which is called final okay so I'll save this I'll go here and I would say run the file okay we should be able to validate all the numbers in the logs so this time I'm not running in debug mode because we have already seen that let's see if it is able to create the new sheet or not automation completed let's go to the output it completed in three seconds right so final count should be 241 and you can see here we have got this sheet which is called the final sheet I can move it here and the count is 241 right you can see here I have the data for home office then the corporate and then the consumer okay let's validate the last record it is Carlos in the consumer the last record is Carlos okay so that means that my automation is working successfully right I was able to merge the data of all the three sheets okay let me delete this guy from here okay and now let's go to the second scenario where I say that I want to write this data into a new excel at this location which is this location D colon data here I want to create a new Final Excel okay I'll go back to UI path and this right range I'll delete Excel application scope everything done I'll go to the activities and I can still use the right range but I want to just write so I can use this workbook as well right so I'll take a workbook right range it is asking me where do you want to create your workbook so I'll pass the path here decolon data and final dot xlsx it should be the complete name what do you want to call the sheet where do you want to put the data so I say that my sheet name is final underscore new just to differentiate okay what is the data table DT final okay if you remove the if you leave this cell blank it will automatically take the first cell okay I'll save this I'll go here and I'll say run the file right so this time this same we are consolidating and putting it into a new Excel sheet and this would be in the same location the robot has started completed let's go back to the output right the number won't change 241 three seconds here as you can see I'll hit a quick refresh final is created double click to open and the same data of 241 records is available here our last guy was Carlos which is here that means that we are successfully able to consolidate the data okay so that's how you can merge the data into the Excel sheets and keep it into a same Excel or you can put it into a different Excel sheet as well okay so that is all for this video I would like to wrap this video here I hope this was insightful if you have any more questions feel free to drop me in the comments or you can also write me an email as well I'll appreciate your feedback on this video and and with this we would wrap this video here thank you for watching if you like this video please do subscribe to the channel and happy automation okay so that is all for this video I would like to wrap this video here I hope this was insightful if you have any more questions any more doubts feel free to write me in the comments or you can also drop me an email as well I would appreciate your feedback on the video and tell me in the comments what would be the next topic or next video you want to see so with that I would wrap this video here thank you for watching if you like this video please do subscribe to the channel and happy automation [Music]

Channel: Tutorials by Mukesh Kala

Views: 3,447

Keywords: UiPath, UiPathCommunity, RPA, LearnRPA, Tutorials By Mukesh Kala, automation, tutorials, how to, uipath tutorials, uipath beginner, robotic process automation, UiPathRPA, uipath rpa, UiPath Videos, Rpa tutorial, Robotic Process Automation, UiPath basic, UiPath Advance tutorial, uipath training, UiPath examples, uipath video, RPA Training, UiPath Mukesh Kala, rename file uipath, uipath tutorial, uipath tutorial for beginners, rpa tutorial for beginners

Id: sJqRXmPRTKw

Length: 35min 22sec (2122 seconds)

Published: Thu Sep 14 2023