Problem in filtering null values in uipath

Hi All,
In the table below (DTInput) Empty cells in column “Country” are null values and Empty cells in column “Email” are blank values.
image
I need to filter this datatable so that all the rows where there is no values in column “Country” and “Email” should be removed. and this should be the result:
image
Please suggest a linq code for the same
@NIVED_NAMBIAR

2 Likes

Hi @Anamika15,

Please find the below query.

DTInput.AsEnumerable.Where(function(row) row("Country") isNot Nothing and not string.IsNullOrEmpty(row("Email").ToString.Trim)).CopyToDataTable

In the above query, As per your requirement, we are filtering based on the following condition,

  1. Country - null values
  2. Email - blank values

I suggest have null and a blank check for both fields to be on the safer side.

Hope this helps.

Thanks :slight_smile:

Hey!! Thanks for the help. But the above code is removing those rows also where we have some value in “Country” and “Email” is blank.
Actually the problem is when I am counting the length of the cells null values in “Country”, I am getting the count as 1. But there is nothing in the cell. I have used .ToSting.Trim method also. Still getting length count as 1 in country and 0 in email.
Hence, I am not getting the correct result from the code you shared.
I am getting this result:
image
But I need this as the output:
image

@Anamika15
it looks like the request differs from expected result:

so that all the rows where there is no values in column “Country” and “Email” should be removed. and this should be the result:
image

but we can derive from expected result that only blank/null Country rows are to filter out (Email Derrik is empty).

Give a try on:

(From d In dtData.AsEnumerable
Where Not If(isNothing(d(“Country”), True, String.IsNullOrWhiteSpace(x(“Country”).toString.trim)))
Select d).CopyToDatatable

Handling empty filter result can be done with the pattern below:

@Anamika15

You can use Filter Datatable also for the same requirement

Check below for your reference

Hope this may helps you

Thanks

1 Like

Can we write a linq where we give the condition as filter the datatable with condition that remove rows where “country” does not contain any alphabet or numbers and “email” is blank?
Value in Column “Country” is a garbage value which is showing as empty bit the length count is 1. I need to filter based on this condition.

Value in Column “Country” is a garbage value which is showing as empty bit the length count is 1. I need to filter based on this condition.

@Anamika15

You write Linq function as below for length count

Hope this helps you

Thanks

If it looks empty, then the function String.IsNullOrWhiteSpace() will take care of it. If you want to remove all rows where both Country and Email are empty test with the linq below:

DT.AsEnumerable.Where(Function(r) Not (String.IsNullOrWhiteSpace(r("Country").ToString) and String.IsNullOrWhiteSpace(r("Email").ToString))).CopyToDataTable

It will keep the Derrik row but also rows where the Country is empty and there’s an email.

So in the example below, it will keep Derrik and Jane rows but not Donald.
image

If String.IsNullOrWhiteSpace() is not working for you, here’s an alternative using Regex instead. It will check for word characters in the Country column.

DT.AsEnumerable.Where(Function(r)  System.Text.RegularExpressions.RegEx.IsMatch(r("Country").ToString, "\w+") or Not String.IsNullOrWhiteSpace(r("Email").ToString)).CopyToDataTable

Regular expression worked :slight_smile: Thanks!!!

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.