Add a whole data row from Excel if the condition is met

Help
1

Hi

How do I set up my studio to check every row from an Excel sheet? If the row “date” meets my criteria, then the whole row should be added to a table.

And how should the properties in “Add Data Row” be set?

This is what i have started with:

image
image500×633 15 KB

2

In the Add Data Row activity in DataRow field put “Row” and in DataTable field put the desired DataTable

1 Like
3

Thanks for the fast response!

If my dates in Excel is expressed like: “22-07-2019 00:09:30” , how do I split this up, so that the expression meets the condition of only the date as: “(now.AddDays(-1).ToShortDateString)”?

4

Use different expression like :

Now.AddDays(-1).ToString(“MM-dd-yyyy hh:mm tt”)

5

If I only have the date, should I then use some kind of “Substring(0,10)” to get the MM-DD-YYYY, if the expression in the if condition is:

row(“Date”).Equals(now.AddDays(-1).tostring(“MM-dd-yyyy”))

Thanks for the help. Very much appreciated!

1 Like
6

Yes you can use String manipulation or Regex to get the value that you wanted.

If my post helped you, you can mark it as a solution so other people see it faster

7

How should the string be placed in the condition:

row(“Date”).Equals(now.AddDays(-1).tostring(“MM-dd-yyyy”))

The excel sheet looks like this, where the date and time is in the same column, separated by two spaces.
image

8

Try

Now.AddDays(-1).ToString(“MM-dd-yyyy hh:mm”)

9

If i only have the date MM-dd-yyyy, shouldn’t the string then be on the: row(“Date”) since there is “too much data” in the excel sheet?

Something like:
row(“date”).substring(1,10).Equals(now.AddDays(-1).tostring(“MM-dd-yyyy”)) - just a guess

10

Can you tell me what do you want in your IF statement?
In your excel table you don’t have just MM-dd-yyyy

11

I want to check each row in the Excel sheet. If the date of the row is yesterday, then it should copy the entire row into a data table, that should be sorted by the date and time (from oldest to newest).

If the row has a date that is not yesterday, then the row should be left out.

This should be done through the entire excel sheet.

I don’t know if it is possible, if there is more information in the excel cell than nedded.

12

Okay.
Can you send me the Excel table?
Use some version of the IF statement that we talked about to resolve your problem

13

ExcelTable.xlsx (97.8 KB)

Here is the Excel table :slight_smile:

14

Karsten Forum.zip (38.4 KB)

Here you go. I think this solves it

3 Likes
15

Hi @Karsten_Bertelsen,
You can even use like this accurate results…
row(“Date”).split(" "c)(0).Equals(now.AddDays(-1).tostring(“MM-dd-yyyy”))
Use the split function then you will get short date.
Cheers

16

Read the range into a DataTable. you can then use FilterDataTable activity to get the row(s) that match your criteria.

image

the matched row(s) are in the output data table variable

17

I am getting this error when trying to copy the flow. Any suggestions on how to go past this?

image

But in yours, it works perfectly! Thanks a lot!

18

You probably didn’t put anything here:

Untitled
Untitled1544×694 24 KB

1 Like
19

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