Downloading yesterday files in PowerShell using WinSCP

1

2

Design Scope: Use PowerShell and WinSCP .NET assembly to automate nightly downloads by using the file timestamp to identify the files to be downloaded. The FTP server being connected to is IIS so it does not support all commands like MLSD in it's current configuration and I don't see them making a change if requested.

Problem: Files that have a 1 or 2 digit month return different string lengths that I'm unsure how to get around this. My code works now, but will stop working in October.

e.g. March displays 3/dd/yyyy instead of 03/dd/yyyy

Other notes: At first I tried scripting this using WinSCP.com, but I could not find a way to specify the date correctly.

e.g. you can specify *.zip>=1D or *.zip<=1D, but *.zip=1D or *.zip==1D are not currently supported with the latest release of WinSCP.

Code:

$yesterday = [DateTime]::Today.AddDays(-1).ToString("M/dd/yyyy")
# OR I have to use ToString("MM/dd/yyyy") for months 10-12,
# but I need both formats to work.
#delete the temporary file
del .\FTPfiles.txt
# Load WinSCP .NET assembly
Add-Type -Path "C:\Program Files (x86)\WinSCP\WinSCPnet.dll"

# Setup session options
$sessionOptions = New-Object WinSCP.SessionOptions -Property @{
    Protocol = [WinSCP.Protocol]::Ftp
    HostName = "server.com"
    UserName = "joe"
    Password = "smith"
}

$session = New-Object WinSCP.Session

    try
    {
        # Connect
        $session.Open($sessionOptions)

        $directory = $session.ListDirectory("/Folder")

        foreach ($fileInfo in $directory.Files)
        {
            Write-Output ("{1} {0}" -f
                $fileInfo.Name, $fileInfo.LastWriteTime) >> FTPfiles.txt
        }
        $fileList = get-content .\FTPfiles.txt | findstr $yesterday
        $stripped = $fileList -creplace '^.*Z12', 'Z12'


        # Download files
        $remotePath = "/Folder/"
        $transferOptions = New-Object WinSCP.TransferOptions
        $transferOptions.TransferMode = [WinSCP.TransferMode]::Binary

        $lines = $stripped

        foreach ($line in $lines)
        {
            Write-Host ("Downloading {0} ..." -f $line)
            $session.GetFiles($remotePath+$line, "C:\Downloads\").Check()
        }

    }


catch [Exception]
{
    Write-Host ("Error: {0}" -f $_.Exception.Message)
    exit 1
}
finally
{
    # Disconnect, clean up
    $session.Dispose()
} 

The $fileInfo in $directory.Files writes the last write time and then file name into FTPfiles.txt for all of the files contained on the FTP server from the specified folder. This text file is then read and then further reduced to just the files that have a write time that occurred on yesterday's date, which currently only works 9 months out of the year because of the date format using 1 digit for the month instead of 2 digits.

Next that file is read and stripped of the dates before the filenames so that the filenames will be used to loop through to download. The transformation looks like this:

FROM:
3/14/2017 2:04:00 AM Z1234_20170314050001_1.zip
3/14/2017 3:04:00 AM Z1234_20170315060002_1.zip
3/14/2017 4:04:00 AM Z1234_20170316070001_1.zip
3/14/2017 5:04:00 AM Z1234_20170317080001_1.zip

TO:
Z1234_20170314050001_1.zip
Z1234_20170315060002_1.zip
Z1234_20170316070001_1.zip
Z1234_20170317080001_1.zip

Then the script uses those filenames to download the needed files from the previous day.

Brad

Posted 2017-03-15T22:03:48.503

Reputation: 241

2I don't see the problem ATM, the format [DateTime]::Today.AddDays(-1).ToString("M/dd/yyyy") will expand to a two digit month in October, the one M just means no leading zero? – LotPings – 2017-03-15T22:51:15.970

@LotPings is right: e.g. [DateTime]::Today.AddDays(-120).ToString("M/dd/yyyy") returns (cca four months ago date) 11/15/2016 with two-digit month. BTW, you don't need to create a temporary file and use external findstr.exe tool. You can check $fileInfo.LastWriteTime directly in PowerShell using appropriate regex and -match operator… – JosefZ – 2017-03-15T23:12:16.120

@LotPings is correct, the .NET string formatting will handle that just fine.

[datetime]::Now.AddDays(200).ToString("M/dd/yyyy") will verify (currently puts you into Oct.

Or use string format MM for always getting 2 digits – Austin T French – 2017-03-16T03:42:19.563

Answers

1

You can use today and yesterday keywords for upper and lower time constraint in a single WinSCP file mask:

$transferOptions = New-Object WinSCP.TransferOptions
$transferOptions.FileMask = "*>=yesterday<today"
$session.GetFiles("/Folder/*",  "C:\Downloads\", $False, $transferOptions).Check()

This needs WinSCP 5.15 and newer.


In older versions of WinSCP, you can generate timestamps in your PowerShell code, like:
*>=2017-03-15<2017-03-16

(midnight times are implied, that's also why *=2017-03-15 is not, what you want; and also the reason why it's not implemented, as it would not be of any use)

In PowerShell, you can implement it like:

$yesterday = (Get-Date).AddDays(-1)
$yesterday_timestamp = $yesterday.ToString("yyyy-MM-dd")
$today = Get-Date
$today_timestamp = $today.ToString("yyyy-MM-dd")

$transferOptions = New-Object WinSCP.TransferOptions
$file_mask = "*>=$yesterday_timestamp<$today_timestamp"
$transferOptions.FileMask = $file_mask

$session.GetFiles("/Folder/*",  "C:\Downloads\", $False, $transferOptions).Check()

See WinSCP article on formatting timestamps in PowerShell.

Though you can also use WinSCP %TIMESTAMP% syntax for a simpler implementation:

$transferOptions = New-Object WinSCP.TransferOptions
$transferOptions.FileMask = "*>=%TIMESTAMP-1D#yyyy-mm-dd%<%TIMESTAMP#yyyy-mm-dd%"
$session.GetFiles("/Folder/*",  "C:\Downloads\", $False, $transferOptions).Check()

See the WinSCP article about Downloading the most recent file.

Martin Prikryl

Posted 2017-03-15T22:03:48.503

Reputation: 13 764

When I use $session.GetFiles("/Folder/*" it's recursively downloading child folders too. How can I exclude folders? – Brad – 2017-03-22T18:45:55.077

I can filter to *.zip which works, but if I needed everything from the specified folder without being recursive, how can I exclude this? – Brad – 2017-03-22T18:51:15.420

See WinSCP FAQ How do I transfer (or synchronize) directory non-recursively?.

– Martin Prikryl – 2017-03-22T19:30:32.027

1

Try this function and pass your specified date range in parameter StartDate and EndDate

Function Download-Files 
{ 
    [CmdletBinding()] 
    Param 
    ( 
        [Parameter(Mandatory=$true)][Object]$Session, 
        [Parameter(Mandatory=$true)][String]$RemotePath, 
        [Parameter(Mandatory=$true)][String]$LocalPath, 
        [Parameter(Mandatory=$false)][String]$StartDate, 
        [Parameter(Mandatory=$false)][String]$EndDate 
    ) 

    If (-Not (Test-Path -Path $LocalPath -PathType Container)) { 
        New-Item -Path $LocalPath -ItemType Directory 
    } 

    Get-WinSCPChildItem -WinSCPSession $Session -Path $RemotePath | ForEach-Object { 
        If (-not($_.IsThisDirectory) -and -not($_.IsParentDirectory) -and $_.IsDirectory) { 
            Download-Files -Session $Session -RemotePath "$RemotePath\$($_.Name)\" -LocalPath "$LocalPath\$($_.Name)\" -StartDate $StartDate -EndDate $EndDate 
        } 

        If (-not($_.IsDirectory)) { 
            If ($_.LastWriteTime -ge $StartDate -and $_.LastWriteTime -le $EndDate) { 
                Receive-WinSCPItem -WinSCPSession $Session -Path "$RemotePath\$($_.Name)" -Destination $LocalPath 
            } 
        } 
    } 
}

You can download the complete code sample from How to download recent files in PowerShell by WinSCP

Eric

Posted 2017-03-15T22:03:48.503

Reputation: 141