How to report a huge amount of data (33 columns for 1-2k lines) in one Excel file!

Hello UiPath Community!

I have a robot that has to report a big amount of data: it has to report 33 cells for each row. The operative is the following:

  1. Get Input file, which is a .xlsx template with all the items to process and the 33 colums to report. The excel file can have around 2k rows.

  2. Retrieve up to 33 data fields from a website for each item in the input file.

  3. Report all that information back to the input file.

Initially the solution I developed for a max. of 500 rows. So, I developed a Performer that can run in several bots at the same time to get the info, but as it has to report in one huge excel, the last robot to be running will be the one that reports.

To report I used a “workbook write cell” surrounded by a try-catch because some cells may be empty. However, this solution is too slow for the current amount to rows of the input file, and after report around 400 lines, the robot fails because it cannot access to the input file, which is blocked by Excel (I have to manually close a “something went wrong” pop up that appears when I open the file).

I cannot use a DDBB, so it has to be a solution that uses a queue to report (currently implemented) and write in Excel.

Could anybody help me here, please? I have no idea how to report such a huge amount of data in the most efficient way possible.

Thank you all!!

2000 rows isn’t a huge amount of data. What you need to be doing is Read Range into a datatable, manipulate the data in the datatable, then Write Range (or Append Range depending upon needs) back to Excel.

Hello @postwick,

I have to report 33 cells for each 2000 rows, which means to write 66.000 data in one excel file one after another.

What I do is to read the Input File, upload the 2000 items to queue, retrieve the info of each item from an application and upload all the info to the report queue. Then, I have to write the details of each item back in the Input file, for what I use TransactionItem.SpecificContent.

I cannot save all the data in a Datatable because if the process fails, then I lose the inforation of the items I have checked in the application, so I upload the info in a report queue to report after and also, if the report fails to write in excel, I have a control of 5 errors in sequence so it will retry them.

What I need is a formula to write each item of the report queue in the most efficient way.

Thank you very much.

You’re causing your own problem by trying to write them one by one. That’s not a good way to do it. Looping through 2000 and writing each one individually to Excel is inefficient. The Write Range is an all-at-once write to Excel. If it fails (which is unlikely) nothing gets written so there’s no issue with knowing how far it got.

Hello,

I think I am not explaining myself or I don’t see your solution clearly within my requirements.

I know that writing one by one is not efficient, but I cannot build a datatable with all the information because it is a very important process to business and I cannot risk the process to fail. If that happens I need a backup or a way of building it again with the same info.

Kind regards,

Laura.

Reading into a datatable, manipulating the data in the datatable to get it how you want, then writing to Excel all at once with Write Range - this is the most reliable way to do it to avoid the issues you are mentioning. Again, if something fails, NOTHING gets written to the final Excel file, so you just fix the problem and run it again.

Thank you for your input @postwick, but if something fails then I would have to manually clone the items in the queue.

I am aware that manipulating datatable would be the most efficient way to perform what I need but I need a solution that takes into account that the information is retrieved from a queue and if the process fails I won’t colone 1000 items to run the robot again.

Something else comes to anyone’s mind?

My solution does not use a queue. You don’t need one. Directly read the spreadsheet in your automation, into a datatable.

Read Range into datatable.

Loop through datatable, go to the web site and retrieve the additional data and add it to the datatable.

Write Range (or Append Range) into a new spreadsheet.

If you insist on doing it from a queue…

Get item from queue

Convert to a row in a datatable (this datatable will have only this row)

Get data from web site and add to datatable

Append Range the datatable (with just one row) to the destination Excel

Empty datatable

Repeat