Filter DataTable for integer values only, using LINQ

Help
1

Hello friends,

I am pretty new to LINQ and ran into a bit of a roadblock that I hope someone can help with. My goal is to filter out of my DT any values that are not integers in the column “Account”. This column has integers, strings and empty strings.

Here are my two attempts so far, that I can’t get to work properly:

  1. This gets me the error of “cannot infer a common type because more than one type is possible” After researching this here, I tried using the cint function for the return, but still getting an issue.

dt.AsEnumerable.Where(Function(r) If(IsNumeric(r("Account").ToString), r("Account").ToString), False).CopyToDataTable

  1. This earlier attempt obviously crashes, as there are other datatypes in the column. Unsure if there is a way to skip rows that error. (In my dataset, it is safe to assume integers are larger than 1)

dt.AsEnumerable.Where(Function(r) CInt(r("Account").ToString) > 1).CopyToDataTable

Hoping someone can point me in the right direction. Thank you :slight_smile:

2

To be honest there can only be one datatype in a certain column… it will be determined by the first one as the read range will set the column datatype to be. You will need to write that IF to filter out the values that can break the filter. IsNumeric can take an object, so first thing you do is remove that .ToString from there: r(“Account”).ToString

1 Like
3

@efleurent
The LINQ Where part is expecting a Boolean returned as the result of the expression defined for its (lets call it) function.

Lets assume you want to filter out:

  • column is not numeric
  • column is not empty or Null
    so such checks has to be evaluated as usual <Evaluation Statement> Operator <Evaluation Statement> .... and then it should work

As good mentioned below it is advisable to filter out in advance rows with null values within this column can e.g. be done with filter data table or chained Where Statements

Let us know your open question. For further help maybe you can provide some sample data, that we can use for further solution development

BTW: maybe the isNumeric Check can be implement as a RegEx isMatch more clear

1 Like
4

Where(Function (row) Not String.IsNullOrEmpty( row(“Account”).ToString)

if row(“Account”) is null then your statement above would break anyway because of row(“Account”).ToString

5

Hello @efleurent

Mostly you get this error when you are trying to use two different datatypes in a query…

instead of all the query you have written, you can do your task only by using the Isnumeric
method

DT.AsEnumerable().Where(Function(r) cstr(r(0)).IsNumeric ).CopyToDataTable

Use the above query and let me knwo about it

Check this workflow for better understanding

Isnumeric.xaml (7.1 KB)

EDIT:- this code will work just fine unless and until it hits a blank value from an excel file…
to overcome this error
Just tweak your query a little bit by adding a .tostring instead of Cstr

DT.AsEnumerable().Where(Function(r) (r(0)).tostring.IsNumeric ).CopyToDataTable

2 Likes
6

Thank you @ppr @bcorrea and @vickydas for your help! Now understanding it needs a boolean as return, makes more sense on how to build the code.
The thing I am still scratching my head about, is this compiler error I am getting. I tested your xaml file and it worked no problem, went out to lunch and after re-opening and re-downloading I cant seem to get it to work again? Either way, I simply shifted the isnumeric check and working fine now. Below is the code as I used it for anyone curious.

dt.AsEnumerable().Where(Function (row) IsNumeric(row(0).ToString)).CopyToDataTable

Thank you all once again for your detailed explanations :slight_smile:

7

still you should not do this part: IsNumeric(row(0).ToString) it is unnecessary and if row(0) ever contain a null value, would break your code, just dont put that ToString.

8

hi @bcorrea I am currently testing the code I commented with empty rows in my test dataset, and it is not causing the automation to crash.

Thank you

1 Like
9

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