Extract Date and Get the Latest Date from Directory

[KamenRider]

Hi

I have sample files below and would like to ask for you ideas on how I can extract the date and format it to "yyyy-mm-dd". After that is to get the latest date.

SM_SIDBULK_MUMBO_20230804_S1_INPUT_compress.csv
SM_INT_LANDERBULK_MUMBO_20230804_K1_input.csv
KM_INT_AGNECY_C_20230801_b_v1_compres.csv
KM_INT_AGNECY_C_20230728_b_v1_compres.csv

Please advise.

Kamen

[rzdodson]

@KamenRider see below. The trick is to Transform the data so that it is all vertically oriented, then utilize a Regex tool to parse out the date from the file name. From here,  I used the Regex tool to find the date pattern that is present in your file names. The result is the Date String field that has nulls where there isn't a date, and the 8-digit date where we find the pattern.

If your file names change their naming convention/nomenclature, you will either need to incorporate additional tools, change the file naming convention through a Directory/Regex Replace combination.

 

Hope this helps! :)

[rzdodson]

There is also this approach utilizing the Tokenize option within the Regex tool.

[KamenRider]

Hi @rzdodson 

Thank you. You did a good job in extracting the date however, I forgot to state that I also needed the files since I need to move it to another drive after getting the latest date. Can you please help me extend the solution to further get the solution I needed? below is the structure of my workflow.

The scenario is as follows:

1. I used directory since the path is directing to a number of folders. The files in the folder is provided with the date in the filename.

2. I am thinking of extracting the date to get the latest date to get the latest files which I need to get to move to another shared drive.

3. And as the sample I have provided, I need the following files so I can move or drop it to another shared drive location.

                  SM_SIDBULK_MUMBO_20230804_S1_INPUT_compress.csv
                  SM_INT_LANDERBULK_MUMBO_20230804_K1_input.csv

I hope to hear for your prompt response for my inquiries.

Thanks,

Kamen

 

[rzdodson]

@KamenRider when able, would you mind sharing the workflow from your last post? I have my suspicion on what the Select tool and your macro are doing, but want to make sure that it is not building out a file path before I start working a solution for the additions you mentioned.

[rzdodson]

In the interim, here is an adjusted solution that will retain the file paths that we are needing before the Select/Macro tools take over.

[binuacs · Accepted solution]

@KamenRider Another method 

[KamenRider]

Hi @binuacs @rzdodson 

Thank you! Both solutions are applicable. I'll be choosing the shorter one except that I have added a filter tool to filter the latest date.

Once again thank you for your assistance.

Kamen