Have you ever had to append hundreds of tables together? At NICAR we were faced with such a repetitive task often enough that we came up with a programming solution. Rather than permanently disfiguring ourselves with carpal tunnel syndrome, we turned to one favorite data-processing FoxPro functions-ADIR().
The ADIR() function makes use of arrays. An array can be thought of as a temporary table. In the case of ADIR(), a temporary array is made up of the contents of a specified directory on your computer. Five pieces of file information are included in an ADIR() array: names, sizes, dates, times and attributes. This information makes up the columns in the array, but the most important for our purposes is the file name. Here's an example of how ADIR() can save your sanity.
When we processed the USDA meat-packing inspections database, ADIR() was indispensable. The meat-packing data arrived on a CD-ROM in dBase format but were separated into nearly 800 identically structured tables, broken down by the region. What we wanted, however, was one table for the entire country. Here's what the basic code that appended the USDA tables looked like:
gcOutputDir = 'c:\usda\outgoing\' gcSourceDir = 'c:\usda\incoming\' USE (gcOutputDir) + 'all_usda.dbf' in 1 =ADIR(temparray, (gcSourceDir) + '*.dbf') SELECT 1 FOR i = 1 to ALEN(temparray, 1) APPEND FROM (gcSourceDir) + (temparray(i,1)) ENDFOR
Explaining the code
The first two lines of the above code simply set our working directories. The source tables are in the incoming directory. The outgoing directory contains an empty table structure - all_usda.dbf - that will become the USDA database for the entire country. (NOTE: This code assumes that the structures of your individual source tables are the same. If not, you could lose data when you append them.)
The third line opens the table structure (all_usda.dbf) into which we will be appending.
The fourth line is where ADIR() comes into play. The function can handle up to three arguments, though in this case we only use two: temparray and gcSourceDir + '*.dbf'
The first argument (temparray) is simply the name of the array that will contain the file names, sizes, dates, times and attributes. In this example, the array did not exist, so FoxPro created one that we named temparray. If the array did exist, FoxPro would have modified the size of the array so that the information would fit.
The second argument (gcSourceDir + '*.dbf') tells FoxPro to look in the source directory and put all the files with a .dbf extension into our array. With all of the file names and attributes stored in this temporary array, we are ready to append the source tables into the outgoing table.
In the fifth line, we select the outgoing table (all_usda.dbf). Next, we need to tell the program to go through the array one row (or one file name) at a time. This is why the FOR ENDFOR loop is used. In the loop, the 'i' is the counter variable that keeps the loop going. So that it begins with the first file name in the array, we set it to 1. (ALEN(temparray, 1)) tells FoxPro to continue the loop until it hits the end of the array. The '1' in the ALEN function makes sure that we are counting the number of rows in the array (which will be the same as the number of file names).
Finally, in the seventh line, we append the source tables into the outgoing table (all_usda.dbf). The last part of that line - (temparray(i,1)) - is important. The (i,1) singles out the first column in temparray, which, in the case of an ADIR() array, will always be the name of the file. As we loop through the array, we are telling FoxPro to keep appending the source tables until it reaches the end of the array.
Justin Mayo can be reached by email at justin@nicar.org.