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.
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
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
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:
ls /FEC/electronic text.txt
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
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
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
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:
-s:ftpscrpt.txt -A ftp.fec.gov
ftp script we’ve already invoked
the shell file we created earlier
append from text.txt
the directory list into the .dbf file
replace all filenames
with “get “+filenames
an ftp “get” command before each file listing.
to the top
insert blank before
a blank row
an FTP “binary” command, critical for downloading binary
files such as .zips or .jpegs
with “cd /FEC/ electronic”
an FTP change-directory command
to the bottom of the file
an FTP “quit” command
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
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.
voila! We’ve now created a second FTP script that will go
back to the FTP site and retrieve the files we’ve specified.
run ftp -s:ftpscrp1.txt
&& We run
the newly created script file, ftpscrp1.txt
we unzip the files we’ve just created.
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.
Palmer is database editor at the San Jose Mercury News can
be reached by e-mail at GPalmer@sjmercury.com.
Return to Tracker.