As the title says, I’m contemplating a process in which the data comes from a workbook that has multiple sheets (different data structure/columns) and the steps that should be done in the system will vary depending on the sheet. My goal here is to understand how it would be best to architect a process of this kind.
How would you suggest setting this up? I have mainly thought about these possibilities:
- Handle changing sheets in the GetTransactionData and in Process have an If for choosing which actions the robot should do. The TransactionData variable would hold the Data Tables of each sheet and I would have to reset the counter when changing sheets;
- Use a main project with sequential logic that calls processes for each sheet. The processing of the sheet could be done in a REFramework of its own. I can see this having a little more overhead because of the framework and code spread across multiple projects.
- Another better option that I’m unaware of?
Given your brief description of the process, i would make the sheet name as transactionitem. Naturally, your TransactionData would be a DT of sheet names as rowitems.
inside Process, there would be a switch case for each type of TransactionItem. You would handle reading the data here.
Read and merge all sheets data into one TransactionData, with a column that specifies which sheet.
I would create a process that handles my spreadsheets and consolidates the data into a Queue. Then I would have a very consistent Queue, with consistent data, facilitating the simple development of my second process, which would be responsible for reading the data from the Queue and working with it. But this would only be possible if all spreadsheets are part of the same business context.
So there would be a complex process working with the worksheets and another simple process working with the Queues. I think this gives you better scalability in your process.
I particularly avoid messing with Datatables, they greatly affect the performance of the process, so I always try to work with queues, which give me better control over what is done in each item and also, depending on the need, allows me to reprocess only one item. (How would you reprocess just one item in Excel?)
Think about Queues.
Question 1 : Do the variable\haders will be changed for all the sheets ?
If Yes , Try like this
- In Get Transaction data
- Assign a out argument counter =0
- use get workbook Sheets and store in a out argument sheets
- first time assign the first sheet as sheet(Counter)
- Switch (sheet(Counter))
Assign all the variables in all the cases based on your requirements.
- Assign a variable in main flow and assign both the arguments to respective ones .
- In Process.xaml create in arguments like in_Counter,in_Sheets and assign them with the proper arguments.
- inside the flow follow the same step like use switch and assign all the cases with the respective task with it
Hope this helps!!
I really like this approach as it seems to be “cleaner” and leverages the Dispatcher/Performer structure.
Yes, the sheets hold different data, each having different columns.
One thing that I considered is that when the Transaction Item is a sheet, it feels like we are losing some of the benefits of the REFramework because the Retry mechanism is used for sheets, and the transactions should be the rows of these different sheets.
Not set transaction item as sheets , instead try this
you should not increment the in_Counter value for all the transactions
add conditions like if transaction item is Nothing
- assign counter = Counter+1
Exactly that, a Dispatcher and another Performer. If the Performer is well developed, it will hardly have any maintenance on it, only if it adjusts something in its Queue.
And the Dispatcher is only responsible for manipulating your spreadsheets and can run at different times. Depending on the volume of information you want to work with, and if you have more than one robot for processing, you can put N robots to serve your queue, where each one is responsible for taking an item from the queue.
I did a similar process, and seeing 3 robots working in the same queue and each one on a different item, is something beautiful to see .
I would suggest not using RE Framework at all. IMO it’s bloated and overcomplicated for most automations.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.