How to filter excel file in if-statement

Help Studio
, , , , ,
1

Hi,
I have a problem: I have an Excel table in which the columns date (column A as “dd/mm/yyyy”) and status (column D) are listed. I would now like to build a function that first checks whether today’s date combined with the status “Order executed” is already in the file. If yes, the function should end, if no, other activities will be executed.

How do I create this?

2

Hi @lope_0238

Read the data into datatable and use filter activity to filter all the columns that you want. And if filter returns a row then it means your excel has the required combination

Cheers

1 Like
3

Thank you for the reply!
If I want to keep it generic can I also write:
Column “Datum”
Operation “=”
Value “DateTime.Now.ToString” ?

And do you know if there is also a way to put it directly in the condition of the if-statement e.g. as a linq or so?

4

Hi @lope_0238 ,
Try below query
dt.Select("ColumnA='"+Now.ToString("dd/mm/yyyy")+"'").CopyToDataTable()

if you want to put it as if condition try below
dt.Select("ColumnA='"+Now.ToString("dd/mm/yyyy")+"'").length>0

Regrads,
Arivu

1 Like
5

Hi @lope_0238

Ye syou can give column number or name…anything and for datetime…please useToString(“MM/dd/yyyy”) or any other format that matches with your excel

If you use filter dt then use dt.rowcount.equals(0)

And yes you can use linq as well…for that use dt.asenumerable.where(function(row) row(“datenum”).tostring.equals(now.tostring(“MM/dd/yyyy”)).count.equals(0)

Cheers

1 Like
6

Thank you for the assistance

1 Like
8

Hi @lope_0238 ,

Could you check with the below Expression :

DT.AsEnumerable.Any(Function(x)CDate(x("date").ToString) = Now andAlso x("status").ToString.Equals("Order executed"))

Here, DT is the datatable which contains your sheet data.

The above Expression could be directly used in an If Activity as you are just checking for existence of values.

Let us know if you are receiving any errors.

1 Like
9

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