How to use the "Filter Table" from UiPath.Excel.Activities to filter for values that don't contain a string

how to use the “Filter Table” from UiPath.Excel.Activities to filter for values that don’t contain a string

For instance, I have an excel file containing the data below. I have to delete “Monika” and “Riya” before preserving the same excel document

image

My excel file should save like this once I delete “Riya” and “Monika”!

The “FilterTable” excel activity’s “FilterOptions” property retains the values “Monika” and “Riya” while hiding the other values. How can I save Excel while removing “Monika and Riya” and leaving all other values intact?

It sounds like the case is about
There is a list of Names
Datatable should be filtered on all names, which are in=remove or not-in=keep the list

With Filter DataTable we are not served by OOTB functionalities

With DataTable.Select or LINQ we do have such options

arrBlockNames | String Array = new String(){“MONIKA”, “RIYA”}

Assign Activity:

(From d in YOurDataTableVar
let chk = arrBlockNames.Contains(d("User Name").toString.Trim.ToUpper
Where not chk
Select r =d).CopyToDataTable

Handling empty results:

About LINQ

Above mentioned statement can also be extended to the EXCEL related Filter Activity.

We do miss the is-not-in-List functionality
Is-in-List filter:


but to create a dynamic filter array would also require extra effort

EDITED: more different Statement adaption

@ppr

Unfortunately, I don’t have a data table here, we could handle this much easier if that was the case. We could directly use “Filter data table” if I had a data table created.

I have only “Filter Table” activity here. Instead of “Including the string values”, I need to exclude the values I provide there. In the properties “FilterOptions”, there is an array of values that we can provide. If I provide {“Monika”, “Rioya”}, it’s including those two values and excluding the rest. But I want to do thing like doesn’t include “Monika” and “Riya”

we answered

but still we can combine different approaches

  • read in excel, excel Column
  • remove all Block values
  • use the result as a filter

maybe you can elaborate more on what is blocking you to use a read range. An keep in mind, we can combine activites as mentioned above

@Krithi1

You can just filter riya and mounika as you did…then use delete rows activity with visible rows checked…

And then use one more filter activity and use remove filters

Or

You can select not equals using advanced filter option in filter table and tehn use delete rows and select hidden rows now and now monika and riya gets deleted retaining remaining values

Hope this helps

Cheers

@Anil_G
“Monika” and “Riya” shouldn’t be deleted, but just filtered.

Would it be possible to elaborate more on this

  • When the filter table activity is used, then the UiPath.Excel.Activities was referenced
  • so also the typical Read Range activities are available

A feedback from your end can avoid some ping-pongs and we can have a better understanding of the scenario and constraints.
Thanks for support

To make it simple. I have an array [“Monika”, “Riya”]

If i use those two in my array, its filtering other values and saving “Monika” and “Riya”

Intead what i want to do is the other way round. How to write the function to exclude those two in the array.

@Krithi1

Then use second approach of using advanced filter and set not equals

Cheers

Thanks for the feedback

we answered.

Before we get into any more ping-pongs

OR
or fall into XY problems

Simply check on your own and feel free to ask for further assistance when interested in alternative options.

Happy Automation :+1:

Thank you, both. I was hoping to see if there is any simple solution around using some functions. But anyway, I took another route to solve this.

Hi @Krithi1

Input:
image

I have used Macros code to filter the names. Check out the below image and you can replicate the code. Below text file contains the macros code. Change the sheet name according to your requirement.


Filter Macros.txt (485 Bytes)
Sequence19.xaml (9.4 KB)

Output:

If you have any queries, I’m happy to help.

Regards

1 Like

Hi,
you can use invoke code activity
and use below code in it.

’ Read data from Excel into a DataTable
Dim dtOriginal As DataTable = New DataTable()
dtOriginal = DirectCast(dtOriginalArg, DataTable)

’ Create a new DataTable to store the filtered data
Dim dtFiltered As DataTable = dtOriginal.Clone() ’ Clone the structure of dtOriginal

’ Iterate through each row in the DataTable
For Each row As DataRow In dtOriginal.Rows
’ Check if the User Name column contains “Monika” or “Riya”
If Not (row(“User Name”).ToString().Equals(“Monika”) Or row(“User Name”).ToString().Equals(“Riya”)) Then
’ Add the row to the filtered DataTable if it does not contain “Monika” or “Riya”
dtFiltered.ImportRow(row)
End If
Next

’ Output the filtered DataTable
dtFilteredArg = dtFiltered

check below workflow.
Filter_data.xaml (8.6 KB)

Thank you very much!!

Hi @Krithi1

If you find the solution for the query, please mark my post as solution to close the loop.

Regards