I don't remember data scrubbing one of the allures of journalism, but it is a perennial topic on the NICAR listserv. All those 'replace all... FoxPro commands can be tiresome, particularly on lengthy tables. But this needs to be done - especially if we want to make sure queries work right.

Last year, at one of NICAR's programming seminars, someone asked whether it's possible to write a FoxPro program that would clean up data automatically with commands that make sure every character column is uppercase and flush left. After a few minutes, I came up with this.

It's pretty straightforward. For those just learning FoxPro programming, it shows one way to use a scan loop. It relies on just one memory variable. The logic is pretty simple: After some stage setting, the program creates a lookup table so it knows the character fields. Next, the program goes through each character field, one at a time, and runs the 'replace all' commands.

If you want everything in proper case (with the first letter uppercase and the rest lowercase), you can easily alter this program. This is a stripped-down version to focus on the essence of the program, but you can spruce it up if you're so inclined.

Without further adieu, let's step through the program:

To make sure nothing else is open, and that FoxPro starts with a clean slate, we'll begin with these three lines. The first line clears the screen. The second closes any open tables, programs and just about anything else lying about. The third flushes out memory variables that could mess up what follows. Now FoxPro should be pretty clearheaded.

clear close all clear all

We'll tell FoxPro where to work. When you use this for real, you'll have to adjust by putting in the path for the folder that holds your tables. So change c:\YourFolder to the path you want.

set directory to \YourFolder

Next, we'll tell FoxPro what table to clean. You'll need to change TableName. The phrase 'in 1' is just a spot on FoxPro's imaginary desk - like saying I'll put this stack of papers on the left corner so that later, when I open another stack of papers, I'll have some free space on the right side. This makes it easier to go between the two stacks. The 'alias ToClean' means that, from here on out, I'll be calling the table ToClean. This is important because it saves us the trouble of having to edit the rest of the program. You shouldn't need to make any more changes to make this program work for you.

use TableName in 1 alias ToClean

Next, we create the lookup table. It's just a record layout in a Fox table. That's done with 'copy structure extended.' If this seems bewildering, just run this command on your favorite table and look at the result. It may help you better understand what will follow.

copy structure extended to Lookup

FoxPro will now open the lookup table in another spot on its imaginary desk.

use Lookup in 2

Here's the promised memory variable. It will remember the name of every character field.

Store to NameOfField

Let's start going through the lookup table line by line. That's what happens with a scan loop.

select lookup scan

We'll check for character fields, and remember their names. Let's say the field name is "Address," and it's a character field. FoxPro now knows that.

if field_type = 'C' Store Lookup.Field_name to NameOfField

Time to flip to our table to be scrubbed.

Select ToClean

Now we'll make FoxPro run the 'replace all.' The ampersand, by the way, is needed so FoxPro knows we're referring to a memory variable, not a column called literally NameOfField.

replace all &NameOfField; with alltrim(upper(&NameOfField))

We're done with that column. Let's see what character columns come next. Will it be a city column? A state column? Whatever it is, we'll be ready, looping through our lookup table one row at a time.

Select Lookup

The next two lines close our condition and our scan loop. They're the required periods at the end of our programming thoughts.

EndIf EndScan

By this point, FoxPro has cleaned all the fields. The next two lines just get our processed table ready to check.

select ToClean use

We're done. Open it up and revel in all the typing you've avoided.

Andrew Lehren can be reached at 212-664-7739, or by email at alehren@nbc.com.