Many tasks in importing and processing computer data are repetitive. This is good because it means you keep asking for data and someone keeps giving it to you.
The downside is you are too smart and your time is too valuable to sit at your keyboard and mechanically type a sequence of FoxPro commands. The answer - as many old-timers ("six months or six databases, whichever comes first") know is a simple batch of commands, saved in a FoxPro command or .prg file.
However, one of your data processing steps could involve adding a field, changing the name of a field, or deleting a field after a new one is created and filled with the right data.
It would be nice if you could cheat your way around the manual two-step intervention this seems to involve: 1) Stopping at the appropriate point in the process to modify the table structure, and, 2) waiting.
Before I tell you how to cheat FoxPro out of tethering you to the keyboard, I should finish on the reasons why batch files for processing data are important:
They save your time, not the computer's. They are repeatable. This is important if your data custodian has to send you a correction after you've invested four hours in your importing and preparation steps. They explicitly document and define the processing steps taken with the data. The batch is developed by deliberating what steps need to be taken and then testing those steps.
After process is refined, the commands are recorded in the batch. This ensures that the correct procedures are not left to chance or memory.
The batch should include comments (non-executing lines) about what you did and why. You should put in who wrote the batch and when it was changed.
All of these things are useful when you are asked about the integrity of the data or about decisions and changes made to the data.
Now, here's how to cheat. The technique is based on two features of SQL and FoxPro: Using the into table keywords at the end of a SQL query, and putting a literal value in the SELECT clause of the query.
As an example, you have a table called contribs and you want to add a character-type column named NewOcc and 40 characters wide. Here is what your batch would look like:
Add a char (40) column to the table
SELECT * space (40) as NewOcc ; FROM contribs ; INTO TABLE contribl * next step in batch
The query selects all the current columns, and creates one that is 40 spaces, using the built-in FoxPro function. The AS keyword is standard SQL and defines the column name you want. If you leave it out, FoxPro will name it EXPR_1 or something.
Here is one more example from one of my batches. It does in one step what I used to have to do in two manual steps: creates a column called state, and then puts the third and fourth characters of the id column into that field. (See the second line.)
As the data comes from the FEC, the state column contains the state used in the candidate's mailing address, not the state where the office is sought. In importing that data, I call that column MailState.
The Candidate ID field contains the two-letter postal abbreviation of the state the candidate would represent. In this example, the columns are explicitly named in the query instead of using the asterisk wildcard; I wanted to dictate the order of the columns.
SELECT cand.id, cand.name, ; substr (id, 3, 2) as State, ; cand.party1, cand.party3, ; cand.ico, i cand.status, cand.address1, ; cand.address2, cand.city, cand.mailstate, cand.zip, ; cand.prin_comm, cand.electyear, i cand.district ; FROM tmpcand CAND ; INTO table cand96
Richard Mullins can be reached at (573) 882-2127, or send e-mail to richard@nicar.org