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:
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.
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.
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
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.
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.
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.
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?
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)
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?
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.
Anil_G
ashokkarale
jinal.shah
Gautham_Pattabiraman
postwick
chandreshsinh.jadeja
vrdabberu
Ajay_Mishra
sven.wullum1
Vyshnavi_Nalumachu