ACC1: How to Import Fixed-Width Text Files

• Microsoft Access version 1.0

SUMMARY

This article describes the fixed-width text file format and explains how to import this type of file.

MORE INFORMATION

A fixed-width text file in Microsoft Access is a file that contains records of a fixed, consistent width. The data fields in each record begin at fixed offsets from the beginning of the record.

You cannot import files that have variable length records that do not contain field separators (for example, commas or tabs). These files are not fixed-width text files. To import this kind of file, you need to first process the file using an editor to insert field separators or to turn the file into a true fixed-width text file by, for example, adding spaces to each record to make all records the same width (number of characters).

Microsoft Access imports fixed-width text files by casting a record template into the file at calculable offsets. It uses the offsets to determine the logical beginning of each new record. Microsoft Access determines the logical record width by scanning the first data record of the file to locate the first carriage return and line feed (CR/LF). It assumes that the first CR/LF marks the end of the first record (see point three later in this article).

The data fields defined in the template come from the information you supplied when you set up the import/export specification. Microsoft Access uses the template to extract the individual field values. Then it enters the field values into table and advances the template to the next record.

To import fixed-width text files successfully, they must meet the following conditions:

1. The file must be truly fixed-width. That is, all records have the same width with all fields in the records beginning at consistent offsets.

2. The import/export specification you set up must match the physical file. Microsoft Access uses the specification to create the template of fields after inspecting the physical file to determine the overall record size. If the field template differs from the actual record size, you may get bad results.

   Ensure that the greatest (Offset + Width - 1) in your import/export specification is less than or equal to the physical record width excluding the CR/LF at the end of the record. In other words, an import/export specification that defines two fields--ID beginning at offset 1 for a width and of 8 and DATA beginning at offset 11 for a width of 50--requires your physical file to contain records of no less than 60 bytes (11 + 50 - 1) not counting the CR/LF. Microsoft Access will ignore records that have extra data beyond the last byte defined in the import/export specification.

3. The first record in the file must not contain embedded CR/LF characters. Otherwise, the Microsoft Access record size calculation will be wrong.

REFERENCES

For more information, search for "Fixed-width text files" using the Microsoft Access Help menu. Also, see the Microsoft Access "User's Guide," version 1.0, Chapter 4, "Importing, Exporting, and Attaching," pages 72-85.