ACC1: How to Import Fixed-Width Text Files
ID: Q94107
|
The information in this article applies to:
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:
- 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.
- 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.
- 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.
Additional query words:
import/export forms Macros Spec
Keywords : kb3rdparty IsmTxtfx
Version : 1.0
Platform : WINDOWS
Issue type : kbhowto