KB153373 - ACC: Importing Fixed-Width Text with Embedded Carriage Returns

Article ID: 153373

Article Last Modified on 1/19/2007

Microsoft Access 95 Standard Edition
Microsoft Access 97 Standard Edition

This article was previously published under Q153373

Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS

When you import or link a fixed-width text file with fields that contain an embedded carriage-return and line feed control characters (ASCII decimal values 13 and 10 respectively), Microsoft Access interpret 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. Once linked, you can use a make-table or append query to import the linked data.

For more information about using Schema.ini, please see the following article in the Microsoft Knowledge Base:

149090 ACC: How to Use Schema.ini for Accessing Text 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 generates the same behavior when linking or importing. Using an export/import specification does not override the wizard.

Steps to Reproduce Behavior

Open the sample database Northwind.mdb.
Click the Tables tab, and then click the Suppliers table.
On the File menu, click Save As/Export.
In the Save As dialog box, click "To an external File or Database," and then click OK.
In the Save Table In dialog box, under Save As Type, click Text Files, and then click Export. The Text Export Wizard starts.
In the Text Export Wizard dialog box, click to select 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.
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 is all part of the same field value.
On the Microsoft Access File menu, point to Get External Data, and then click Import.
In the Import dialog box, under Files Of Type, click Text Files. Click the Suppliers.txt file, and then click Import. Note 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.
When finished viewing, click Cancel to terminate the Import process.

REFERENCES

For more information about importing fixed-width text files, please see the following article in the Microsoft Knowledge Base:

94107 ACC1: How to Import Fixed-Width Text Files

For more information about accessing data in a text file, search in the Help Index for "accessing text files," or ask the Microsoft Access 97 Office Assistant.

Additional query words: truncates missing

Keywords: kb3rdparty kbprb KB153373