Deleting remote .csv files with timestamp in their names older than 2 days

Question

I am trying to delete remote (.csv) files in the FTP server older than 2 days files.

The files do not have their last modification time set correctly. I have to rely on a timestamp in their names.

Naming of the file is like Sales_201705010315.csv (date and time).

My current WinSCP script is:

option batch on
option confirm off
open login ftp credentials
cd /OUT
rm *<1D
exit

When I run the script, files are not deleting. Can someone please correct my scripting

How are you running the .txt file? It's seems like WinSCP script, but you did not mention WinSCP + What is the question? The script looks good. If it does not work as you would like, you have to tells us, what is wrong! + Though why do you use `*<1D`, if you want to select 2 days old files? + Can you really select the files by their modification time? Because your post indicates the files have timestamps in their names. That's not the same as file modification time, that's used by the `*<1D`. Do the file modification times match the timestamp in the file names or not? — Martin Prikryl, May 02 '17 at 20:51
Hi I am running the .txt file using using sales.bat file. The txt script is running fine except for rm section. At first I gave rm *.csv then all the files have been removed. My question is I need to remove 2 days old files. can you help me how to remove those files based on the my file name (sales_201705010315) — swetha, May 03 '17 at 18:52
So it's not possible to select the files based on file modification time? — Martin Prikryl, May 03 '17 at 18:56
I can select them, but I run the batch file everyday automated — swetha, May 03 '17 at 19:04
I understand. I've mean "select automatically by file timestamp". OK, I will assume it's not possible. — Martin Prikryl, May 04 '17 at 07:40

Martin Prikryl · Answer 1 · 2019-06-25T07:56:07.017

This will indeed delete files "older than 1 day" (not 2 days):

rm *<1D

See file mask with time contraints.

But that syntax uses file modification time.

See also Delete files older than X days from FTP server with PowerShell or batch file.

If you need to select the files based on timestamp in their names, it's more complicated.

It's easy to delete files with a timestamp 2 days old:

rm Sales_%TIMESTAMP-2D#yyyymmdd%????.csv

This uses %TIMESTAMP% syntax with a relative time. The syntax will make the command resolve to (as of 2017-05-04):

rm Sales_20170502????.csv

But that won't delete files 3 and more days old. That's not a problem, if you run the script regularly every day. If you want to cater for 1 or few days of outages, you can delete files with timestamp 2, 3, 4... days old like:

rm Sales_%TIMESTAMP-2D#yyyymmdd%????.csv
rm Sales_%TIMESTAMP-3D#yyyymmdd%????.csv
rm Sales_%TIMESTAMP-4D#yyyymmdd%????.csv
...

If you really want to delete all files with timestamp 2 and more days old, you have to write the script in a more powerful language.

Example in PowerShell with use of WinSCP .NET assembly:

# Load WinSCP .NET assembly
Add-Type -Path "WinSCPnet.dll"

# Setup session options
$sessionOptions = New-Object WinSCP.SessionOptions

# Set up session options
$sessionOptions = New-Object WinSCP.SessionOptions -Property @{
    Protocol = [WinSCP.Protocol]::Ftp
    HostName = "ftp.examle.com"
    UserName = "username"
    Password = "password"
}

# Connect
Write-Host "Connecting..."
$session = New-Object WinSCP.Session
$session.Open($sessionOptions)

Write-Host "Listing files..."
$remotePath = "/OUT"
$files = $session.ListDirectory($remotePath).Files

$prefix = "Sales_"
$twoDaysBack = (Get-Date).AddDays(-2)
$timestamp = $twoDaysBack.ToString("yyyyMMdd")

foreach ($file in $files)
{
    if (($file.Name.Length -gt ($prefix.Length + $timestamp.Length)) -and
        ($file.Name.SubString(0, $prefix.Length) -eq $prefix) -and
        ($file.Name.SubString($prefix.Length, $timestamp.Length) -le $timestamp))
    {
        $path = [WinSCP.RemotePath]::EscapeFileMask($file.FullName)
        $session.RemoveFiles($path).Check()
        Write-Host "Deleted $($file.Name)"
    } 
}

Write-Host "Done"

score 0 · Answer 2 · answered May 02 '17 at 23:29

0

Be aware that the file on the FTP server will have the date/time the file was created on the FTP server, not the original file's date/time. So if your file was transferred by some automated task that is run overnight the FTP server date/time may be different. If the date/time on the FTP server isn't synced to a time server you'll run into the same problem. And you can experience the problem if the FTP transmitting machine and receiving machine are in different time zones.

answered May 02 '17 at 23:29

thx1138v2

566
3
6

*"Be aware that the file on the FTP server will have the date/time the file was created on the FTP server, not the original file's date/time"* - How do you know? That depends on the uploader. – Martin Prikryl May 03 '17 at 05:54
got it. I tried to run as rm %%timestamp -1d#yyyymmddhhss%%. But this also didn't delete those files. Can you help me how to change the script to match – swetha May 03 '17 at 19:01
"How do you know?" Judging by his deletion code I'm assuming he's using something similar to upload the files and that would be an FTP put. He didn't mention any other software but that's a valid point. – thx1138v2 May 03 '17 at 22:57
Can you keep a log on the client side of the files uploaded and then run through that, pull the date/time out of the file name, and delete based on file name rather than a date calculation on the FTP server? – thx1138v2 May 03 '17 at 23:02
@thx1138v2 The script uses WinSCP, whose `put` **does** update the uploaded timestamp to match the source file - Though the download script says nothing about the way the files were uploaded, so we do not know about that. – Martin Prikryl May 04 '17 at 06:09

Deleting remote .csv files with timestamp in their names older than 2 days

2 Answers2

Linked