Article ID: 208404 - View products that this article applies to.
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).
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.
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 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.
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
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.