Group files by prefix and delete file in a folder that have less number in suffix in the group using-CodePudding

I have written a script to compare all files in a folder that read prefix (first 7 characters) and suffix YYYYMMDD (last 8 characters) and delete a file that have less value in suffix so I have only latest file in that folder.

I need to run this script every 28 days so I can drop new files in the folder and delete the file(s) that have less value in suffix (grouped by prefix). The script is not throwing any error but it's not deleting the old file either.

I have attempted to write a script as below to read the prefix and suffix of each file and group by prefix and sort by suffix, skip one file and delete the old one. The file name format is as below

The file name examples are as below YRMGN02_AIRAC_20221201.xlsx
YRMGN02_AIRAC_20230323.xlsx
MMLSR01_AIRAC_20221201.xlsx
MMLSR01_AIRAC_20230323.xlsx

$ErrorActionPreference = "Stop"
Set-StrictMode -Version 3

 

$List = get-childitem *.xlsx
ForEach($File In $List)
{
    $IDENT = $File = {$_.BaseName.SubString(0,7)}
    $AIRAC = $File = {$_.BaseName.SubString(14,7)}
    Group-Object $IDENT
    sort $AIRAC -Descending    
    Select -Skip 1
    Remove-Item $File -ErrorAction Stop
  }

CodePudding user response：

I believe you're looking for something like this, see the inline comments. Note the use of -WhatIf, if you consider the code is doing what you expected you can remove it to actually delete the files.

# get all .xlsx files
Get-ChildItem -Filter *.xlsx |
    # Group the files by their Prefix
    Group-Object { $_.BaseName.Substring(0, 7) } |
    # for each group of files
    ForEach-Object {
        # sort them by their suffix in Descending order (newest goes first)
        $_.Group | Sort-Object { $_.BaseName.SubString(14, 7) } -Descending |
            # skip the first file (the newest one) and output the rest
            Select-Object -Skip 1
    # all output gets removed
    } | Remove-Item -WhatIf