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