Remove Excel Rows That Contain Keywords

Help
1

Hey Community,

i am new to UiPath and have a short question on how to delete certain rows in my Excel sheet.
I basically want to go through a certain process which is repetitive and therefore requires a loop.

I read out an excel file go through the process and want UiPath to write “next” in a certain cell of my excel workbook. So far so good. Now i want to create the loop and let UiPath check in which row “next” appears and delete that particular (complete) row.

So my question is how do i remove excel rows that contain keywords like “next” ?
I found this very promising article but if i download the files the program yields an error without performing any task.

Best regards :slight_smile:

2

@maurpalmenice Check attached zip file having excel file rows will be deleted based on keyword

remove rows based on condition.zip (8.2 KB)

3

Hi.

One method I use a lot is with vb.net lambda inside an Assign activity

dt1 = dt1.AsEnumerable.Where(Function(row) Not row("column1").ToString.Contains("keyword") ).CopyToDataTable

So it essentially takes all rows where “keyword” is NOT contained in “column1”, and you can add many other conditions using Or or And. Then, it copies those rows to the data table variable you are assigning it to, removing the rows with “keyword”.

This method is instantaneous and requires no loops or if activities which sometimes clutter your process.

Regards.

1 Like
4

Hey indra,

thank you for your fast reply. But the program is not deleting existing rows. It is just exporting all other rows which don’t have the key and in return can be put into a new excel file, right? I was wondering if its possible to delete rows from my current worksheet permanently? :slight_smile:

regards!

5

https://activities.uipath.com/lang-ja/docs/filter-data-table

6

Hey Indra,
I still have the question, as nothing is happening and the workflow still doesn’t work.
I attached the file and hope you can open it.

My aim is basically to read the excel file, build a data table. Identify in which row the word “next” appears and delete that particular row completely. Also export a new excel so we can continue with using it with the following tasks.

Best Regards
Maurice
Main.xaml (11.1 KB)

7

Read the excel sheet into Datatable using ReadRange Actvity and then download BalaReva.DataTable.Activities package and use Remove Data Row Select, and give the condition column name=“next”

1 Like
8

Hi ClaytonM, if the contains function result in a no row databale, there is a error on the copyToDataTable activity stating that can’t copy empty datatable. How to check if is empty before copying? Thanks!

9

you can do something like below:

If dt1.AsEnumerable.Where(Function(row) Not row("column1").ToString.Contains("keyword") ).Count > 0 then
dt1 = dt1.AsEnumerable.Where(Function(row) Not row("column1").ToString.Contains("keyword") ).CopyToDataTable
else
dt1  = new datatable
end if
1 Like
10

Thanks a lot!!

11

I suggest assigning the filter code to an array of datarows. Then, you can do .Count>0 to check if it found rows.

This way, you are not duplicating code in multiple places. :smiley: