Restrict the row generated by Directory

[ankurrjit]

Hello All,

Is there a way to restrict the rows generated by a DIRECTORY tool. I have a path that holds folder from 2018 for every single day like 20180101, 20180102....20200604.

whenever I run the workflow, it takes 24 minutes to read all these files and produce rows info. How can I restrict the no of rows read by the directory to read-only folders from 20200101 onwards and ignore other folders.

Best Regards

Ankur Tyagi

[DavidP]

I'm wondering if you can put the Directory tool inside a Batch Macro and update the File specification filter via the Control Parameter.

You can specify the exact filename with date and then replace the date with a list of valid dates from your control parameter.

[ankurrjit]

Hi @DavidP   Thanks for the suggestion. I tried to give it in this way as below snapshot but it is not working, am I doing something wrong.?

[RobertOdera]

HI, @ankurrjit 

I'm curious.

Can you try updating your File Specification in the Directory Tool from what you presently have, to *2020*

It's a long shot (hoping the directory well only has the files you need), but hope it helps.

Cheers!

[DavidP]

@RobertOdera,'s suggestion is certainly much simpler and more sensible than trying to write a batch macro with a directory tool in it - although the idea does appeal to me just for the sake of it, so I might try it anyway. I'll get back to you on that idea.

Looking at your filename syntax, putting the following in the File specification will probably work:

*RTL_SAP_SD1_INVENTORY_BALANCES_DAILY*2020_*

[ankurrjit]

Hi @RobertOdera  - Wooohooo!!! , it is working and now I am able to bring the search down.

Having said that If I go with this option "2020_", the "Directory" tool is scanning everything from 2020-01-01. It would be life-saving if there is any way if I can bring to the latest folder e.g. today 20200604, tomorrow 20200605, etc. without any manual intervention. My file names are as below as per 

[DavidP]

The directory tool also returns the creation datetime as a field. You can use a filter tool after the Directory tool with a condition like

todate([CreationTime])=todate(Datetimenow())

which will only keep files created today.

[RobertOdera]

Sure thing, @ankurrjit 

Except we are now back to @DavidP  approach. That is the way to go for a moving cut-off.

Consider generating a formatted string for DateTimeToday (), like so Replace(DateTimeToday(),'-','')

So now for today you would have 20200604

Use it in your macro as the update value.

This will always pull the 'today' files for any given day the workflow is run.

Cheers!

[ankurrjit]

agree with @RobertOdera  and @DavidP , this is what I am doing further in filters, but I am trying to reduce the search at directory itself, because now it takes around 40 minutes and imagine at the end of this year it might take more than hour just to scan the files?

Is there any way I can update the directory path with e.g. abc\def\20200604 and go on with the latest date?

I tried with batch macro but it is not updating directory path

[DavidP]

As promised, here is a workflow that uses a batch macro to return only files based on your file naming conversion where the date matches today's date.

Note that you have to set your folder path in the text input tool. With this you have a lot of flexibility with the folder path. For instance, you can add multiple rows for multiple locations and then dynamically replace the date folder in the path for each with today's date.