Hope everyone is having a blast automating!
I have a dilemma and have seen others struggling with this so I’m looking for some direction in this matter.
Lets say we are receiving multiple emails each containing an excel file with hundreds of rows of data. We check every day for new emails, if any are present, we need to download the excel, process the data and send back an updated excel file to the same email. Simple process, but how would it be best practice involving a dispatcher/performer solution in this sense?
Idea 1: Dispatcher creates a queue item for each new email containing the email address and the path to where the dispatcher downloaded the excel. The performer will take each row from that excel and process it, and send it back to the email address captured in the queue item.
Problem: If processing one row takes 5 minutes, processing 100 rows will clog the robot for a long time and there is higher risk to break the process in the for each datarow loop, which goes against the dispatcher/performer model.
Idea 2: Dispatcher downloads the excel and for each datarow, creates a queue item. The dispatcher processes each transaction item.
Problem: How exactly do we handle sending the complete processed excel data to corresponding email, if we have more than one emails, each with it’s own excel data added as transaction items in the queue.
I know it’s a long read but I appreciate your input because it’s been bugging me for a few days now
or you can also do this (use dictionary to maintain # rows processed for each excel instead of passing row number to queue item)
create string asset in orchestrator / shared folder called jsonString
after processing row
try to convert jsonString to dictionary,
if it fails
create a dictionary
assign dictionary(excel file path) = 1 jsonString
if it succeeds, then
convert this string to dictionary and
check if dictionary contains current excel path as key.
assign dictionary(excel file path) = dictionary(excel file path) + 1
assign dictionary(excel file path) = 1
and write updated dictionary to jsonString and write jsonString to orchestrator asset / txt file in shared drive
Compare dictionary(excel file path) to totalRowsOfDatatable, if equal then it means excel is finished processing and we can send excel back to email
use this to convert dictionary to json string jsonString = NewtonSoft.Json.JsonConvert.SerializeObject(dictionary)
use this to convert json string back to dictionary dictionary = Newtonsoft.Json.JsonConvert.DeserializeObject(Of Dictionary(Of String, Integer))(jsonString)
Thanks for your input, I marked first suggestion as a possible solution and will try to implement it in my next process.
The second one should work too but I was asked to use just activities as much as I can and not scripting. But I like the dictionary path too.
the reason i added a second solution with dictionary was in case the performer doesnt process the rows in the correct order for some reason. e.g. it processes the queue item where rowNumber = last row. But i dont think that will happen