Got it, let's tackle this problem step by step. Your scenario—hourly updated Excel files where you only need to retain the last one of each day—requires a script that groups files by date, identifies the latest in each group, and deletes the rest. Here's a tailored batch script for this:

Batch Script Implementation #

@echo off
setlocal enabledelayedexpansion

:: -------------------------- CONFIGURATION --------------------------
:: Set your target folder path here
set "targetDir=C:\mypath"
:: Set your Excel file extension (e.g., .xlsx, .xls)
set "fileExt=.xlsx"
:: Adjust the date extraction logic based on your filename structure!
:: Example filename: XXXX_XX_202405201400.xlsx (datestamp ends with YYYYMMDDHHMM)
:: We'll extract the first 8 characters of the 12-digit timestamp (YYYYMMDD)
set "dateExtractPattern=!filename:~-16,8!"  :: Tweak these numbers to match your filename
:: -------------------------------------------------------------------

:: Group files by their extracted date
for /f "delims=" %%f in ('dir /b /a-d "%targetDir%\*%fileExt%"') do (
    set "filename=%%f"
    :: Extract the date segment from the filename
    set "fileDate=!dateExtractPattern!"
    :: Append the file to the corresponding date group
    set "files_!fileDate!=!files_!fileDate! %%f"
)

:: Process each date group to keep only the latest file
for /f "tokens=1,* delims==" %%d in ('set files_') do (
    :: Sort the files in the group (assumes timestamp in filename is ascending; use sort /r for descending)
    set "keepFile="
    for /f "delims=" %%f in ('echo %%b ^| tr " " "\n" ^| sort') do (
        set "keepFile=%%f"
    )
    
    :: Delete all files in the group except the latest one
    for %%f in (%%b) do (
        if not "%%f"=="!keepFile!" (
            echo Marked for deletion: "%targetDir%\%%f"
            :: Uncomment the line below to enable actual deletion after testing
            :: del /f /q "%targetDir%\%%f"
        )
    )
)

endlocal
echo Daily cleanup task completed!

Critical Notes for Customization #

• Date Extraction: This is the most important part—you must adjust the dateExtractPattern to match your actual filename structure. For example:
  • If your filename is Report_Sales_2024-05-20_1500.xlsx, use set "fileDate=!filename:~-16,10!" to grab the 2024-05-20 segment.
  • Test the extraction by adding echo !fileDate! inside the first loop to verify it's capturing the correct date.
• Testing First: The script uses echo to show which files would be deleted. Run it once to confirm the correct files are marked, then remove the echo and uncomment the del command to perform actual deletion.
• Sorting Logic: If your filenames use a descending timestamp (e.g., latest file has the smallest number), replace sort with sort /r to reverse the order and pick the correct latest file.

How It Works #

1. Group Files by Date: The script first loops through all Excel files, extracts the date from each filename, and groups files into batches by their date.
2. Identify Latest File: For each date group, it sorts the filenames (using the timestamp in the name) and keeps track of the last one (the most recent update).
3. Delete Unwanted Files: It then deletes every file in the group except the latest one you want to retain.

内容的提问来源于stack exchange，提问作者PotatoeBAM