Rename files under FTP site

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?
LVL 5
bfuchsAsked:
Who is Participating?
 
aranaCommented:
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)
$BkpPath="/yourBackupPath"
            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.RemoveFile($fullNewName)
                $session.MoveFile($fullName, $fullNewName)
            }

Open in new window


rinse and repeat each time before Caspio runs its importing routine
0
 
aranaCommented:
Can you give us an example of how files look now, and how is caspio expecting the file names ?
0
 
bfuchsAuthor Commented:
Hi,
See attached how files look like.
Will try to get screenshot of what Caspio bridge is expecting.
Thanks,
Ben
Untitled.png
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
bfuchsAuthor Commented:
See attached import screen from Caspio.

Thanks,
Ben
Untitled.png
0
 
David Johnson, CD, MVPOwnerCommented:
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.
0
 
aranaCommented:
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.
0
 
bfuchsAuthor Commented:
Hi Experts,

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

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

@arana,
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?

Thanks,
Ben
0
 
bfuchsAuthor Commented:
@Arana,

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..

Thanks,
Ben
0
 
bfuchsAuthor Commented:
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.
Ben
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.