ACC: Importing Fixed-Width Text with Embedded Carriage Returns

• Microsoft Access versions 7.0, 97

SYMPTOMS

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

When you import or link to a fixed-width text file with fields that contain an embedded carriage-return and line feed control character (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 to 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:

   ARTICLE-ID: Q149090
   TITLE     : 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

1. Open the sample database Northwind.mdb.

2. Click the Tables tab, and then click the Suppliers table.

3. On the File menu, click Save As/Export.

4. In the Save As dialog box, click "To an external File or Database," and then click OK.

5. In the Save Table In dialog box, under Save As Type, click Text Files, and then click Export. The Text Export Wizard starts.

6. 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.

7. 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.

8. On the Microsoft Access File menu, point to Get External Data, and then click Import.

9. 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.

10. 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:

   ARTICLE-ID: Q94107
   TITLE:      ACC1: How to Import Fixed-Width Text Files