I am working on a process that processes one excel file with multiple sheets. Some calculations are done for each of the sheets and some data is extracted, inputted into another application and some data is inputted back into the sheets. There is different logic for different sheets (for example sheet1, sheet2 and sheet5 have the same logic, sheet3, sheet4 and sheet10 have the same logic etc.)
So here are the requirements that are a must:
I must use REFramework
I’m not allowed to use Orchestrator Queues
I can think of many ways to do this, but I was wondering what would be best according to UiPath best practices. I am particularly confused about what to use as my TransactionItem. Here are some of the options that I thought of:
TransactionItem could be the name of each sheet. I can do logic where in Process I will check what group the current sheet falls under and execute logic for it accordingly. However, I’m assuming in this case, the excel file would need to constantly be opened and closed for each of the sheets?
Another idea is to completely abandon the concept of TransactionItems, use the Initialization and the final states of REF, but in process I could open the excel file and do a “for each sheet” activity.
Can you think of a better way to do this? I tried to research for similar use-cases but couldn’t find anything, but if you come across similar forum posts, feel free to link me those.
If the operations that you perform with the excel sheets are only within the memory, say Update row values, Filter row values , then we could do it by collecting all the sheets data as Dictionary of Datatables. Here, Sheet name can be the key and it’s data as it’s corresponding value.
The Transaction Item could be the sheet name and Dictionary could be used to access each of the sheet values.
Then after all the operations/manipulation is done, you could write the data back to the Excel sheet at the end. This way you would not need to open/close Excel sheets many times.
However, this needs to be properly analyzed whether there can be any memory usage problems and whether or not the Excel Scope is mandatory to be used for your required output.
Let us know your thoughts on this and what might possibly be the challenges faced.
Unfortunately there is a lot of data in each of the sheets and it is VERY unstructured (not in a table but rather in random cells), so I don’t think that would work.
In Init State, In First Run, Get all the names of the Excel Sheets.
Now going towards the transaction item, we will be considering each sheet name as a transaction.
In the Process workflow, you’ll read the excel sheet by giving the sheet name and here you’ll define whatever logic you need. And also your entire working on one sheet will be here. i.e. For each row etc everything will be here.
What I think I will do, is I’ll have a dictionary that has key:value = sheetname : template how to process it
And I’ll do the rest how you suggested.
I will mark your answer as solution, but I have one more dilemma to consult you on. Where do I keep the list of what “template” each sheet belongs to (what logic needs to be done for it)
Is it bad practices to add another sheet to my config file “Mapping” where I map what template each sheet belongs to? Can you think of a better approach?
@M_Kr You can keep the logic information in a separate sheet in Config file, in fact it is a good practice.
The purpose of config file is specifically to hold this kind of information and depending upon the information, we can add/remove sheets in the config as we want…it’s totally up to us.