TECH TIP: Campaign Finance
Setting up an automated FTP download.

By Griff Palmer
San Jose Mercury News

When looking for an FTP client, power users often cast aside the lowly command line client bundled with Microsoft’s Windows 32-bit operating systems in favor of jazzier GUI clients.

Beneath the Win32 client’s drab exterior, though, is a program with scripting capabilities to rival programs from the Unix side of the tracks. Database software FoxPro, with its own scripting abilities and its ability to call other programs, makes a perfect complement for this deceptively plain-clothed client.

Any command that can be invoked from the FTP command line can be written into a text file. Whole strings of commands can be stored in a script and executed en masse.

Running a FoxPro script in concert with an FTP script, and throwing in some PKZip as needed, it’s possible to automate complex data retrieval and importation tasks.

Key to this process is the fact that FTP can capture not only data files but also the contents of directory listings. A text capture of a directory listing, is a nice, structured file that can be parsed into a FoxPro file, edited and output to a text file that can, in turn be invoked as an FTP script.

Think about it for a second: You can capture a directory listing from a re-mote FTP site and use the file names from that directory listing to form the backbone of an FTP script. It can save a lot of typing.

Preparing for the script

Putting together such a script can be time-consuming. The effort may not be worth it for one-time jobs. But for FTP jobs you run regularly, these scripts can be real time-savers.

Let’s go through a FoxPro/FTP/ PKZip trifecta that goes to the FEC site, captures data files and unzips them. (From there it should be obvious how to add commands to the script to append the unzipped files into pre-structured tables.)

There are a couple of prerequisites: (1) a tweak to your FoxPro settings; (2) a shell file into which to append the directory listing once you’ve captured it. To adjust our FoxPro settings, we’ve got to stop thinking scripts for a minute and delve into the Windows GUI.

Using your favorite method, browse to the FoxPro directory (FPW26) in a standard FoxPro 2.6 installation. Look for a file icon labeled “FOXRUN.” Right-click on the icon and choose Properties. Click on the Program tab in the Properties dialog. Near the bottom of the dialog is a checkbox labeled “Close on exit.” Make sure the checkbox is checked. This setting assures that control is returned to FoxPro after an external program has done its stuff. (The same process can work with later versions of FoxPro as well.)

Making directory listing

The shell file should contain one field wide enough to accommodate the widest path/filename listing in a directory display. For what we’re doing here, 50 bytes will be wide enough. Create a table called dirlist.dbf with a single field called filenames.

We’re going to break this into two scripts. The first script will go to the site and capture the directory listing. It consists of a text file called ftpscrpt.txt, containing these commands:

cd /FEC/electronic
ls /FEC/electronic text.txt
close
bye

The first line changes to the appropriate directory on the FTP site. The second line calls a directory listing of /FEC/electronic and redirects the output to a file called text.txt. Then the script ends the FTP session.

But how do we start the FTP session and invoke the script? The following command would do it from a DOS prompt: ftp -s:ftpscrpt.txt -A ftp.fec.gov.

FTP launches the client. The -s: switch, followed by a filename, causes the client to treat the contents of that file as a script. The -A switch forces the client to log you on as an anonymous user. (If you find your-self using a server that doesn’t allow anonymous logins, just drop the -A switch and add a user name and password to your ftp script file.)

The command string and switches are followed by the address of the ftp site.

Last steps for program

This same string of commands and switches can be called from inside a FoxPro script. Create a program file. (I named mine, fit-tingly enough, ftp.prg.)

By simply adding the word “run” to the line above, the FoxPro script will launch the FTP session and carry out the commands in the ftp script file: run ftp -s:ftpscrpt.txt -A ftp.fec.gov.

Once the ftp script is finished, control will be handed back to FoxPro, and you’ll have a text file called text.txt, containing the directory listing from the FEC site.

Now you can use your FoxPro script to work a little mojo on that text file. Everything below – including explana-tory notes that begin with the “&&” character string — is in our Fox pro-gram file, ftp.prg:

run ftp -s:ftpscrpt.txt -A ftp.fec.gov

&& The ftp script we’ve already invoked

use dirlist

&& Opens the shell file we created earlier

append from text.txt type sdf

&& Appends the directory list into the .dbf file

replace all filenames with “get “+filenames

&& Adds an ftp “get” command before each file listing.

go top

&& Goes to the top

insert blank before

&& Inserts a blank row

replace filenames with “binary”

&& Adds an FTP “binary” command, critical for downloading binary files such as .zips or .jpegs

replace filenames with “cd /FEC/ electronic”

&& Adds an FTP change-directory command

go bottom

&& Goes to the bottom of the file

append blank

replace filenames with “quit”

&& Adds an FTP “quit” command

&& Suppose we didn’t want all the files in the directory listings, but only those produced after certain dates. This is easy with the FEC data because the file naming conventions are date-based. You can use the file names to eliminate from the script those files you’re not interested in getting. Suppose we don’t want any files dated before March 25, 2000:

delete for filenames=’get’ and substr(filenames,21,8) < “20020325” pack

copy to ftpscrp1.txt fields filenames type delimited with blank

&& Now we copy our work out of the .dbf file to a .txt file “delimited with blank” is necessary to eliminate leading spaces from the text file.

&& Et, voila! We’ve now created a second FTP script that will go back to the FTP site and retrieve the files we’ve specified.

close all

run ftp -s:ftpscrp1.txt -A ftp.fec.gov

&& We run the newly created script file, ftpscrp1.txt

run \pkzip\pkunzip *.zip

&& Then we unzip the files we’ve just created.

Avoiding other problems

There is one issue here. The old PKZip v. 2.04g doesn’t support long file names. When you run into long file names that can create problems. WinZip offers a command line interface, which theoretically should work as well from a FoxPro script and should overcome the long file name problem. Doubtless there are other command line-based compression programs out there that can be worked equally well into this scheme.

If you regularly run a FoxPro/FTP script combination, you might forget to erase text files and to clean out your shell file. A pre-existing file can make your script stop and prompt you for an overwrite — no big deal unless you’re counting on that script running itself while you take a lunch break. Appending file names to a shell file that’s al-ready got data in it has the potential for creating nasty confusion.

You can simply add record-delete and file-delete commands to your FoxPro script to clean up detritus that may get in the way later.

In this example script, we used the “ls” command to capture the directory from the server. This gave us a short-form directory. However, there may be times when you want a long-form directory. For instance, if you want dates to key on for deciding which files to download and the site’s file naming conven-tions aren’t date-based, the date-time stamps in the long-form directory can be valuable.

Substituting “dir” for “ls” will give you a long directory.

To download a copy of the program ftp.prg, go to www.nicar.org/techtips.html. The Web page also features downloads and other supplementary materials for various articles that have appeared in past issues of Uplink.

Happy FTPing!

Griff Palmer is database editor at the San Jose Mercury News can be reached by e-mail at GPalmer@sjmercury.com.

Return to Tracker.