Importing Mainframe Files into dBASE or FoxPro

This is a discussion of how to import into dBASE a file which Disc Interchange has converted from a mainframe tape.

Mainframe tape files generally come in fixed-length field, fixed-length record form, with no field or record delimiters. When we read such a file from tape for import into a dBASE compatible database, we append a record delimiter (carriage return, line feed) to the end of each record, creating what dBASE calls an "SDF" file. Such a file can be read directly into a dBASE .DBF file, once a proper dBASE structure is created.

To import the tape data into dBASE, do the following:

1. Create a new dBASE .DBF file with the command "CREATE <filename>", with a structure that exactly matches that of the tape file after conversion. All fields must be accounted for in the dBASE record structure. If the tape has fields that are of no interest to you, you must still define them, and you can later remove the fields you don't need with the MODIFY STRUCTURE command, or a dBASE "COPY TO <file> FIELD <list>" command, or by creating a new database structure with only the desired fields, then doing an "APPEND FROM <file>".

2. Import the SDF file into the newly created dBASE file, like this: "APPEND FROM <filename.ext> SDF". You must use the SDF option to tell dBASE this is an SDF file-type you are appending. The extension need not be ".SDF", but a quirk in some versions of dBASE requires that it have some extension.

3. Inspect the first few records, the last few records, and some at random to make sure all the fields are in their proper positions. Compare the number of records in dBASE (display structure) to the count from the SDF file to make sure all the records imported.

Additional tips:

1. If the file was delivered to you in a compressed format, like PKZIP, be sure to unzip it before using it.

2. If you have a very large data base, you should probably test your new structure with a small portion of the data. You can do this by starting the import, then pressing ESC to abort it. Inspect the records, then ZAP the partial file and start the import again. Be sure you ZAP the partial file, or those records will be duplicated in the final database.

3. You can view the .SDF files with an ASCII editor (or word processor), but DON'T SAVE THE FILE!   Quit without saving, and if you've inadvertently made any changes, abort them. Most word processors and many editors add formatting codes to the file which would make it impossible to correctly import the modified .SDF file into the dBASE .DBF file.

4. If you don't want all the fields of the SDF file, it is possible to create a DBF file with fewer fields. The rule is this: You must define all the fields up to the last one you want. Once dBASE has filled all the fields in the DBF file, it will discard anything else up to the next CR-LF record delimiter. As far as we know this works in all dBASE compatible databases.

5. If the file to be imported has "variable data", meaning that some records are short, or missing fields at the end of the record, dBASE will still import the record correctly.  dBASE will stop importing that record when it encounters the CRLF, even if all fields have not been filled.  For a discussion of files with short records, see Importing Variable Data Files into Access.   dBASE functions in much the same way as Access when importing variable length records.

6. If there are many contiguous fields in the SDF file that you don't want in your final dBASE file, you can group them together and call them one field, like "GROUP1", or "JUNK1". After importing the file you can remove them. This will save time.

7. After importing, if you have fewer records in the dBASE file than there were on the tape, the most likely cause is a control-Z in the SDF file. dBASE interprets control-Z to mean end-of-file, and terminates the import.  You must replace the control-Z with a space or letter and import the file again.  DISC normally tests files for control-Z and removes them, so this shouldn't happen if we converted the file from tape to SDF.

8. After importing, if you have more records in the dBASE file than there were on the tape, some records in the SDF file may have a CR or LF in the middle of them. This happens when there is binary data, or simply junk, in the file. DISC normally tests files for this, so this shouldn't happen if DISC converted the file.

Additional Information

For more articles on data conversion, see our TechTalk Index.

Disc Interchange Service Company, Inc.
15 Stony Brook Road
Westford, MA 01886