Extract dates from filenames then process files by date in ascending order - Powershell

Question

First of all I'll supply my code and then explain.

$toImport = gci "\\server\folder\*" -include "Examplefile*.txt"
$toImport = $toImport.fullname

$date_of_file = @()

foreach($item in $toImport){
    $date_of_file += (split-path $item -leaf).substring(11,6) 

}

write-host ($date_of_file | sort-object)

I am getting filenames with get-childitem, then just putting their full path into an array. My $toImport variable holds the filepaths and it looks like:

\\server\folder\Examplefile010220.txt
\\server\folder\Examplefile010320.txt
\\server\folder\Examplefile010420.txt
\\server\folder\Examplefile010520.txt
\\server\folder\Examplefile120919.txt

I then use split-path on it to get just the filename where I can run .substring() on it to extract the dates and then I add those dates into variable $date_of_file, which using the above example will hold these values... 010220 010320 010420 010520 120919

I tried to then sort these dates in ascending order because I have a later part of my script that will process the files but they must be done in order.

Some things I've tried to fix the above posted script: (get-date -date $date_of_file | sort-object)

($date_of_file.ToString('MMddyy') | sort-object)

$date_of_file += ((split-path $item -leaf).substring(11,6) ).toString("MMddyy")

$date_of_file += get-date -date ((split-path $item -leaf).substring(11,6) )

($date_of_file | Sort-Object { $_. -as [datetime] })

$date_of_file += [datetime]::parseexact(((split-path $item -leaf).substring(11,6) ), 'MMddyy', $null).ToString('MMddyy')

How can I get this to sort by date in ascending order? So far every time I try something I'm running into errors or this date is still at the end of the list, '120919', when it should be first.

================= UPDATE

$toImport = gci "\\server\folder\*" -include "Examplefile*.txt"
$toImport = $toImport.fullname
cls

$date_of_file = @()

foreach($item in $toImport){
    $date_of_file += [datetime]::parseexact(((split-path $item -leaf).substring(11,6) ), 'MMddyy', $null)

}

write-host ($date_of_file | Sort-Object)

This bit seems to work but it's putting the format of the dates into a format that looks like Thursday, January 2, 2020 12:00:00 AM. and I tried to do .ToString('MMddyy') on the result but I'm getting this error Cannot find an overload for "ToString" and the argument count: "1".

Answer 1

A concise and efficient solution that directly sorts the System.IO.FileInfo instances output by Get-ChildItem in the chronological order implied by the MMddyy-formatted date strings embedded in the file names:

Get-ChildItem \\server\folder -Filter Examplefile*.txt |
  Sort-Object { $_.BaseName -replace '.+(\d{2})(\d{2})(\d{2})', '$3$1$2' }

• Get-ChildItem \\server\folder -Filter Examplefile*.txt:

  • -Filter is the most efficient way to find files matching a given wildcard pattern, because it filters at the source (the filesystem) rather than retrieving all items first and then making PowerShell filter after the fact (which is what happens with -Include).

• Sort-Object { $_.BaseName -replace '.+(\d{2})(\d{2})(\d{2})', '$3$1$2' }:

  • Passing a script block ({ ... }) to Sort-Object's (implied) -Property parameter causes the script block to be evaluated for each input object, with the input object at hand reflected in automatic variable $_.

  • The -replace operation uses a regex-based string substitution that effectively rearranges the embedded date string from MMddyy into yyMMdd format and sorts based on the latter, so that lexical sorting equals chronological sorting.

Answer 2

The accepted answer seems like a great solution. Here is my updated answer giving you a sorted array of [pscustomobjects] with the filenames included that you can then reference back to your filenames with $date_of_file.fileNM

$toImport = gci "server\folder\*"
$toImport = $toImport.fullname

$date_of_file = @()

foreach($item in $toImport){
    $date_of_file += [pscustomobject]@{
                      "fileNM" = $item;
                      "dispDate" = (split-path $item -leaf).substring(11,6);
                      "dateStamp"=  [datetime]::parseexact((split-path $item -leaf).substring(11,6),'MMddyy', $null)
                      }
    }

$date_of_file = $date_of_file | Sort-Object -Property dateStamp

$date_of_file.dispDate

----------- Old answer below -----------

This works for me:

$date_of_file = @()


$date_of_file +=  [datetime]::parseexact("010220", 'MMddyy', $null)
$date_of_file +=  [datetime]::parseexact("010320", 'MMddyy', $null)
$date_of_file +=  [datetime]::parseexact("010420", 'MMddyy', $null)
$date_of_file +=  [datetime]::parseexact("010520", 'MMddyy', $null)
$date_of_file +=  [datetime]::parseexact("120919", 'MMddyy', $null)


$date_of_file | Sort-Object {$_ -as [datetime]}

Answer 3

i think you may have a problem in what you are SEEING. your sorted list should have the 120919 file at the END of the "newest first" listing since the others are all in 2020, not 2019.

however, here is my version of a solution to the problem. the "magic" in this case is getting only the numbers from the end of the .BaseName of the file. since you threw that away, i had to use a split and a regex. [grin]

$FileNameList = @(
    '\\server\folder\Examplefile010220.txt'
    '\\server\folder\Examplefile010320.txt'
    '\\server\folder\Examplefile010420.txt'
    '\\server\folder\Examplefile010520.txt'
    '\\server\folder\Examplefile120919.txt'
    )

$FileNameList |
    # this would be far easier if you have left things as fileinfo objects [*grin*] 
    Sort-Object -Descending -Property {
        [datetime]::ParseExact(($_.Split('.')[0] -replace '.+(\d{6})', '$1'), 'MMddyy', $Null)
        }

output ...

\\server\folder\Examplefile010520.txt
\\server\folder\Examplefile010420.txt
\\server\folder\Examplefile010320.txt
\\server\folder\Examplefile010220.txt
\\server\folder\Examplefile120919.txt

note that the last file is from 2019 and the others are all from 2020. [grin]

Answer 4

This might work for you. It adds a SortableName property which is yyddMM, then sorting by that property:

dir "\\server\folder\*" -include "Examplefile*.txt" | `
  Select-Object -Property *, @{ Name = 'SortableName'; Expression = {$_.BaseName.Substring(15) + $_.BaseName.Substring(11,4) }} | `
  Sort-Object -Property SortableName | Select-Object -Property FullName