I have the following two excel files to reconcile File 1 : File’s name System A 20210913 where 20210913 is date formatted as YYYYMMDD File 2: File’s name is System B 13092021 where 13092021 is date formatted as DDMMYYYY I would like to set up a scheduler for my workflow to run automatically following the rules below: * If the dates from two files are different, the workflow will stop running immediately and will send an email to notify recipients of the inconsistent dates * If dates from the two files are the same (13 Sep 2021 in this example), the workflow will be able continue to run and reconcile the two files. The reconciliation result would then be output into an excel file consisting of 2 sheets Sheet 1: Matched records from the two System A and System B files Sheet 2: Unmatched records (anything records from System A but not found in System B) Finally, the workflow will send out an email with output file attached to relevant recipients. Any idea is much appreciated! Thanks Output.xlsx Input System B 13092021.xlsx Input System A 20210913.xlsx

Notify when file's dates are inconsistent

anhdo92

I have the following two excel files to reconcile

File 1 :

File’s name System A 20210913 where 20210913 is date formatted as YYYYMMDD

File 2:
File’s name is System B 13092021 where 13092021 is date formatted as DDMMYYYY

I would like to set up a scheduler for my workflow to run automatically following the rules below:

If the dates from two files are different, the workflow will stop running immediately and will send an email to notify recipients of the inconsistent dates
If dates from the two files are the same (13 Sep 2021 in this example), the workflow will be able continue to run and reconcile the two files. The reconciliation result would then be output into an excel file consisting of 2 sheets
Sheet 1: Matched records from the two System A and System B files
Sheet 2: Unmatched records (anything records from System A but not found in System
Finally, the workflow will send out an email with output file attached to relevant recipients.

Any idea is much appreciated!

Thanks

Output.xlsx

Input System B 13092021.xlsx

Input System A 20210913.xlsx

Workflow

Date Time

Accepted answers

RolandSchubert

Hi @anhdo92 ,

I've added the EMail tools now, both are not configured, but I think it may help to clarify the logic of my approach.

There are 2 Tool Containers, depending on the value of "Check" (dates are different or the same), one of the Containers is enabled, the other one is disabled and either the join is executed and result mail is send or the error mail is send without executing join.

Best,

Roland

CheckDate.yxmc

CheckDate.yxmd

All comments

RolandSchubert

Hi @anhdo92 ,

you can use Directory tools to find the filenames (I think, only the "date part" is changing). You can extract the date from the filenames, convert both dates to date format and compare.

To execute the workflow only if both dates are the same, you can use a macro with a Condition tool and a Tool Container. If check of dates isn't successful, the container will be disabled and the steps are not executed, if dates are the same, the container is enabled, next steps are executed. You can add the required mail functionality in a similar way (mail to warn, that dates do not match) and add an Email tool (use Block Until Done - anchor 1 for Output tool, anchor 2 for Email).

What do you think? Let me know if it works for you.

Best,

Roland

CheckDate.yxzp

anhdo92

@RolandSchubert thanks a lot Roland. I can't seem to open up your workflow to have a closer look tho... Upgrading to latest version will take a couple of days for me to sort out governance paperwork.. Is there any chance to convert it back to the older version I'm using (2019.3 x64) ?

RolandSchubert

Hi @anhdo92 ,

I've attached the workflow and the macro in version 2019.3, but you can modify the version by editing the file using a text editor (Notepad, Notepad ++). Replace the version I used to create the file with the one you currently run.

Does it work for you?

Best,

Roland

CheckDate.yxmd

CheckDate.yxmc

anhdo92

HI @RolandSchubert

Is it possible to get the workflow to send email in both cases

When the dates are not consistent - send email to notify

When dates are consistent - continue to do the reconciliation and then send another email with recs result attached ?

And after the macro, what I would like have the output in excel. The current macro generates 2 separate workflows

Thank you

RolandSchubert

Hi @anhdo92 ,

I've added the EMail tools now, both are not configured, but I think it may help to clarify the logic of my approach.

Best,

Roland

CheckDate.yxmc

CheckDate.yxmd

anhdo92

Fantastic! Thanks a lot @RolandSchubert for your help 🙂

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703