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

This article was previously published under Q208404
This article has been archived. It is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.

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

When you import or link a fixed-width text file with fields that containan 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.
The Import Wizard does not evaluate the field with the embedded or soft return as part of the field's value.
To work around this behavior, create a custom import specification usingSchema.ini, and then link the text file using Visual Basic forApplications. After the file is linked, you can use a make-table query or append query to import the linked data.
The Microsoft Access Text Import Wizard assumes that a combination of CR/LFdesignates a new record when parsing a fixed-width formatted text file.Imported or linked data appears in an unexpected format when a field valuecontains an embedded CR/LF.

Using Visual Basic for Applications and the TransferText AcImportFixed feature generates the same behavior when linking or importing. Using anexport/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 Addressfield produce an unexpected text file format. Note how the fourth rowends with "9-8 Sekimai" and the fifth row starts with "Musashino-shi," eventhough 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. Thefield value in column five of record four ends with "9-8 Sekimai" andrecord five starts with "Musashino-shi."
  9. When finished viewing, click Cancel to terminate the Import process. You may delete the Suppliers.txt file.
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.
truncates missing prb not imported correctly schema ini

Article ID: 208404 - Last Review: 01/09/2015 16:34:32 - Revision: 2.0

  • Microsoft Access 2000 Standard Edition
  • kbnosurvey kbarchive kbprb kb3rdparty KB208404