Filter Data Table Activity - Failing to filter data correctly

I have a CSV file that is read into a datatable. The CSV has no headers so the datatable generated generic headers of Column1,Column2,…,…

Here are a couple of the records being read in:

I am trying to use the FilterDataTable activity to keep only rows which are >= 200 in Column7 and > 1 in Column5 which both the above examples are.

The resulting datatable is empty which I can only assume is because those columns are being recognised as a string rather than a number, so the greater than check doesn’t work.

Any suggestions on how to overcome this issue?

@daryldawes Are you sure you have the names of Columns properly, I guesss the Column Names starts from “Column0” When you don’t add headers. Can you Check that and confirm if you are using the right Column :sweat_smile:

Thanks. I did check the locals looking at the source datatable and it is the right column numbers. They start at 1 and are consecutive numbers.

Oddly enough, I have just hovered over Column5 and Column7 and it shows the data type as a Double. :expressionless:


@daryldawes Are there any rows with value greater than 200 :sweat_smile:, Filter Datatable Should Work fine

Yes. The 2 records that I posted in my original post have 6 & 11 in Column5 and 888.93 in Column7 which would meet the “keep” conditions provided to the filter datatable activity.

@daryldawes Oh if 888.93 was in Column7 , then it should have work, but When i Counted the Column in the post that you gave, it was in Column6 :sweat_smile:

@daryldawes Can you try this Query :
DT.AsEnumerable().Where(Function(row) (if(String.IsNullOrEmpty(row(“Column7”).ToString.Trim) Or String.IsNullOrWhiteSpace(row(“Column7”).ToString.Trim),False,Convert.ToDouble(row(“Column7”).ToString.Trim) >= 200)) and (if(String.IsNullOrEmpty(row(“Column7”).ToString.Trim) Or String.IsNullOrWhiteSpace(row(“Column5”).ToString.Trim),False,Convert.ToDouble(row(“Column5”).ToString.Trim) >1))).CopyToDataTable

Assign it to the Same Datatable that you are using

Only if the Filter Datatable is not working properly :sweat_smile:

@daryldawes is it solved ?
If not please keep variable type from value column to string as it it comparing string to int. which will alwayd fail.
Compare in this way:
Column name >= Convert.Tostring(200)


@7387023380, the issue is still unsolved. I have tried your suggestion and it still doesn’t pick up any records from the datatable.


@daryldawes Is it Possible to send the csv file?

@supermanPunch, this has worked a treat. Wonder why the inbuilt activity doesn’t work but this did. At least I have a solution that I can work with. :slight_smile:

@daryldawes You Have to handle Exception for that Statement :sweat_smile: , If there are no rows available after Filtering, The Datatable will be Empty, Hence it will throw an error.

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