Consolidate Multiple Excel Files data into single excel file

 RPA | Consolidate multiple Excel files data into single excel file in Automation Anywhere| Automation Anywhere- Part-55

Consolidate multiple Excel file Data in Automation Anywhere - In this blog we will discuss how to merge and consolidate multiple excel file data into one single excel file using A2019 Automation Anywhere also known as Automation 360.

Below are the steps on how can we merge or consolidate multiple excel files data into single excel file using A2019 Automation Anywhere or Automation 360 as follows:-

• Drag and drop loop action from the loop package. In the properties section select for each file in folder as the iterator then pass in the folder path from which you want to loop through the files and assign the output to a dictionary variable type.
• From the excel advanced package drag and drop the open action to open the excel file. In the properties section pass the file path and the file name which we want to open from the folder, check the sheet contains a header checkbox and lastly provide the session name which we will use it further in our excel actions that we would be using.
• Use the get multiple cells action from the excel advanced package to retrieve excel cell values. In the properties section by default all rows option is selected we will be keeping that option as it is, pass in the session name and assign the output to a table variable data type.
• Now we will be again using the loop action to iterate through each rows in the table so in the properties section select for each row in table as the iterator, select the table variable created in the previous step and assign the output to a record variable data type.
• Now using the string assign action store the data to a string variable data type for the columns which we want to store.
• Then using the add item to list  action from the list package add the items from data table to list variable.
• Close the excel file using the close action from the excel advanced package.
• Use the loop package select the for each item in the list as the iterator, select the list variable and assign the output to a string variable of string data type.
• Open the excel file and in the sheet2 we will be writing the data to this sheet in the excel file.
• Use the set cell action from the excel advanced package set the data from the list to column A.
• Repeat steps 8-10 to loop through all the items in the list and set the data in column B.

Now we will see the code snippet for the steps that we have discussed above as shown below in the screenshot.

So with that we have come to the end of our blog where we discussed how we can store the data from an excel file to a list variable and then write the data to the excel file using A2019 Automation Anywhere.

If you have any questions regarding this article or blog then you can raise your queries in the comment section and I will be more than happy to answer all of your queries.