Adding new rows to a spreadsheet takes so much, it's unusable in real-life automations

Help Integration Service
,
1

I did a very simple test. I have a “Source.xlsx” file and a “Dest template.xlsx” file, both hosted on O365’s One Drive. The source file has 4 columns and 21 rows, including the header. The destination template has a row (the header).

I then built an automation with Studio Web that would:

  1. Copy “Dest template.xlsx” to “Dest.xlsx” overwriting if found
  2. For each row in the Source.xlsx file
    2.1 Add row in the Dest.xlsx file (Append), filling it with data from the Source Current Row
    2.2 Write line something

What I have then noticed was that adding nothing but 20 rows to a spreadsheet took 2 minutes and 10 seconds. That means 6.5 seconds per row that just has 2 columns. This is huge as we’re only talking about 20 rows.

Think about an actual automation that needs to work with thousands of rows.

Here are my files:

And the log:

  1. 4:02:02 PMexcel test execution started
  2. info4:02:20 PMProcessed #1
  3. info4:02:26 PMProcessed #2
  4. info4:02:33 PMProcessed #3
  5. info4:02:40 PMProcessed #4
  6. info4:02:45 PMProcessed #5
  7. info4:02:52 PMProcessed #6
  8. info4:02:57 PMProcessed #7
  9. info4:03:02 PMProcessed #8
  10. info4:03:07 PMProcessed #9
  11. info4:03:13 PMProcessed #10
  12. info4:03:19 PMProcessed #11
  13. info4:03:25 PMProcessed #12
  14. info4:03:30 PMProcessed #13
  15. info4:03:36 PMProcessed #14
  16. info4:03:41 PMProcessed #15
  17. info4:03:47 PMProcessed #16
  18. info4:03:52 PMProcessed #17
  19. info4:04:04 PMProcessed #18
  20. info4:04:10 PMProcessed #19
  21. info4:04:16 PMProcessed #20
  22. info4:04:16 PMexcel test execution ended
1 Like
3

@bogdan1

You can download the file or do the updates using linq and write

Each row we do only when nothing is common and also getting file to local is not possible

Cheers

4

Tried this using a datatable as an in between vehicle. Works faster.

image
image1380×605 70.9 KB

1 Like
5

The reason is you have the files stored in cloud storage, which will always be slow.