ACC2000: Fixed-Width Text with Embedded Carriage Returns Is Imported Incorrectly (208404)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q208404
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

When you import or link a fixed-width text file with fields that contain an embedded carriage return (CR) and line-feed (LF) control characters (ASCII decimal values 13 and 10 respectively), Microsoft Access interprets this character set as being the end of a record. The remaining portion of the record that follows the CR/LF is considered the beginning of a new record.

CAUSE

The Import Wizard does not evaluate the field with the embedded or soft return as part of the field's value.

RESOLUTION

To work around this behavior, create a custom import specification using Schema.ini, and then link the text file using Visual Basic for Applications. After the file is linked, you can use a make-table query or append query to import the linked data.

MORE INFORMATION

The Microsoft Access Text Import Wizard assumes that a combination of CR/LF designates a new record when parsing a fixed-width formatted text file. Imported or linked data appears in an unexpected format when a field value contains an embedded CR/LF.

Using Visual Basic for Applications and the TransferText AcImportFixed feature generates the same behavior when linking or importing. Using an export/import specification does not override the wizard.

Steps to Reproduce Behavior

  1. Open the sample database Northwind.mdb.
  2. Click the Tables tab, and then click Suppliers.
  3. On the File menu, click Export.
  4. In the Save as type box (in the Export Table 'Suppliers' To dialog box), click Text Files, and then click Save. The Export Text Wizard starts.
  5. In the Export Text Wizard dialog box, click Fixed Width.

    NOTE: The fifth column (Address field) of the fourth record displays an embedded, or soft, return as two vertical bars. These are the actual CR/LF control characters.
  6. Click Finish to export the data.

    NOTE: Using Notepad, you can open the Suppliers.txt file and view how the table was exported. The embedded returns within the Address field produce an unexpected text file format. Note how the fourth row ends with "9-8 Sekimai" and the fifth row starts with "Musashino-shi," even though this data is all part of the same field value.
  7. On the Microsoft Access File menu, point to Get External Data, and then click Import.
  8. In the Import dialog box, in the Files of type box, click Text Files. Click the Suppliers.txt file, and then click Import. Notice that the Text Import Wizard starts and that the wizard assumes a fixed-width format.

    In the Sample data from file box, view how the wizard interprets the text file: The wizard guesses where the field breaks are and when a new record begins. Fixed-width records with an embedded carriage return within a field are interpreted differently than expected. The field value in column five of record four ends with "9-8 Sekimai" and record five starts with "Musashino-shi."
  9. When finished viewing, click Cancel to terminate the Import process. You may delete the Suppliers.txt file.

REFERENCES

For more information about accessing data in a text file, click Microsoft Access Help on the Help menu, type import or link a delimited or fixed-width text file in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:6/29/2004
Keywords:kb3rdparty kbprb KB208404
©2004 Microsoft Corporation. All rights reserved.