Extract Date and Get the Latest Date from Directory

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

Workflow

Date Time

Accepted answers

binu_acs

@KamenRider Another method

MAx_Date_130823.yxmd

All comments

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.

2- Regex.png

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.

1 - Solution.png

Hope this helps!

Date in File Name.yxzp

rzdodson

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

2 - Solution.png

Date in File Name 2.yxzp

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.

Extend date.PNG

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.