Excel loop failure when extracting subset of rows

Help Studio
, , ,
1

I an trying to iterate a large worksheet containing about 10k rows and several formulas on each row. I set the “View Excel Window” to false in the “Excel Scope” activity I am only updating a few rows so I filtered the worksheet with a “Filter” activity and used the “Read Range” activity to copy about 15 rows to a data table by selecting visible rows only. I defined the range as fc.Sheet(“Sales”).Range(“A:Y”) so as to only get a few columns for visible rows only. But iterating the data table is slow or freezes. I have a “Write Range” activity to write the rows back to the worksheet but it freezes. Any one have any ideas?

I see I can copy the range to another tab where I may try to write VBA or Python to write it back to the original worksheet after performing operations on it.

I only need the three-member primary key and two values and want to update several values.

2

With range “A:Y”, UiPath might returns 10k rows, that’s why the process is slow. If possible, try a more specific range like “A1:Y15”.
Or if the rows with values are not continuous rows, try linq to filter them to an array of drow :slight_smile:

3

@Phil_Kelsey

Two options here for you

  1. Use filter property in read range to read only the filtered data…check the options of read range
  2. Use excel as database and use swl queries this way it would be very fast to read and update…also filtering can be done in sql using where clause

Hope this helps

Cheers

4

Hello @Phil_Kelsey

  1. Disable Excel Formula Calculation:
  • Use an “Excel Application Scope” activity.
  • Set Excel calculation mode to “Manual” to improve performance during data operations.
  1. Read Only the Necessary Range:
  • Utilize the “Excel Read Range” activity.
  • Specify the target sheet name and range (e.g., “Sales” and “A:Y”) to retrieve only the required data.
  1. Split Data into Batches for Processing:
  • Implement a loop, such as a “For Each” activity, to process data in smaller batches if necessary.
  1. Re-enable Excel Formula Calculation:
  • Within the same “Excel Application Scope,” set Excel calculation mode back to “Automatic” after data operations.
  1. Write Back Updated Data to Excel:
  • Employ the “Excel Write Range” activity to save the modified data back to the Excel sheet.

Thanks & Cheers!!!

5

I checked the “visible rows only” only box in the “Read Range” activity. I am not familiar with linq. Can you describe more?

6

I did both. I set up the worksheet as a database and used sql statements and I also tried filters prior to copying the range. Still slow.

7

@Phil_Kelsey

ideally sql should not be slow…but if it is still…better go with running a python script and invoke it from UiPath if possible for you

cheers

8

It appears 1 million blank rows somehow were added to my worksheet. I deleted them and appears to fix things. However, I switched to odbc and sql statements which seem to work well. However, I am trying to use named parameters (@param) in the “Run Query” activity, but I am only able to make ordinal position parameters with “?”. Can anyone confirm if named parameters are not valid for this type of connection?

I asked this question here

9

@Phil_Kelsey

Glad the initial issue is resolved

Can you please close if both are resolved

Cheers