Assign Activity LINQ Query

[Ishan_Shelke]

I have an Excel sheet with almost 40000 Rows in it,

I am trying to read the Excel Sheet and using an assign activity I am trying to filter out rows like this

(From r In dt.Select()
Where Convert.ToDouble(r(“Rem Life(%)”).ToString)>70 Select r).CopyToDataTable

But I am getting an Error as :

My Flow :

Does anybody have an idea what might be the issue ?

[ashwin.ashok ((TheCodingTheory))]

Hi @Ishan_Shelke

Could you give this a try?

(From r In dt.AsEnumerable()
Where Convert.ToDouble(r("Rem Life(%)").ToString)>70 Select r).CopyToDataTable()

Kind Regards,
Ashwin A.K

[Ishan_Shelke]

Hi Ashwin,

Still the Error is same.

[ppr (Peter Preuss)]

its about the value that is not parseable into a double by default. This can have multiple reasons e.g. local number format like
1.234,45 vs. 1,234.45

Just share with us some sample data of the datatable e.g screenshot from immediate panel

[supermanPunch (Arpan)]

@Ishan_Shelke

(From r In dt.Select()
Where Convert.ToDouble(r(“Rem Life(%)”).ToString)>70 Select r).CopyToDataTable

Hi @Ishan_Shelke ,

Maybe as a First try to also include .Trim on your .ToString and Check if it works.

If the above change doesn’t work then you could try the below :

DT.AsEnumerable.Where(Function(r)if(r("Rem Life(%)").ToString.Trim.IsNumeric,CDbl(r("Rem Life(%)").ToString.Trim)>70,false)).CopyToDatatable

Also, Check on the following for Errors on Directly converting to Datatable

[FirstAid] Handling of The source contains no DataRows exception

This FirstAid Tutorial will describe how a the source contains no DataRows EXCEPTION can be handled. Introduction Let’s have a look at the following filter data table scenario: Name CCode Tom US Charlotte FR Seema IN Jean FR Assignment: Filter all rows on a particular Country Code like FR, UK, ES, UK… Ensure that all CCode data column values are trimmed Ensure that the Filter check is case insensitive we can implement it e.g. with the help of a LINQ statement: dtData.As…

[Gokul001 (Gokul Balaji)]

HI @Ishan_Shelke

For me it’s working. Can you provide us the sample input?

Regards
Gokul

[Ishan_Shelke]

Just have this whole excel file, I am facing problem with this excel file

ATP stock report 19.09.2022.xlsx (5.9 MB)

[ppr (Peter Preuss)]

same as described here:
[FirstAid] Datatable: Debug & Analysis invalid DateTime Strings / String to DateTime Parsing Issues - News / Tutorials - UiPath Community Forum

We cannot rely on what we do see visually in Excel. So the datatable has to be inspected

@ppr

Just share with us some sample data of the datatable e.g screenshot from immediate panel

similar we can run an analysis in the immediate panel like

dtData.AsEnumerable.Where(Function (x) Not Double.TryParse(x(“Rem Life(%)”).ToString, nothing)).ToList

or others analysis lines

So we recommend to do this analysis as in less then 1 min the issues can be more analysed on the real causing values

[Gokul001 (Gokul Balaji)]

Hi @Ishan_Shelke

You can try with Filter Data Table activity

Check out the XAML file

LINQFilter70.xaml (6.2 KB)

Regards
Gokul

[Ishan_Shelke]

The To Trim command worked flawless Thanks