After using "read range" , the datatable filter doesn't work

Help Activities
, ,
1

I use the excel activity “read range” to read data from a excel file, the data is storaged in a datatable variable, then I filter the data with “filter data table”. but it doesn’t work, the result is empty.

Strangely, I use another acitvity “system-file-read range workbook” to read same data from the excel, and filter. It succeeded.

what’s the different of “excel-read range” and “file-read range workbook”? Thanks.

5
5911×291 7.29 KB

1
1514×602 18.3 KB

2

7
8

9
9832×548 16.2 KB

2

excel read range activity is modern activity and works when excel application is installed in your desktop

Read range workbook is a classic activity it will works when there is no excel application installed in your desktop

3

thank u for your reply.

I think the function of the “read range” component is fine as it reads data from Excel normally. But why did the filtering fail?
Is there some difference in the datatable after read range? I didn’t see any differences in the content display from the watching view.

4

I tried to change the filter condition to “2.2” and used strings, but there was no improvement.

5

image
image772×689 19.7 KB

once try using filter activity
as you have used modern excel activities

6

Hi,

Can you check datatype of the column for both datatable using LogMessage or MessageBox as the following expression?

dt.Columns("IWH Support Time(H)").DataType.Name

Probably, ReadRangeX 's column is Double and ReadRangeWorkbook’s column is String, I guess.

Regards,

7

Yes, Thank you.
firstly, I used the “excel filter”, and then “read range”, it’s no problem. it can filter data successfully.

8

11
11672×1264 45.8 KB

thanks. the data type is different with the two activity. one is “string”, another is “object”

I think it’s maybe has different feature packages and different technical implementations, so the functional components cannot be mixed to use. That is, the interface standards for component functions are different.

9

Hi,

Thank you for sharing.

As it’s a little complicated that FilterDataTable decide which datatype is used, the following LINQ expression and If may be better.

image
image887×686 32.9 KB 

arrDr=dt.AsEnumerable.Where(Function(r) CDbl(r("IWH Support Time(H)").ToString)<=2.2).ToArray()

Regards,

10

@wang_long

With string datatype ypu cannot use <= to filter…that is the reason it is failing

Genwrally when read as workbook it reads the backend value

Cna you please try changing the reqd formatting to rawvalue and check if that works…mostly that should be working and please remove visible rows check box from read range…

And yes to avoid such differences we sometimes go with linq depending on how the data would be coming…as sometimes there might be empty cells as well in place of 0 and then when read using read range because of empty cell the column type might be considered as string instead of object or double

And for the same in linq we can add a little caution as well to check for empty or parse cdbl before trying to convert and check

arrDr=dt.AsEnumerable.Where(Function(x) Double.TryParse(x("IWH Support Time(H)").ToString,0) AndAlso CDbl(x("IWH Support Time(H)").ToString)<=2.2).ToArray()

Cheers

11

Hi @wang_long

When you are using the excel activities the Microsoft Excel application has to be installed in your system, then it works.

If the Excel application is not installed in your system, make sure to install to use the Excel activities.

If you don’t want to install the Excel application then you can use the workbook activities, it doesn’t need excel application installed in your system.

Hope it helps!!