ACC: Error Message When Importing Informix Delimited Text File

ID: Q99406

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0

SYMPTOMS

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

When you attempt to import an Informix delimited text file that uses the pipe (|) symbol as a delimiter, you receive the following error message:

Name " contains invalid characters

CAUSE

When you export in Informix as text delimited and you specify the pipe symbol as the delimiter, Informix adds an extra pipe symbol at the end of the line. When you import this file to Microsoft Access by specifying "First row contains field names," Microsoft Access assumes there is another field after the last pipe symbol. Since the value is null, Microsoft Access returns the above error message.

RESOLUTION

Open the file in a text editor and add a dummy name after the last pipe symbol. After you import the file, you can delete this dummy field.

Clear the First Row Contains Field Names check box when you import the file. After you import the file, delete the first row of the table (which contains the field names instead of data). Then, switch to Design view and modify the table's field names.

MORE INFORMATION

Steps to Reproduce Behavior

Create a text file in Microsoft Windows Notepad as follows:
```
      A|B|C|
      1|2|3| 
```
Place a pipe symbol between each character. The pipe symbol is usually found on the backslash (\) key on your keyboard.

Save the file as INFORMIX.TXT.

Open a database in Microsoft Access.

From the File menu, choose Import.

Select Text (delimited) as your data source and choose Import.

Select the INFORMIX.TXT file that you created in step 1. Choose OK.

The Import Options dialog box appears. Select the First Row Contains Field Names check box, then choose the Options button.

In the Field Separator combo box, type the pipe symbol and choose OK. The above error message appears.

Informix is manufactured by Informix Software, Inc., a vendor independent of Microsoft; we make no warranty, implied or otherwise, regarding this product's performance or reliability.


Keywords          : kb3rdparty IsmTxtd 
Version           : 1.0 1.1 2.0
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: March 23, 1999