What is the best way to use REFramework when each transaction item to be processed is an Excel sheet?

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.

Thank you in advance!


Hi @M_Kr ,

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.

1 Like

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.

Thank you for this idea though!

1 Like

Hey Maya @M_Kr ,

I would suggest, do it this way:

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.

And so on…

In case of any confusion, please do let me know!


1 Like

Thank you for your ideas.

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.

1 Like

Another approach for the sheet logic could be placing switch activity in Process workflow.

And here depending upon each sheet name, you can define your logic…

Like this:

case Sheet1
Logic abc

case Sheet2
Logic xyz

This way there will be no need to use config and dictionary for this.

1 Like

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