Automation can be a good thing. You can turn a burdensome chore into a quick-and-easy routine. You can update information more often. You can set up a scheme to do a task consistently. To that end, the IRE and NICAR Da- tabase Library has automated quite a few tasks over the past year. We've set automated weekly updates of Federal Election Commission cam- paign finance data, Federal Aviation Administration accident and service difficulty reports databases, and monthly updates of databases of air- craft, airmen and Internal Revenue Service nonprofits.
Such automated tasks, via pro- grams written for Visual FoxPro, keep data fresh and make down- loads for users a snap. Not a FoxPro user? You can program automated tasks in almost any software. One automated process, which we run almost daily, benefits lots of us- ers of our databases, especially those with smaller news organiza- tions seeking a state slice of data. Some state slices are particularly advantageous.
Occupational Safety and Health Administration inspec- tion data, for example, is voluminous for the entire U.S. but records for one state would likely fit on one CD In years past, state-slicing was a manual process. From a copy of the data on CD, we queried the data, copied the results into a folder, then copied the documentation. The big- ger the database - for example, the truck inspection database con- sumes seven CDs - the more labo- rious the process.
The end result was, for the most part, that copying the entire country would be easier than slicing out a particular state. So we set out on a project to auto- mate state slices, and those lessons learned can be applied to many types of tasks that can be automated. First, we inventoried what we wanted to offer by state. Our order form (www.ire.org/datalibrary/orderform) lists several databases available via state slice.
Then we examined the da- tabases in question. Some natural inconsistencies occur: First, not all the databases have the same field names for states, and some have multiple fields listing states. For example, the federal con- tracts database contains three fields that might include a state - the lo- cation of the government office, the address of the contracting company, and the state where work was to be performed.
The states in those fields might all be different. Second, government agencies don't treat the state identifiers the same. For contractors, it's a postal abbreviation. For federal assistance, it's a two-digit Federal Information Processing Stan- dards (FIPS) code. For the FBI, it's yet another series of two-digit codes. Then there are the multiple-table da- tabases. In some instances (OSHA, for example) the state code is in- cluded in every table.
Others (truck accidents, for example) do not. The bottom line: When you get ready to do some automation, get very, very familiar with the data structure and documentation. Here's how we handled the inconsis- tencies: We created a table of data- bases - including a one-character code for each database, the full name of the database (strictly for human reference; our state-slicer program relies on the code, not the name) and a memo field that contains the SQL query language needed to query the data for a particular state.
We created a simple SQL-written program that has three steps: 1) It poses a series of questions to the user - On which local drive do you want to save the data? Which database do you want to slice? Which state do you want? 2) It makes a copy of the table of databases, transfers the answers of the questions to the SQL language about that particular database, and copies the memo field material into a new program.
3) It then runs the just-created pro- gram, including the final step of copy- ing the data, plus documentation, to the local drive the user chose during the first step. To dissect each step: Since this is an internal tool at the moment, there's no fancy interface - a double-click on a desktop icon starts the process, and just four keystrokes later (that is, one for the drive designation, one for which database, two for the state), the state-slicing begins.
The first section sets the stage and offers some direction: gcstatedir = \compname\datal gcstoredir = \compname\data\states\list\4 set directory to (gestatedir) Now, the series of questions begin: ? GET READY FOR A SERIES OF QUESTIONS ? ? 'YOU WILL BE ASKED: ? 1. WHICH LOCAL DRIVE WILL YOU SAVE THE DATA? TYPICALLY, YOU WILL CHOOSE M OR N' ? 2. WHICH DATABASE ARE YOU QUERYING? ? 3. WHICH STATE ARE YOU SLICING FOR?
? ? ? YOU SHOULD HAVE IN FRONT OF YOU THE STATE SLICE BINDER' ? ? wait IF YOU ARE READY, HIT THE SPACE BAR TO CONTINUE' set alternate to (gcstatedir) + 'drive' set alternate on wait 'Which drive will choose to save the data? close alternate What's an "alternate?" Instead of just splashing questions and answers on the screen, the "alternate" command tells it to store that information into a file, in this case naming it "drive." The next few steps create a temporary table, append records from the just- created file named "drive" and parse it into records so that we can record the local drive's letter.
create cursor tempdrive; (junk c(50), drive c(1)) append from drive type sdf for not empty(junk) replace all drive with upper (substr (junk, at ("?" junk)+1 goto top store alltrim (drive) to curdrive We repeat the process for the other questions/answers. The "store" com- mand takes needed pieces of infor- mation - drive, database, state - and stores that information tempo- rarily, only long enough to complete the state-slicing task.
Those tempo- rary, arbitrary, names are "curdrive," "curdbase" and "curstate." For the sake of example, let's say we choose the "N" drive on our ma- chine, the government database of firearms licensees (the code on that would be "F"), and the state of Ala- bama (the code in this database would be postal: "AL"). The next step is to use the table of databases, named "tables stored" and make a copy of the table.
The program names the copy as the word "table," plus the one-letter code of the database in question and the state code. So our copy of the table would be named "tableFAL." Then, opening up that copy, we start plugging in the stored pieces of information into the memo field content of the record that relates to the specific database. replace all sql with strtran (sql, XX' (curstate)) for id = (curdbase) replace all sql with strtran(sql,inh (curdrive) for id = (curdbase) The field called "sql" is the memo field that contains the SQL program lan- guage for each individual database to slice.
The function "strtran" is a find- and-replace tool with three arguments - the field, the character to find and the material to replace it with. For the firearms licensee data, here's some key SQL language: gcyears = '04' gcdrivedir = \ATF_XX\' gcstatedir = \\compname\\data\\states\\\' gcpickupdir = \compname\data\states\pickup\ATF_FF gcstate 'XX' Notice the "XX" and "~" that are serv- ing as placeholders for the actual state code and the drive name where we want to store the data and docu- mentation.
The "gcpickupdir" folder already contains the documentation that goes along with the data. The query for the data, then copying the results, uses the "gcstate" vari- able, with the "XX" value replaced by "AL" for our example of Alabama: select from 'Ffl + (gcyears); where premst = (gcstate) or mailst = (gcstate) ; nowait copy to (gepickupdir) + 'Ffl' + (gcyears) type foxplus The last step is to make some copies of the data and documen- tation to a folder named "N:\ATF_AL." command = 'xcopy /e /v + (gcpickupdir) + (gcdrivedir) run &command This is, of course, a work in progress: The next step is to add another question and allow the user to automatically place it on our FTP server if someone needs the data right away.
After that, we'll start add- ing our archival data in addition to our current copies of databases we provide year-by-year. All that can be accomplished in some not-too-diffi- cult Visual FoxPro language. We'd love to see how you've used your favorite applications to auto- mate tasks; if you've got one you're willing to share, please e-mail me. Contact Jeff Porter by e-mail at jeff@ire.org