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 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
bfuchsAuthor Commented:
See attached import screen from Caspio.

Thanks,
Ben
Untitled.png
0
Determine the Perfect Price for Your IT Services

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

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.