How to delete a row in excel using invoke method

Hello,

I am trying to read a 89 MB excel file with 455302 rows.

if I use the read range (a system activity) it UiPath frozen and stop working, but if I use excel application scope anad a read column read column activity works great, but the problem is that I would like to delete the file that contains “X” parameter in the many rows, I tried to use method delete, by using invoke method, but I can’t tell the method to delete a row from a IEnumerable object data type. I know the row number but I can’t delete it.

Any ideas, dear friends?

Either you read a column or an entire range it is recommended to use Excel Application Scope.

Reading very large Excel files has a nice solution: do it in chunks of 10000 rows. Read A1:C10000, then A10001:C:20000 and so on until you get less than 10.000 rows in the resulting datatable.

I don’t quite get what are you trying to do with the file. Is that a file on disk, an Excel file, some rows in Excel…

2 Likes

Thank you for your reply Badita ImI>,

I have this file with 37 columns and a lot of rows, and I’m trying to delete everything that has today’s date.
I know how to run a “for each” activity but like I said UiPath frozen every time I tried to read the file due to the quantity of lines I guess, the thing is that after UiPath finds toda’s date i need to delete the row from that file but I dont want to build a data table and then append the data into a csv, I want to work on the excel (.xlsx) file saved on my desktop directly. That’s why I´m usin invoke method and on the properties tab under parameters,I am assigning a variable with the row number but is not letting me. It’s saying that changed the value and it cannot be removed.

The option to divide it into sections works great, I just doing a for each and adding 1,000 to a variable and sum it to a variable.

Let’s suppose that reading in batches has been solved.

Now we need to remove the rows that falls under a specific condition. There are two remarks here:

  1. There’s no need to use Invoke Method as there is a dedicated activity for this Remove Data Row.
    As a parenthesis, if you want to use Invoke Method for removing rows the target object should be dt.Rows and not dt.Columns

  2. You need to iterate through your Rows collection but you can NOT use a For Each Loop. The reason is that you will get an error saying that the collection you iterate through can not be modified within the iterator.

The solution is to use a While block:

i = dt.Rows.Count -1
While (i>=0)
{
if (condition) RemoveDataRow (dt.Rows(i))
i = i-1
}

You need to write/append your resulting dt in a new Excel File. Changes in the datatable are not propagating in the Excel file.

Hope this helps.

3 Likes

Is there any activity that could be built based on badita’s answer? Then we could delete whilst iterating through the datatable? People have different ways of doing this - my method is to use dt.Select to grab the rows I don’t want into an array of Data Rows and then delete them in a separate for each item in arrDataRows as you are no longer iterating through the datatable. However, other people do different things like a for each within a for each and then Break which is also a bit ugly and I can see that you guys have two other ways of doing this!!

Just something that is very common which I believe should have a neater solution :slight_smile:

Actitvity would be called DeleteRows - pass in datatable and condition…pass out success value and/or number of rows deleted/?

Richard

1 Like

Hi Badita,

Based on your While block example, how do I refer to the particular column’s data of the corresponding data row for the condition check, prior to decide using RemoveDataRow if the condition is true?

Thanks,

Tew

This post might help you on this:

Regards…!!
Aksh

2 Likes

@badita I have a same problem while reading 100,000 rows in a excel sheet . So , i tried doing it in chunks but issue is header of the sheet , i have 100 columns in my sheet .

So , if i read 10000 rows first and store it in a datatable(Dt1) then i run do while loop to read more rows until rows less than 10000 then i need to merge those rows with dt1 but merge datatable will not work without headers
So , can you tell you how can i merge those chunks(10000rows) together without using build datatable and adding row by row by ofcourse.

Please eloborate your solution above . How to get completed 100,000 rows in a datatable .
Thanks
Ankit