Sort file in SFTP based on datetime using PowerShell

Question

I have multiple files in the SFTP folder that were created at the same time but at different dates in their filenames. Example file:

REGISTRATION_ELI_20210422_071008.csv 
REGISTRATION_ELI_20210421_071303.csv
REGISTRATION_ELI_20210420_071104.csv

I want to copy 1 file with today's date in its filename and send it to local. Which property can I use to list files and copy the one that matches today's date?

#Setting credentials for the user account
$password = ConvertTo-SecureString "password" -AsPlainText -Force
$creds = New-Object System.Management.Automation.PSCredential ("gomgom", $password)

$SFTPSession = New-SFTPSession -ComputerName 172.16.xxx.xxx -Credential $Credential -AcceptKey

# Set local file path and SFTP path
$LocalPath = "D:\WORK\Task - Script\20221010 - AJK - ITPRODIS380 - upload file csv ke sql server\csvfile"
$SftpPath = '/Home Credit/Upload/REGISTRATION_ELI_*.csv'
    
Get-SFTPItem -SessionId $SFTPSession.SessionID -Path $SftpPath -Destination $LocalPath | Sort-Object {[datetime] ($_.BaseName -replace '^.+_(\d{4})(\d{2})(\d{2}) (\d{2})(\d{2})', '$1-$2-$3 $4:$5:')  } | Select-Object Name
    
Remove-SFTPSession $SFTPSession -Verbose

Answer 1

You can try to change your $SftpPath like this :

$SftpPath = "/Home Credit/Upload/REGISTRATION_ELI$([datetime]::Now.toString('yyyyMMdd'))_*.csv"

I introduce the date in he path you look for :

/Home Credit/Upload/REGISTRATION_ELI20221221_*.csv

---

You can perhaps solve your problem by first listing remote files and them download the one you want by date. I don't test but it gi can give something like that :

$SftpPath = '/Home Credit/Upload'
$SftpPath = "/Home Credit/Upload/REGISTRATION_ELI$([datetime]::Now.toString('yyyyMMdd'))_*.csv"
$Files = (Get-SFTPChildItem -SessionId $Session.SessionId -Path "$SftpPath") | where {$_.name -like $SftpPath}
foreach ($file in $files)
{
  Get-SFTPItem -SessionId $SFTPSession.SessionID -Path $file -Destination $LocalPath
}