How to Delete or Remove Data Row using a WHILE LOOP

[jmf (Jawaad Farooqui)]

FOR EACH LOOP does not allow Remove Datarow Activity during it’s iterations. You can use datatable.select to filter if you want but if you want a simpler but extremely powerful method use this. The reason this is powerful is that you can put as many conditions on as many columns as you want. It’s powerful because of simplicity but performance wise it is not the best with Uipath.

• The source of datatable may be an excel or may be a CSV
• Test the Algorithm for performance if your input file is more than 0.5 GB.

*Test and Use at your own Risk. If you find a bug please share with us.

Method:

1. Set Total_in_records as an integer dtb_in.rows.count() before the while loop . We will use this as EOF(end of File) to end the while iteration.

2) Intialize Ctr_loop = 0 . This is our index
3) Use the DO while loop as below.
In my case I am removing the column “comp” =“0” you can change it to nulls or blanks by comparing with =“” ( two double quotes) .

If you remove rows the EOF reduces by 1. so total_in_record is reduced by 1 with each remove.You also do not have to ctr_loop++ because the current record is removed and the positions of the subsequent rows have changed by -1 .** Basically your datatable is shrinking shorter so your counters have to adjust themselves
4) I have read the dataable with headers and I can use as many conditions on as many columns as I want.

-Jawaad
https://www.linkedin.com/in/jawaad-farooqui-8309802b

[ClaytonM]

Hey,
Good info. Thanks.

This is a method I use. 1. Filter table to rows I want to delete using complex conditions, 2. For Each
It’s just as powerful with various vb.net methods like with .Where (is what I use)
Then, I can abandon counter numbers.

I will keep in mind the way you did it too just incase.

Thanks.

[vvaidya (Vinay Vaidya)]

Why not use the opposite condition in Select or Where and write only wanted rows back to same datatable using CopyToDataTable instead of looping?

dt = dt.Select("row(0).ToString <> "0").CopyToDataTable

[ClaytonM]

That works too

[1vivek12 (vivek verma)]

Hi I have a task in which i have build a data table. Now the is one column Aging (Double) now i have to filter data condition is: if row(“Aging”) is greater than 0.35
then populate data table with these values.
Please help

[jmf (Jawaad Farooqui)]

see if this helps. No looping needed

1. Assign strFilterCondition=“[Aging]>‘0.35’” ( 0.35 is between single quotes. )
2. Assign dt = dt.Select(strFilterCondition).CopyToDataTable

Please test for boundary conditions like spaces, non-numeric values , negative values) .
If it works please share the results here.

[kfukumo]

Hi all, I’ve a question, When I want to delete the row that contains irregular value, how should i do with this?
As now, I tried like this, which is referenced from UiPath (https://www.uipath.com/kb-articles/excel-automation.remove-rows-that-contain-keywords), but this way didn’t work out.
I want to delete the row that contains invalid data in partial match. Like this.

As you can see, Columun[No] contains [FN] keyword, I wanna let robot recognize this value is invalid.
Please help

Regards,
Kai

[balupad14 (Balamurugan)]

Hi @kfukumo,
Read the data into datatable. You can use the the below Remove Data Row Select Activity.

BalaReva.DataTable.Activities

Hi Friends. Below you have Documentation and Sample attachment for the “BalaReva.Datatable.Activities” You can find the Activities in DataTableExtensions NameSpace [image] Add Data Row Range It allows to add collection of datarow into DataTable. Sample File : AddDataRowRange.zip (7.8 KB) Remove Data Row Select It allows to remove the data rows based on the Select(Where) condition from DataTable. Sample File : RemoveDataRowSelect.zip (7.6 KB) Group By Aggregation(Sum,Count,Avg,Max,…

Regards
Balamurugan

[kfukumo]

Hi, @balupad14,
Thank you for your prompt reply, but your project couldn’t use in my UiPath.

And my concern is wheteher I could tell you exactly what I want to say.
the data I want to remove from datatable is not fixed value, it sometimes change, keyword that enable to recognize it which is invalid data is “FN” only.

Thanks,
Kai

[balupad14 (Balamurugan)]

HI @kfukumo,

yes . you can able to pass the custom condition in the select.
For example .
1.Create variable strCondition
2.Assign value to strCondition=“10FN05”
3.In the Remove Data Row Select activity, It has the Select property(Where condition).
you can assign it like =“No=’ + strCondition + '”

Regarding the Error.

You have to install the package BalaReva.DataTable.Activities as below.

if you are not able to see the package take a look that how to To configure the manage Package.

About the Community Feed category

Community Feed will be moved to Go! This category stores the knowledge about all components uploaded to our Community Feed. However, because the feed was replaced by a much more mature and functional UiPath Go! platform, it will no longer be used and we disabled the Upload button. Category settings: – only Forum staff can create new topics – everyone can reply The community feed has a new home https://gallery.uipath.com Thanks to @Alexandra_Olteanu, @dburner, @Cosmin_Popescu and others …

Regards
Balamurugan

[kfukumo]

Thanks a lot.
I’ll try it.

[fraijo]

Hi,
I need to remove a row from data table if the column 1 is repeating after checking the value in column 2 (date value), I need to keep the row with the latest date in the column 2
1 04/20/2018
1 05/30/2018
1 03/25/2018
2. 03/04/2018

In the above example I need to keep 1 05/30/2018 and 2 03/04/2018
Can anyone help on this?

[balupad14 (Balamurugan)]

hi @fraijo,

Here I have attached a sample to for ur situation.
Before that you have to install the this package .

File :MaxDate.zip (7.1 KB)

output :

Regards
Balamurugan.S

[fraijo]

Hi Balamurugan,

I have tried after adding that package. but it is throwing error

Main has thrown an exception

Source: Group By Aggregation

Message: Syntax error in aggregate argument: Expecting a single column argument with possible ‘Child’ qualifier.

Exception Type: SyntaxErrorException

System.Data.SyntaxErrorException: Syntax error in aggregate argument: Expecting a single column argument with possible ‘Child’ qualifier.
at DataTableExtensions.GroupAggregation.Execute(CodeActivityContext context)
at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.ActivityInstance.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

[balupad14 (Balamurugan)]

hi @fraijo

if you don’t mind can you share the source to verify?

Regards
Balamurugan

[fraijo]

sample.zip (17.7 KB)
Please find the source code

[balupad14 (Balamurugan)]

Hi @fraijo,

I have changed the source.The column name is having the space…

File :sample.zip (17.9 KB)

Regards
Balamurugan

[fraijo]

Hi Bala,

It is working only for those two columns. when I tried to get more columns as per the requirement, it was giving the wrong data. The highest date row was deleted and other two rows are there in the result sheet.

is there any way to get the remaining columns also?

Thanks in advance

[padamus7 (Paul Adamus)]

Hey I am really interested in this expression, but it seems like it’s incorrect. Seems to be a missing ". Can you tell me the correct expression?

[Karun (Karun Kumar Nalam)]

Hi @balupad14,
In my case there are no column names in my Excel file then how can I mention default column names of Excel(A,B…etc) in select property of Remove Data Row Select Activity to remove some rows.