Help in LINQ query

Help Activities
, , , ,
1

Hi All,
i have excel sheet and i need to filter a column. it contains numeric values and string values.
I need to filter and get the rows which are grater than 18 if their age.

DT.AsEnumerable().Where(Function(row) If(Double.TryParse(row.Field(Of Object)(“Age”).ToString(),0), CDbl(row.Field(Of Object)(“Age”))>18,False)).CopyToDataTable()

this code is working when column contains numeric values.
Sometimes i get only string values at the time i’m getting error with that. How can i handle that exception . any help in code modification.

2

Hi @Learner007

DT.AsEnumerable().Where(Function(row) 
    If(Double.TryParse(row.Field(Of Object)("Age").ToString(), 0), 
        CDbl(row.Field(Of Object)("Age")) > 18, 
        If(row.Field(Of Object)("Age").ToString().Trim() <> "", 
           False, 
           False)
    )
).CopyToDataTable()
3

A defensive Query syntax

dtFiltered =

(From d in dtData.AsEnumerable
Let ag = d("Age")
Where Not (isNothing(ag) OrElse String.IsNullOrEmpty(ag.toString.Trim) )
Where Double.TryParse(ag.ToString.Trim, nothing)
Where Double.Parse(ag.ToString.Trim) > 18
Select r = d).CopyToDataTable
4

hi @ppr Still getting same error.

My Input1 :
Not Available
Not Available
24
Not Available
12
Not Available

Input 2 :
Not Available
Not Available
Not Available
Not Available

Even my should work for Second Input but it is giving error like below

Assign: The source contains no DataRows.

5

we dont know which one

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

6

Hi @pravallikapaluri Still getting same error

Assign: The source contains no DataRows.

7

@ppr if the input comes likes this then it is throwing error. If it contains at least 1 numeric value it won’t throw error. I will check that link what you have sent.

8

@ppr

dtData.AsEnumerable.Where(Function (x) x(“CCode”).toString.Trim.ToUpper.Equals(strCCFilter.ToUpper)).CopyToDataTable

How can i use this for integer and what is the value of strCCFilter

9

As described within the FirstAid

Result | DataType: List(Of DataRow) =

(From d in dtData.AsEnumerable
Let ag = d("Age")
Where Not (isNothing(ag) OrElse String.IsNullOrEmpty(ag.toString.Trim) )
Where Double.TryParse(ag.ToString.Trim, nothing)
Where Double.Parse(ag.ToString.Trim) > 18
Select r = d).ToList

then model the if and check if there are Datarows returned or not

10

@Learner007

Try this one

DT.AsEnumerable().Where(Function(row)
Dim ageString As String = row.Field(Of Object)(“Age”).ToString()
Dim isNumeric As Boolean = Double.TryParse(ageString, 0)
If isNumeric Then
Dim ageValue As Double = CDbl(ageString)
Return ageValue > 18
Else
Return False
End If
).CopyToDataTable()

Hope it will helps you :slight_smile:
Cheers!!

11

Hi @Nawazish_Ahmad getting below error.

1702302525582440411704455343229
17023025255824404117044553432291920×1440 502 KB

1702302550721290833109751451889
17023025507212908331097514518891920×1440 481 KB

12

@ppr I’m unable to assign it to a output variable list

It is throwing error

13

Whenever an error is encountered, the detail information is also to share directly. We avoid ping-pong communication and the support can be done more efficently.

  • Reread the FirstAid and follow the described steps
  • Adapt the Assigns,
  • create needed additional variables and modellings
14

Hi @Learner007 ,

Please find the below mentined link, whcih contains video on it.

Link:-https://youtu.be/I2WXWKtBJhs (This will works from 15-Dec-2023 at 5PM onwards)

Linq:- DT.AsEnumerable().Where(Function(row) If(Integer.TryParse(row(“Age”).ToString(), Nothing), Convert.ToInt32(row(“Age”)) > 18, False)).CopyToDataTable()

This will needs to surrounded by try catch, then it will work with out any error.
Filter with Linq And Filter Data Table Solution-N0-4.xaml (17.8 KB)