ACC: Unable to Import Portion of Fixed-Width Text Files

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

You cannot import any portion of a fixed-width text file.

CAUSE

Each record in a fixed-width text file must be separated from the next by a carriage return or line feed, and all the records must contain the same number of characters. The text file you are trying to import does not meet these criteria.

RESOLUTION

Modify the text file to ensure that all records meet these requirements:

• Each record is on a different line.
• Each record contains the same number of characters.

STATUS

This behavior is by design.

MORE INFORMATION

Microsoft Access cannot import fixed-width text records that are not delimited by a carriage return or line feed.

Mainframe users may be familiar with long strings of data where the first x number of characters are the first record, the next x number of characters are the second record, and so on. In order to import these records into Microsoft Access, you must ensure that each record is on its own line with a carriage return or line feed at the end of each record.

For example, consider the following string of characters:

   lastnamefirstnameagelastnamefirstnameagelastnamefirstnameage

To modify the file using code, follow these steps:

1. Read in the file in Binary mode with a fixed-length string that is the same length as the record.

2. Write each string to a new file that is open for sequential output.

3. Repeat steps 1 and 2 until you reach the end of file.

   lastnamefirstnameage
   lastnamefirstnameage
   lastnamefirstnameage

In Microsoft Access 97 and 7.0

1. Click on Get External Data\Import on the File menu.

2. Change Files of Type to Text Files.

3. Select the text file you want to import and click on Import. This will launch the Text Import Wizard.

4. Click on the Advanced button.

5. Enter the following settings for Field Information:

         Field Name   Data Type   Start   Width
         --------------------------------------
         Last         Text        1       8
         First        Text        9       9
         Age          Number      18      3
   
   

6. Click on Save As and save this specification as Test. Choose OK.

7. Continue through the wizard by clicking Next and click Finish to import the file.

In Microsoft Access 2.0 and 1.x

1. From the File menu, choose Imp/Exp Setup.

2. Under Text Delimiter, change the Text Delimiter from " to {none}.

3. Enter the following settings for Field Information:

         Field Name   Data Type   Start   Width
         --------------------------------------
         Last         Text        1       8
         First        Text        9       9
         Age          Number      18      3
   
   

4. Choose OK, and save this specification as TEST.

5. From the File menu, choose Import.

6. Select Text (Fixed-Width), and then choose OK.

7. Select the text file you want to import, and then choose Import.

8. In the Import Text Options dialog box, select the TEST specification. Choose OK.

REFERENCES