How to Delete or Remove Data Row using a WHILE LOOP

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

1 Like

That works too :smiley:

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

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.

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

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

Regards
Balamurugan

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

1 Like

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.

Regards
Balamurugan

1 Like

Thanks a lot.
I’ll try it.

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?

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 :

image

Regards
Balamurugan.S

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)

1 Like

hi @fraijo

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

Regards
Balamurugan

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

Hi @fraijo,

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

File :sample.zip (17.9 KB)

Regards
Balamurugan

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

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?

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.

Hi, Can i know what the properties inside Remove Data row activity?

@mashy2
You have the option to use either a DataRow or Row index in the ‘Remove Data Row’ activity. Ideally, you would use a DataRow inside a generic ForEach using an array of DataRows (I do not recommend using a Do while)
which in practical use would look something like this:

For each row1 In dtVariable.AsEnumerable.Where(Function(r) r("Amount").ToString.Trim="").ToArray
    Remove Data Row: row1 in properties

So, this example would loop over all rows where the amount is empty, and remove those rows. you should implement this with how it works for your process.

You can not remove data rows while looping a data table, from my understanding, since you would be changing the source table which you are looping. (ie ForEach row in dtVariable)

As an alternative approach, you can simply filter out the rows, so no looping required in this approach:

Assign arrFiltered = dtVariable.AsEnumerable.Where(Function(r) Double.TryParse(r("Amount").ToString.Trim, Nothing) ).ToArray

then, perform some actions over that array of rows. In my example, it stores all rows where the Amount is a number to an array of rows. If you want them back to a table, simply use an IF:

IF arrFiltered.Count > 0
    Assign dtVariable = arrFiltered.CopyToDataTable

You can also use the Filter Data Table activity instead of this approach, but from my experience, it has less capabilities.

Note: all examples are pseudocode using activity names.

Regards.