Moving and renaming downloaded files to another directory in WinSCP scripting

Question

I have created the following files that do specific tasks as indicated:

• DataCollection.bat - Opens WinSCP and runs command in the dailyimport.txt
• Dailyimport.txt - logs into remote FTP site and retrieves three *.csv files, renaming them in the destination folder.

Later a PHP script is executed to import the data in the three CSV files into my database.

What I need to do now, and where I am having a problem is:

• Moving the downloaded files (Now on local host) into a new directory, and again renaming them, by keeping the file name and adding a timestamp.
• Moving the files on the FTP server into a new directory.

Please see my code I have below:

DataCollection.bat:

@echo off
winscp.com /script=dailyimport.txt
if %ERRORLEVEL% neq 0 goto error

echo Upload succeeded, moving local files
move "C:\DataImport\modules_flat_file_footer.csv" "C:\DataImport\Imported\modules_flat_file_footer.csv.%TIMESTAMP#yyyymmss%"
exit /b 0

:error
echo Upload failed, keeping local files
exit /b 1

dailyimport.txt:

# Connect
open ftp://myftpdetails/
# Change remote directory
cd /downloads/MySQL
# Force binary mode transfer
option transfer binary
# Download file to the local directory
get modules_flat_file_footer_*.csv "C:\DataImport\modules_flat_file_footer.csv"

# Disconnect
close
# Exit WinSCP
exit

Answer 1

• The %TIMESTAMP#yyyymmss% is a feature of WinSCP, you cannot use it in Windows batch file directly. Though you can run winscp.com from the batch file to format the timestamp. See the code below and WinSCP example Formatting Timestamp in Batch File.

• You are downloading three files to a single local name (modules_flat_file_footer.csv). So you actually lose two of them.

---

You can use a batch file like this:

@echo off

set SESSION=ftp://username:password@ftp.example.com/
set REMOTE_PATH=/downloads/MySQL
set ARCHIVE_PATH=/remote/archive/path
set LOCAL_PATH=C:\DataImport
set IMPORTED_PATH=C:\DataImport\Imported
set MASK=modules_flat_file_footer_*.csv

winscp.com /ini=nul /log=download.log /command ^
    "open %SESSION%" ^
    "get %REMOTE_PATH%/%MASK% %LOCAL_PATH%\*" ^
    "exit"

if errorlevel 1 (
    echo Error downloading
    exit /b 1
)

php import.php  

if errorlevel 1 (
    echo Error importing
    exit /b 1
)

for /F "tokens=* USEBACKQ" %%F in (`winscp.com /command "echo %%TIMESTAMP#yyyymmss%%" "exit"`) do (
    set STAMP=%%F
)

for %%F in (%LOCAL_PATH%\%MASK%) do (
    move %%F %IMPORTED_PATH%\%%~nxF.%STAMP%
    if errorlevel 1 (
        echo Error moving %%~nxF
        exit /b 1
    )

    winscp.com /ini=nul /log=archive_%%~nxF.log /command ^
        "open %SESSION%" ^
        "mv %REMOTE_PATH%/%%~nxF %ARCHIVE_PATH%/%%~nxF" ^
        "exit"
    if errorlevel 1 (
        echo Error archiving %%~nxF
        exit /b 1
    )
)

---

Though with such a complex logic, you better use a PowerShell, not batch file.

Use WinSCP .NET assembly from the PowerShell script.

In PowerShell you can also enumerate the list of actually downloaded files, so you are sure to move only the files that were downloaded. This protects you against situation that new file is added to the remote folder between the download and move (transactional safety).

You can base the code these official examples for the WinSCP .NET assembly:

• Downloading file to timestamped-filename
• Moving local files to different location after successful upload