2

Kindly see this screen cast to get better idea about our requirement:

https://www.screenr.com/QmDN

We want to automate the Text Datasource Generation and connection to MS Excel in order to make it easier to the end-user to connect to the Text Datasource (CSV) to MS Excel so that they can generate their own reports.

The steps I have in mind:

  1. Use WinSCP FTP Client with Scripting

  2. Write script to get the most recent updated file from FTP Folder

  3. Or instead of step 2, download all generated files from FTP to a Shared Folder on the Network.

  4. Get the most recent version of the Generated CSV File

  5. Rename the file to the Standard Naming Convention. This must be the name used in MS Excel as the CSV Text Datasource.

  6. Delete all other files

I developed sample script that can be used by WinSCP to download the files from FTP folder:

# Automatically abort script on errors
option batch abort
# Disable overwrite confirmations that conflict with the previous
option confirm off
# Connect
open CSOD
# Change remote directory
cd /Reports/CAD
# Force binary mode transfer
option transfer binary
# Download file to the local directory d:\
#get "Training Attendance Data - Tarek_22_10_21_2014_05_05.CSV" "D:\MyData\Business\Talent Management System\Reports\WinCSP\"
get "*.CSV" "D:\MyData\Business\Talent Management System\Reports\WinCSP\Files\"
# Disconnect
close
exit

Then, I can schedule the above code to run periodically using this command:

winscp.com /script=example.txt

The above sample is working fine, but the main problem is how to identify the most recent file, so that I can rename it, and delete all the other files.

Appreciate your help.

Tarek

Martin Prikryl
  • 188,800
  • 56
  • 490
  • 992
tarekahf
  • 738
  • 1
  • 16
  • 42

2 Answers2

3

Just add the -latest switch to the get command:

get -latest "*.CSV" "D:\MyData\Business\Talent Management System\Reports\WinCSP\Files\"

For more details, see WinSCP article Downloading the most recent file.

Martin Prikryl
  • 188,800
  • 56
  • 490
  • 992
1

You don't specify the language you use, here a Ruby script that downloads the most recent file of an FTP path. Just to demonstrate how easy and terse this can be done with a scripting language like Ruby.

require 'net/ftp'

Net::FTP.open('url of ftpsite') do |ftp|
  ftp.login("username", "password")
  path = "/private/transfer/*.*"
  # file[55..-1] gives the filename part of the returned string
  most_recent_file = ftp.list(path)[2..-1].sort_by {|file|ftp.mtime(file[55..-1])}.reverse.first[55..-1]
  puts "downloading #{most_recent_file}"
  ftp.getbinaryfile(most_recent_file, File.basename(most_recent_file))
  puts "done"
end
peter
  • 41,770
  • 5
  • 64
  • 108
  • Thank you. The language I used is the script of the tool WinSCP: http://winscp.net/eng/docs/scripting. I don't know Ruby and no one here knows how to program in Ruby. Appreciate to give example using batch commands (Windows command lines). – tarekahf Oct 26 '14 at 08:00
  • WinSCP is no scripting language, it is a Domain-specific language(at which making Ruby exels because it is so readable). Ruby is the easiest to use general scripting language I know of, it is often used in teaching programming to kids. I advise you to take a look at it. As few minutes it took me to make this working sample in Ruby, it would take me hours to do it in batch, so sorry, i won't do that – peter Oct 26 '14 at 12:24
  • This will do using batch file: http://stackoverflow.com/questions/97371/how-do-i-write-a-windows-batch-script-to-copy-the-newest-file-from-a-directory FOR /F "delims=|" %%I IN ('DIR "*.*" /B /O:D') DO SET NewestFile=%%I So no need to learn a new Language have additional layer of complexity unnecessarily. – tarekahf Oct 28 '14 at 21:17
  • Here is a reply from WinSCP forums: http://winscp.net/forum/viewtopic.php?p=53040#53040 ... it seems there is a solution already. – tarekahf Oct 29 '14 at 13:43