Filter DataTable-The output row count is the same as input

Help Activities
, ,
1

Hi,
I’m trying to remove rows in excel that their 4th column starts with: 100 or 200.
Note that in the sheet those cells appear with a space.
The Output sheet row-count is the same as the input…
See image:

image
Thank you!

2

Hi @hp321 ,

You should provide the Column Index and not the Column number, If your Column Number is 4 then 3 should be provided in the column section.

Indexing starts from 0.

1 Like
3

Have a look at this LINQ

Assign Activity
dtFiltered =

(From d in YourDataTable.AsEnumerable
Let v = d(3).toString.Trim
Let chk = {"100","200"}.Any(Function (x) v.StartsWith(x))
Where not chk
Select r = d).CopyToDataTable

Handling empty result:
:ambulance: :sos: [FirstAid] Handling of The source contains no DataRows exception - News / Tutorials - UiPath Community Forum

Debugging / Inspection / Tracing / Prototyping
Understanding the 6 Debugging Panels of UiPath in the easiest way possible! - News / Tutorials - UiPath Community Forum

EDITED: with the hint of @supermanPunch
changed 4 to 3

4

@hp321

use this in assign activity

dt1.AsEnumerable.Where(Function(a) not a(3).ToString.trim.StartsWith("100") or a(3).ToString.trim.StartsWith("200")).CopyToDataTable

no need to use filter activity

5

Hi @Shiva_Nikhil
I tried you idea. As you can see:
it is index 4 [column 5]
I would want that something like this won’t appear:
image
I assigned this to the dt variable:

image
image948×100 5.18 KB

Do I need to add the space? like (" 200")?
thanks

6

as we do trim the column values to be more robust we dont need the space for " 100", " 200" StartsWith check and can handle both scenarios (with/without starting space in column)

7

Thanks @ppr
See what I get:
image

8

we dont know what was done at your end in details

But we do have this result and confirmation that is accepted within an Assign Activity
grafik

9

Hi,
I’m trying to keep the rows that in the 5th column are empty or are “N/A” or contain “2023”.
I started the workflow with “read range” added the “assign” and concluded with “write range” using the same variable at all stages.

I’m using this code in assign:

(dt_filteredjoin.AsEnumerable().Where(Function(x) x(4).ToString.Contains(“2023”) Or x(4).ToString.Equals(“N/A”) Or String.IsNullOrEmpty(x(4).ToString))).CopyToDataTable()

Please let me know the reason to that he column comes out empty,althouhg when I click to check the count I see there are a few…
Thank you!

10

Thanks @supermanPunch , @ppr , @Shiva_Nikhil
The sheet was not deleted after each run, that’s why it seemed like the filter was not working properly…

2 Likes