Introduction
When you need to access a specific FTP server and download or upload one or more files, the best option is to use the standard FTP programs, like the one we recommend:
However, when this task has become repetitive, as when we download daily files from multiple OSS (via FTP), then it’s better to automate it - less wasted time, minimize errors, etc…
And a way to automate the execution of FTP is to create a Windows application that performs the work.
Let’s see now so how can this be done?
Goal
Create a basic FTP client that allows us to automate the task of downloading files located on an FTP server.
For today, we will be using Microsoft Access with VBA (although it can be done with several other programs such as Microsoft Excel).
For you that are already used to Hunter System, this FTP Client using VBA is just another module of Hunter, as always just adpting or using the File Structure (and Directories) that already exists.
File Structure
In today’s tutorial, we include a few files and directories to our standard structure.
The procedure for this structure from the file received is the same, so no need to explain again.
How to run an FTP on Windows
There are several ways to do the FTP on Windows, the most basic is through the Command Prompt (‘cmd’).
Just type the command ‘ftp’ followed by the name of the server, and you already see the prompt for you to put user to proceed.
This is basic but it becomes quite interesting when we can use files of type ‘BAT’ to perform the same action - in this case, the sequence of commands we’d type.
A ‘BAT’ file is nothing more than a Text file, only instead of the extension ‘txt’ extension has the ‘BAT’. The files of this type are recognized by the system as a batch file, when opened, trigger the necessary actions - running its commands.
This feature is widely used for example in Drive Test data collection, where you can create files for downloading and upload automatically, while you care only to collect and/or analyze data.
After you log in (username and password), you have access to an FTP prompt. Note: there is also the Anonymous access, but it is not our case because we will always (or at least should have) servers protected with User ID and Password. Anyway, in order to access Anonymously - when permitted - just enter the user ‘Anonymous’.
We can use a lot of commands to perform our actions (like looking for a file).
So let’s know at least the basic FTP commands for today’s application.
List of FTP commands
There are over 40 types of FTP commands, and they can be easily accessed through an Internet search, such as in the following website:
http://www.nsftools.com/tips/MSFTP.htm
An important observation is that we are speaking here of commands to be used on the command line in Windows. There is another list of commands, ‘Raw FTP’ which can be found in the same way, but beyond our scope here today.
Continuing then follows a brief description of the commands used:
- ’ binary ’ (or ‘bin’): use the binary mode in transfers (explanation below).
- ’ hash ’ (or ‘ha’) allows to visually follow the progress of the transfer, through the printing of the sign ‘#’ for each transferred block.
- ’ get ': to get a particular file (download).
- the command ‘mget’ does the same for multiple files.
- ’ put ': a particular file to send (upload).
- the command ‘mput’ does the same for multiple files.
- ’ bye ': to close the FTP connection.
A note about using the command ‘bin’ before performing the transfer. The other option, or transfer mode, is ASCII (command ‘ascii’), which is also the Default mode. The ASCII mode is great for transferring files in text format - and only. In ASCII mode, we have file compression, which makes the download quicker. But when we have other types of files such as images, video or Word documents for example, the transfer mode to be used is the binary, because it transfers the files the way they are, without changes.
Each command has its own syntax. For example, the command ‘get’ has the syntax:
get remote-file [local-file]
In this case, ‘remote-file’ specifies the file name to be searched. If specified, the optional parameter ‘local-file’ specifies the name that this file should have when downloaded. Otherwise - not specified - the file will have the same name as the remote.
For more details about these and other commands, use the link reference as suggested above.
Performing an FTP
Once you know the commands and how to do via the command line, you can manually do a transfer:
- Creating a Text file with the lines to be entered (one command per line);
- Renaming this file to type ‘BAT’;
- And running this file (double click for example).
The Application
Now it’s pretty simple to understand how our application can be created is not it?
Quite simply, it follows the same steps as above, but instead of doing everything manually, using the features of Access and/or Excel with VBA.
Manipulating the files programmatically you can create loops to different servers, different files, and whatever you want.
As an example, we have an application to download GSM and UMTS OSS’s data parameters files, for further parsing through the module that has already been seen:
A module where we show how to extract the data from these downloaded files, and accumulate them in a database).
The application is very simple, but can easily be improved - evolved - to achieve the same results as the best FTP programs available.
Anyway, it meet our current goal.
Basically, you just edit an Excel file with information about each connection (Server, User, etc. …). To edit this file, just click the button for the Main Interface.
Then click on the Exclamation, FTP to process entries.
You can also process only desired specific rows (entries), simply using some kind of filter.
In our case, we use a query: ‘qry_FTPConnections’. This is a simple query, fetching data from table ‘tbl_Config_FTP’ - in fact, our Excel spreadsheet linked, and we have seen several times how to do this.
The macro (VBA) process the records of this query, which in turn, shows only the records whose field at the original file ‘FTPCommand’ is not empty. In short, if you leave this field blank, its entry FTP will not be processed!
So you can keep a list of all possible connections, such as the OSS’s. And when you run, make the desired edits.
Hunter Logs
To improve our experience a little more, we have included a new feature - a Log file just introduced yet another concept that can be replicated in any other module that you use or develop.
As we ran, we write the results (success, failure, among other information) in a simple text file, that we can see the end of execution.
In this application, we have a log file generated for each time that we run, containing all the information from all transactions - or what is more convenient.
Another possibility, which we’ll see soon, is to use a single log file where the information will only be accumulated at the end of it.
VBA Code - And Workarounds Used
The code required, just over 50 lines, was written as always in a very simply way, is fully commented, and using the most appropriate procedures for manipulating files, and some little tricks to circumvent potential problems.
One such problem occurs when in file manipulation, we need to create and delete temporary files. The processing is very fast, and it may be that the system does not have time to provide the file, locking it. In this case, if the file being deleted is not available, we have the error 70 - ‘Permission Denied’.
To solve this problem for example, decided not to delete temporary files, but store them in a separate directory - ‘Transactions’. Somehow this ends up being good, it is possible to see each of these files, in case of any problem.
In such applications, it is common for people to use the same name, and ‘wait’ (pause) for some time, until it is available - released - by the system for manipulation. In our case, in each transaction, we add a suffix - the date and time it was processed. Thus, we find a solution to this type of error. Also, when we do not use fixed pauses - we don’t waste time, ie, to make our application run more quickly.
In short: for each transaction, we have files with a certain date and time. While they are being processed - FTP performing the transaction - we can create other (which will have different date and time!) And continue processing other transactions.
Although it may seem complicated, the solution is simple. And if you wish, you can manually delete the files in the ‘Transactions’ folder or via a button on the Main Interface.
Conclusion
Again, we saw how to use the Hunter Methodology to obtain help in our work.
Today for example, we saw how you can build a ‘FTP Client’ application using Excel, Access and VBA.
As advantages of a custom application like this, we highlight the possibility of automation of repetitive tasks - and therefore unnecessary, or at least capable of being improved.
It is also possible - and easy - to improve or extend the application to meet other specific purposes by simply following the same idea of the main points discussed here and shown in the VBA commented code.
If by chance you find this tutorial interesting, could you share? Here you have quick and easy ways to do this. And again, thank you for your attention.
Download
Download Source Code: Blog_036_Hunter_FTP_Client_(using_Access_Excel_e_VBA).zip (450.2 KB)