Illuminate receives a lot of questions from our smaller clients now exploring the wonders of data - specifically, on assistance/ways to set up automation of files from our SFTP server to their local machine/server. Using a GUI client such as Cyberduck/Filezilla is cool, but just not cool enough because it's all manual (login > download > logout > etc). Cool people sit around, do nothing, and let technology do everything. Luckily, we’ve got the following notes below to help. Please note this is provided as a generic guide and your market may vary! We will try our best to assist, but this is ultimately the responsibility of the client.
There are a few concerns that we want everyone to be aware of before diving in:
- "I'm network security master! This technique is too primitive. It exposes my login/password, etc." Yes, it is primitive and yes, it exposes your username/password in a plain text file. This was meant to be as simple as possible for all of our end-users. There are more difficult and secure ways (i.e. authentication keys, etc.) that anyone is welcome to explore and research on their own.
- This is free advice: You can definitely google "How to automate from an SFTP server" and you'll get millions of hits on various ways to set up automation. Stick with it below or filter, learn and refine!
- Times change: Windows has gone through many versions. So, there's potential that your version of Windows may need slight changes to the script. The scripts below were confirmed to work on WinXP and Win7. Google is your best friend!
- "What you stated below doesn't work! I'm frustrated! Can Illuminate fix these instructions/doc?!": Read bullet point #2 above. We don't know all the technologies of this world. We're just trying to help and we only get paid in gum and peanuts!
The following commands can be used via a .bat script to automate (using an automation app such as Window Task Scheduler) files from our SFTP server into their local server. FYI, this script is made for a scenario where your local machine is running on Windows and attempting to pull a file from Illuminate’s SFTP server. Pay special attention to the forward/backward slashes in the commands below, as Windows uses backward slashes vs. Linux (our sftp server) using forward slashes.
It's required that you must download psftp from http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html. Please download the psftp.exe.
This is the 1st file. Create a .bat (batch) file and copy/paste the following two lines into it. You can name this batch file anything you wish (ie. lunch_extract.bat, etc).
Line1: Depending on where you downloaded and are storing the psftp.exe file, please specify the path to that file. For example, if I download the file into a folder at this path "C:\myscripts\", then my first line would be:
1: set PATH=C:\myscripts\psftp.exe
Line2: With the sftp info that Illuminate has provided you, replace the "user_name" and "password" with what you were provided. At the end of the line, there is an option -b in which you specify what script file to run once connected to the sftp server. You can rename the "myextractionscript.scr" to whatever you want (ie. lunch_extract.scr, mealtime_extract.scr, etc) or keep the generic myextractionscript.scr. A finished example would be:
2: psftp sftp.illuminateed.com -l "illuminate" -pw "ru1es" -b myextractionscript.scr
Your completed file should have the following two lines:
psftp sftp.illuminateed.com -l "user_name" -pw "password" -b myextractionscript.scr
2. This is the 2nd file. Create a .scr (script) file and copy/paste the following 4 lines into it. Please be sure to name this .scr file according to what you stated in file1 line2 above. For example, in file1 line2 above, I specified "-b myextractionscript.scr", then I want to name this file "myextractionscript.scr”. The purpose of this file is to script the steps, once file1 has successfully logged you onto the server.
Line1: The first line here is to specify the path, on your local machine/server, of where you want the files to download to. If for example, I want the file(s) to download to the path "C:\myscripts\lunch_data\", my completed line1 would be:
1: lcd C:\myscripts\lunch_data\
Line 2: Specify the path on the sftp server where your file(s) are located. If you don't know, use your GUI sftp client (Cyberduck, Filezilla) to login and browse to your expected folder. You will find your file path in the URL somewhere. For example, if my file(s) are in this location (~/data_extracts/meal_time/), line2 would be:
2: cd data_extracts/meal_time/
Line 3: Specify what file(s) you wish to "get" and download back into your line1 path. If there are multiple files, use the asterisk: get *. Otherwise, for example, if I wanted to download one file (ie. mealtime.txt), line3 would be:
3: get mealtime.txt
Line 4: Quit/end the session.
Your completed file should look have the following 4 lines, with directory paths as you spec’d:
3. From here, you can do a few test runs by executing the batch script (file1). Simply double click the batch script and it should execute within Windows. If properly set, it should download the correct file(s) down to your machine, to that directory you specified in the “lcd” command. If it doesn't work or there are errors, troubleshoot.
Lastly, and once the scripts above are confirmed to be working, if you wish to set up automation, simply use Windows Task Scheduler to point to the batch file and schedule the times/dates/intervals. The Task Scheduler will execute that batch script at the time/date/interval that you've set up.