Copy data to excel column cells with filtered range which is not sequential

Help Activities
, ,
1

I have a scenario where need to update data for one of the excel column for filtered rows which are not sequential. Write range activity not working as the range is not sequential. I am currently using a approach like finding the index number of each row and updating the data for each column cell using the index number. This is working fine but consuming lot of time if there are more rows as each column cell need to be updated one after the another after finding the row index and impacting the performance.

Pls let me know if there is any another quicker way to do this.

Note that all the activities need to be performed in same excel and its a macro enabled excel with 90K rows and 65 MB size.

2

Hi @ramesh550.m

When you need to update data for filtered rows in an Excel worksheet where the filtered rows are not sequential, you can optimize the process to improve performance. Here are some suggestions:

  1. Instead of updating each cell individually, consider filtering the data and updating it in batches. This way, you reduce the number of read-write operations.
  2. Excel activities in UiPath can be slow, especially when dealing with a large number of cells. Instead of updating cells one by one, you can use LINQ to manipulate the DataTable (if you load the data into a DataTable) or use VB.NET code to manipulate Excel data directly in memory.
  3. If possible, you can consider using parallel processing to update the data concurrently. Split the data into smaller chunks and process them in parallel using multiple robot processes. Be cautious with this approach to ensure data integrity and avoid conflicts.

Keep in mind that processing a large Excel file with 90K rows and a size of 65 MB can be time-consuming, so optimizing your automation process is crucial. Depending on your specific requirements and constraints, you may need to combine several of these approaches to achieve the best performance for your scenario.

Thanks!!

3

Hi

Updating data for non-sequential rows in a large Excel file can be a challenging task

Im suggested this as u said like

You can use VBA macros or the Execute Macro activity in UiPath to temporarily disable these settings during processing, Instead of reading cell and updating

Cheers @ramesh550.m

4

@ramesh550.m

Welcome to the community

  1. Try to use excel as db and use update activities
  2. Read the data into datatable and try to use linq queries
  3. Vba can be another option

Cheers

5

Thanks for the suggestions Nitya. Updating the data in batches is not an option because not all the filtered rows(I have to perform this action repeatedly for multiple customer names) have the same count. Also splitting the data into smaller chunks not possible as there are some pivot tables build on this data and they get disturbed if we split the data.
I haven’t tried LINQ queries option for this yet. Will try to explore.

6

Thanks for the suggestion Palaniyappan. I am trying macros option as well.

1 Like
7

Thanks for the suggestions Anil. Will try Linq and macros options and see if this can be achieved.

1 Like