Only keep newest file containing pattern

[SIMMS7400]

Hi Folks -

Happy Holidays!

I have an "Import" directory where all my import files from my source EBS system are dumped on a daily basis.

The format is as such:

Code: Select all

Client_GL_Detail_PBCS_122317_OCT2017.txt

During any given month, the only string that changes in the data i,e, 122317.

I need to be able to scan this particularly directory and remove all files older than the most recent.

For instance, if my directory consists of this:

Code: Select all

Client_GL_Detail_PBCS_122317_OCT2017.txt
Client_GL_Detail_PBCS_122417_OCT2017.txt
Client_GL_Detail_PBCS_122517_OCT2017.txt

It should look like this after the script processes it:

Code: Select all

Client_GL_Detail_PBCS_122517_OCT2017.txt

I know how to get the most recent file, but not sure how to preserve that during a delete process without spooling contents to a temp file, and then reading that which is very inefficient.

Could someone recommend a solution?

Thank you!

[aGerman]

If the creation time of the files fits to the names then it's quite simple.

Code: Select all

@echo off &setlocal
for /f "skip=1 delims=" %%i in ('dir /a-d /b /tc /o-d "Client_GL_Detail_PBCS_*_OCT2017.txt"') do ECHO del "%%i"
PAUSE

(remove ECHO and PAUSE if the right files are displayed)

If you need to process the file names please tell us what 122317 is for. If it's MMDDYY then OCT2017 wouldn't fit to your examples. Thus, I assume it's something different.

Steffen

[SIMMS7400]

Hi Steffen -

Thank you for that! But let me explain it a little bit better.

My target system is loaded with data for current month, current month -1, and current month -2.

Therefore, 122317 indicates the day the files were created from the source system (EBS).

For instance, on 122317, three files were dropped off in the import directory as such:

Code: Select all

Client_GL_Detail_PBCS_122317_DEC2017.txt
Client_GL_Detail_PBCS_122317_NOV2017.txt
Client_GL_Detail_PBCS_122317_OCT2017.txt

However, there are times the script may fail on the data import because an account wasn't added in the target system but exists in the source system. When the script fails on the data import, the data file isn't archived/moved. Therefore, on 12/24, the same set of files are dropped off and now an OCT file (or NOV, or DEC etc) could exist twice and we only want to load the LATEST OCT file (or NOV or DEC).

For example:

Code: Select all

Client_GL_Detail_PBCS_122417_DEC2017.txt
Client_GL_Detail_PBCS_122417_NOV2017.txt
Client_GL_Detail_PBCS_122417_OCT2017.txt
Client_GL_Detail_PBCS_122317_DEC2017.txt
Client_GL_Detail_PBCS_122317_NOV2017.txt
Client_GL_Detail_PBCS_122317_OCT2017.txt

So, I want to delete all other "OCT" files (and any other "duplicates") except for the latest ones.

Does that make sense?

[aGerman]

What about

Code: Select all

@echo off &setlocal
for %%i in (JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC) do (
  for /f "skip=1 delims=" %%j in ('2^>nul dir /a-d /b /tc /o-d "Client_GL_Detail_PBCS_*_%%i2017.txt"') do ECHO del "%%j"
)
PAUSE

Steffen

[SIMMS7400]

Hi Steffen -

Thank you so much! This works about 99%. I am seeing one weird thing.

If it's just 1 duplicate, it works great. However, if all files have duplicates it doesn't keep the most recent ones.

Let me know you. Here is the before photo:

before.png (15.34 KiB) Viewed 5249 times

After:

after.png (7.91 KiB) Viewed 5249 times

Notice the date modified - thank you!

[aGerman]

The /tc option of the DIR command makes that the output is sorted by the creation date. Just remove this option if the files shall be sorted by the date modified.

Steffen

[SIMMS7400]

Hi Steffen -

That worked like a charm - thank you so much!