Rename files under FTP site

bfuchs used Ask the Experts™
Hi Experts,

We have a third party system that generates a file in a FTP server every 15 min.
Now we would like to import this file into Caspio bridge.
Caspio bridge has the capability to add under scheduled tasks to import files from a FTP server.

However I'm facing the following issue.
Caspio bridge expects a fixed name file, while the system which generates files to the FTP server is set to add time stamp into the names of the files created.

Wondering what is the solution?
Perhaps you can help me put together some code that will rename files under that FTP site?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Can you give us an example of how files look now, and how is caspio expecting the file names ?
See attached how files look like.
Will try to get screenshot of what Caspio bridge is expecting.
See attached import screen from Caspio.

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2016

my way of thinking is you ftp the files to a folder, after ftp closes or you use a filewatcher service you then copy the received file to another folder (if the copy completes you rename the file in the destination folder then run the caspio input routine.
File path and name in your screen looks like a user entered value, what prevents you from modifying that to the filename on the ftp site?, or is it that you dont want to be changing the filename every time?
Caspio image does not show full filename but from what you said in your question I assume caspio does not like the last part of the filenames (the "_nnnnnnnnnnnnnn"  underscore and 14 digits  it wants only this "ENTOUT_613_PATContractChanges.csv"  and not this "ENTOUT_613_PATContractChanges_20180226123006.csv" , and same rule for all the other files correct?
In  that case I would suggest using some command line ftp utility like WinSCPit has an included POWERSHELL script exactly for this purpose, you can schedule the script to run before your import  so it will rename the files
the reason to use WinSCP and not regular windows ftp is that windows ftp doesn't support renaming many files with wildcards (and PS takes care of  reg exps replacing)
here are is the regexp you would need

for search  :               (.*)(_[\d]{14})([\.]csv)
and for replace         $1$3

This will turn: ENTOUT_613_PATContractChanges_20180226123006.csv
to   :                  ENTOUT_613_PATContractChanges.csv

and   ENTOUT_613_SchChanges_20180226123006.csv
to       ENTOUT_613_SchChanges.csv

etc. for all your files in the ftp server
you can change the script so it doesn't require user input.
Hi Experts,

First excuse me for the late response, just was not in the office today.

Do you have example of code to demonstrate how to use Filewatcher for renaming files on FTP?

what prevents you from modifying that to the filename on the ftp site?
This is done by a third party software company, and so far they dont agree to customize this feature for us..

I assume caspio does not like the last part of the filenames
Not exactly, the thing is they need something consistant, and this timestamp keeps changing according to the date/time it was created.

the reason to use WinSCP and not regular windows ftp is that windows ftp doesn't support renaming many files with wildcards
How about if we use fixed length to be renamed, meaning in the ENTOUT_613_PATContractChanges_20180226123006.csv example, after 28 characters everything should get truncated?

Sorry for asking this but since I'm not familiar with scripting language, would you mind posting the full code for it?


Just curious if that software will also be able to copy files to a different folder and delete old files?

Otherwise I dont see how just renaming will solve our problem as we have many files with same name and datestamp on it, as every 15 min another file gets created..

Script will not copy or move any files at the server as it is now , it is just for renaming them, as the original question asks,
you can however ajust the script to

delete all files that do not have a timestamp
download the files, or move them  or copy them to another folder (for backup purposes)
rename the files in the server path that have a timestamp for Caspio to be able to import
let Caspio do its importing

here is an untested patch for the code in that script that will copy the files to another path (remote) before renaming them (replace the for each part code in the original script with this)
            foreach ($file in $files)
				$fullBkpName = [WinSCP.RemotePath]::CombinePaths($BkpPath, $File)
				$session.CopyFile($fullName, $fullBkpName)
                $newName = $file -replace $pattern, $replacement
                Write-Host "$file => $newName"
                $fullName = [WinSCP.RemotePath]::CombinePaths($remotePath, $file)
				$fullBkpName = [WinSCP.RemotePath]::CombinePaths($BkpPath, $File)
				$session.duplicateFile($fullName, $fullBkpName)
                $fullNewName = [WinSCP.RemotePath]::CombinePaths($remotePath, $newName)
                $session.MoveFile($fullName, $fullNewName)

Open in new window

rinse and repeat each time before Caspio runs its importing routine
Thanks arana,
I'm not able to test it due to personal urgency, however looks like this will serve the purpose..
Sorry for late response.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial