Fetch Date from excel & compare with the current date, if matched get the corresponding row from excel

uiautomation

#1

Hi,

I have a new requirement where I need to read dates from my excel sheet (dates are in column2 comma sepaarted) and compare the date with current system date. IF date matches with any of the column in excel sheet get the jobname and based on the jobname trigger the job flow. MY excel looks like below

Jobname Dates
job1 1/2/2018,2/2/2018,3/3/2018
job2 1/4/2018,2/5/2018,3/6/2018
job3 1/10/2018,2/7/2018,3/9/2018
job4 1/11/2018,2/12/2018,3/1/2018

Can someone please share a flow on how to get this done. I have tried different solutions but did not work.


#2

Hi.

So, we can do this by looping through the excel sheet and comparing the dates, or we can do this using LINQ which is a little less cluttered by looks more complicated (even though it’s not)

Let me provide you with the first solution.
This is just psuedocode to represent the activities

For each row in dtJobs
    If activity <==condition:  row("Dates").ToString.Split(","c).Where(Function(d) IsDate(d.Trim) ).Select(Function(d) CDate(d.Trim) ).ToArray.Contains(Now)
        Assign jobname = row("Jobname").ToString.Trim

So, essentially you are taking the Dates column, splitting it by comma, then taking only values that are dates and using .Select() to convert them to DateTime types. Finally, using .Contains() you can check if Now is in the array of dates. Then, just pull in the Jobname column

Hope that helps.

Regards.


#3

Thanks ClaytonM for your quick response I will implement it and get back if it works :slight_smile:


#4

I tried it but somehow messed it up. Can someone please
share complete workflow tat will help me understand.


#5

Can somebody help me with workflow on this requirement.