Getting new data is like getting a present. You want to get the data loaded into your PC database program as quickly as possible so you can start playing with it.

But this unwrapping process can be delayed by simple typing mistakes on our part, or, what's worse, there can be small or frustratingly substantial differences between the record layout we have on paper and the data we were given on tape.

Just like alarm clocks going off at 6:15 a.m., this kind of thing happens more often than we'd like. Here are some of the procedures I've come up with to deal with the problems that sometimes hold us up from the fun part: getting to interview data.

For the tape processing, I'm using Nine Track Express and assuming familiarity with its Create File Parameters and Create Field Descriptions steps. Additionally, these tips take advantage of Microsoft Excel's marvelous Text Import Wizard; a graphical word processor such as Microsoft Word; and a file viewer called List, a shareware program I find indispensable.

Testing format

Use .CSV as the output format while tesing. The Preview feature in Nine Track Express can save you a lot of time. I never process any tape without using it. While the Transfer File procedure is counting off the records moved from the tape to the PC, pressing the "P" key pauses the process and shows you exactly what Nine Track is reading as a single record. If the data at the beginning and at the end looks OK, then you know the record length you are using is correct. I always use the Preview several times to make sure the one record I saw wasn't coincidentally correct and most of the others were wrong.

Mistakes in field lengths are more frustrating. If you are outputting the data to the FoxPro or Paradox format, the preview will not show you how the data is being split fields. To find out if right, you have to quit Nine Track and look at the results in the database program. If it's wrong, that means quitting FoxPro or Paradox and going back to Nine Track. And if you're using the Windows version of these database managers, then you have the extra step of loading and unloading Windows.

Time saver

Save yourself some time and use .CSV as the output format until you know that the data fields are being split in the right places. Then, when the field description is right, change the output format to the database format you want.

The commas and quotes will reveal simple field length problems such as: every ZIP code field beginning with a letter (some column before the ZIP is one byte too long. Of course, this example is too easy. Finding mistakes in data layouts when most of the information is readily recognizable stuff such as names, addresses, amounts and dates is easy compared to data sets that are hundreds of bytes long, with nothing but key fields, and lots of small fields with codes like "EA" and "J1." When scanning the preview output, go to the end of the line, and work back to the beginning. Load a small sample of the file (1,000 records or less) in fixed-position format.

Saving samples

I often do this step first with a dataset I haven't processed before. I save the sample file on the floppy I keep for every data project. All finished and tested Nine Track profile (PR2) files go here too. (I know I wouldn't want to have to do all that typing and testing all over again.) I also save small .dbf samples of all the tables in the data set. I often print a small portion of this file, especially if it's obvious that some record layout debugging is going to be needed. Most data records are longer than 80 characters, so this is where a graphical word processor comes in handy. First, for the printout to be useful, you have to set the text to a monospace font such as Courier or Courier New. Proportional fonts such as Times Roman will display the text with less width for the i's and l's and more space for M's and W's. The result is text that doesn't look like a fixed-position file. If necessary, I set the point size to eight, use Print Setup to shrink the left and right margins to the minimum and print sideways on the paper (landscape mode), or even choose legal size paper over letter.

Displaying this file with the List program also helps, since one of the features of this program displays a ruler or counter line at the top of the screen to make it easy to see, for example, what characters occur in positions 149 through 156.

Since fixed-position output only divides the data records, but doesn't split the fields, you can quickly fill out the Field Description part of the PR2 file by making a single field with a length equal to the data record length. Use character as the data type.

Nine Track Express has a limit on how big a single field can be, so if the record length is greater than 500 bytes, you have to enter descriptions for 500-byte fields until what's left is less than 500 bytes. Then you make the final field length equal to the remainder. For example, if the record length is 1637 bytes, add field descriptions for three fields of 500 bytes, and then one field of 137 bytes.

Other tools

Use Excel on the sample fixed position file. I think everyone who sees and uses the Excel 5 Text Import Wizard immediately likes it because it's so easy. I used it recently on a non-problem data set I had just received because it was the fastest way to get the data imported without a lot of typing of field lengths.

I also like the Text Import Wizard because it's a great teaching tool for the concept of importing data. It presents the perfect picture of what has to occur when fixed-position files are imported into any spreadsheet or database, regardless of the software method.

This picture is also a great help in figuring out problem record layouts. You can use Excel's wizard to scan the records (as with a file viewer) and at the same time, designate where the fields should be split. When the wizard asks for the data type for each column, choose "Text" for every column. By default, the Excel Wizard designates every column as "General," but in the program's intent to be all-purpose and helpful, data such as Social Security numbers or other ID data composed of all numerals and no alphabetic or punctuation characters will be converted to scientific notation like 2.35E7, which is my Social Security number with the confidential parts rounded off.

Spreadsheet limits

Spreadsheets have a limit of about 16,000 rows, but you only need to test a few hundred rows. This is another reason for doing that fixed-position sample output of the data.

After you figure out the correct layout, you have several options. You can import the spreadsheet into FoxPro or Paradox and print out the table structure for reference in correcting the Nine Track field description. It temporarily takes up more disk space, but you could also load the entire tape file as a fixed-position file; empty all the sample rows from the FoxPro or Paradox table and then import the fixed-position file into the empty table.

Because of the difference in how Excel and FoxPro or Paradox store data, there is a limitation to this method. If the data has long character fields that are filled with just spaces, instead of data, those fields will show up in the database file with much smaller widths. The only way around this problem that I can suggest is to replace all spaces in the fixed-position file with some distinct character that is not used in the data. The caret character (^ or Shift+6) is a good choice.

Richard Mullins can be reached at (573) 882-2127, or send e-mail to richard@nicar.org