Filter datatable after read range from excel

Hello,
I noticed some weird behaviour. In a project I was reading range from an excel file and then filtering into a new datatable, all entries that had amounts less than zero.

When using the classic read range workbook, this just worked fine:
image

When using the modern read range filter datatable above did not work:

The classic seems to read everything as an object where modern it seems to depend on how the excel file itself is defined.

In my team we are trying to only use the modern activities so I solved it with linq:
DT.AsEnumerable().where(Function(r) CInt(r("Column").ToString) < 0).CopyToDataTablePreformatted text

Is there a way to make the modern read range behave the same way as the classic one?

1 Like

Hi @Snorri_Arinbjarnar ,

What output do you get in modern read range activity?

And what main difference you are finding in both?

@Snorri_Arinbjarnar

I guess using the excel read range the column is casted to string instead of number…so the filter is not working as expected and hence linq is working there as well as you are casting it to number in the query

Cheers

1 Like

Hi @Snorri_Arinbjarnar ,

Could you change the Read Formatting value of Modern Excel Read Range activity to RawValue and check if you are able to get the output ?
image

Also, Do note that when performing numerical comparisons, use a numerical value and not a String, in the Filter Datatable screenshot provided, the value should be just 0 and not "0".

Let us know if you are able to get the output after performing the changes.

Hi @Snorri_Arinbjarnar ,

The output of the Read Range activity is a DataTable variable that contains the data from the specified range in the Excel workbook. The DataTable variable has columns that correspond to the columns in the Excel worksheet, and the data type of each column is determined by the data type of the data in the corresponding column in the Excel worksheet.

Linq works because, in condition CInt(r("Column").ToString) < 0 that each DataRow object must satisfy in order to be included in the filtered collection. In this case, it checks whether the value in the “Column” column of the DataRow is less than 0, after converting it to an Integer type.

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