How to Delete or Remove Data Row using a WHILE LOOP

excel

#1

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.

image
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


How to delete a row in an Excel workbook
Delete data row from data table
#2

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.


#3

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


#4

That works too :smiley:


#5

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


#6

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.


#7

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


#8

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

Regards
Balamurugan


#9

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


#10

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


#11

Thanks a lot.
I’ll try it.


#12

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?


#13

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


#14

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)


#15

hi @fraijo

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

Regards
Balamurugan


#16

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


#17

Hi @fraijo,

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

File :sample.zip (17.9 KB)

Regards
Balamurugan


#18

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


#19

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?