Append DataTable data from multiple Excel Sheets in one workbook

I have one workbook with multiple sheets named with date and year. Starts with the current month and current year, and it goes back to January of the year. For example, it is June 2019 now, so the workbook contains the following sheets in this order:
June 2019
May 2019
April 2019
March 2019
February 2019
January 2019

Each sheet contains lots of sales data. Now, I need to search for a certain sales data row from all these sheets. Initially, I thought about searching the data only from the current month, and if not found, go back to the previous month sheet, and then if still not found, go back to the previous month, until it is found, or not found at all. Like retrieve DataTable from each sheet using Read Range activity, each time the data is not found in a specific month.Let’s call it Method A.
(The reason I thought about doing this at first, was because the data we search tend to be found in closer months to the current month. For example, the data we search NOW can only be found in June 2019 sheet, and if not, it is more likely to be found in May 2019 sheet, rather than January 2019 for example as it is too far in the past.)

However, I realized it might be easier if I just keep reading all data from each sheet, and append all data back to back, creating one big DataTable. Let’s call it Method B. This way, I don’t have to worry about “having to go back to the previous month if the data is not found”. Like, I would only need one search within one DataTable, whereas Method A would require multiple searches if the data is not found in a certain month.

My questions are:

  1. In situation like mine, do you think Method B is an easier and better idea? I just thought the logic would be much simpler and there would be less activities with Method B. Do you think this is a good idea? I am very new to UiPath and I want your opinion.

  2. How can I read from each sheet until it hits January of the year, appending each DataTable underneath it each time? Like
    [ Block of June 2019 data]
    [ Block of May 2019 data]
    [ Block of April 2019 data]

and so on, into one big datatable. I thought about using Append Range activity, but this looks like it will write into an actual excel file, which I don’t need. I only need to use DataTable consisting of all months’ data. Can anyone give me some advice?

1 Like

Hi @tomato25
Thats was really a very big one to type, kudos to your patience on writing this question
Fine, coming to the point

Yes, of course this is a good idea when compared to the Method A, as the logic and activity involved is very less comparatively. The reason is method A lot of nested if condition which is not a good practice of programming or coding in general
so i would also prefer method B

Then to accomplish this, kindly follow the below steps that could help you resolve this, and you know something, you have already made halfway done, anyhow this might add some insights rather
–use a excel application scoep and pass the input as file path of excel, and we can get the output of from this activity of type workbook and name it out_workbook
–use a assign activity like this with a variable named out_sheet_array which is a variable of type string array
out_sheet_array = out_workbook.GetSheets
–use a for each loop and pass the above variable out_sheet_array as input and change the type argument as string in for each loop activity
–inside this for each loop use a read range activity and mention the sheet name as item the variable that we obtain from for each loop and get output as out_dt
–use a build datatable activity and create a table of similar structure to that of in excel and name it in_dt
–next use a clear datatable activity and pass this in_dt as input to it
–next use a merge datatable activity and mention the source as out_dt and destination as in_dt
–finally we will be getting a datatable in_dt with all the records in all the sheets
–now you can use any activity to filter it or sort it out further with this datatable in_dt

Hope this would help you
kindly try this and let know for any queries or clarification
Cheers @tomato25

1 Like

Thank you for the quick reply.

Do I need to do all that within the Excel Application Scope activity?
I am not sure where I should put all these acitivites.

1 Like

yes you are right
Cheers @tomato25

1 Like

I am actually getting an error here. Inside Excel Application Scope, I gave it the full path to the workbook. I added resultWorkbook variable to the Output property of the Excel Application Scope.
Inside it, I have added Assign activity where I put

sheetArray (of String Array) = resultWorkbook.GetSheets

But this is returning Option strict on disallows implicit conversions from System.Collections.Generic.IEnumerable(Of string) to 1-dimensional array of String

Fine just now made the changes
create a variable of this type
System.Collections.Generic.IEnumerable(Of string)
Cheers @tomato25

Also, my apologies but I forgot to mention something.
This workbook contains some other irrelevant sheets as well. Instead of just “month year” sheets, there are “General Sales Data”, “Others”, etc. Like sheet names that are totally different from “month year”. Also we don’t know where these sheets are located.
So we want to make sure we will only read only necessary sheets in the right order. Does your method still work?

Fine
use a if condition like this, before read range activity
item.ToString.Contains(“2019”)
which would fetch only the year named sheets
if this condition gets passed it will go to THEN part where we can keep the read range activity
Cheers @tomato25

1 Like

Hmm, again I am so sorry to mention this, but some months contain different column names, and some months contain some columns that other months don’t have.
To check a data from the sheets, we will only need to consider 6-7 columns out of 30+ columns existing in each sheet.

Is there any way to go around this? I am so sorry I mentioned this now :frowning:

1 Like

Fine no worries @tomato25
–use excel application scope and pass the input as file path and get the out as workbook with a variable named out_workbook
–use a assign Activity within this Excel application scope and mention like this
Out_sheet_list = out_workbook.GetSheets
–here comes the answer for your query create use Build datatable Activity and create the columns you need…get the output as finaldt
–then use a for each loop and pass the input as out_sheet_list and change the type argument as string
–inside the for each loop use a read range activity and mention the sheet name as item and get the output as outdt
–use a for each row loop next to this read range activity while still being inside the first for each loop, and pass the input as outdt
–inside the for each row loop now use a add datarow Activity and mention the input as finaldt in datatable property and in array row property mention only the columnname you want like this
{row(“YourColumnName 1”).ToString,row(“YourColumnName 2”).ToString,…}
–so this will add only the column value you want rather to than everyone…
Now you can use this finaldt datatable as you want…hope this would help you
Cheers @tomato25 …

2 Likes

Were you able to get now buddy @tomato25

1 Like

Thank you! I think I got it to work! You have always helped me when I have questions. I really appreciate your help, man!

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.