ACC: Error Message When Importing Informix Delimited Text File

• 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

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

2. Save the file as INFORMIX.TXT.

3. Open a database in Microsoft Access.

4. From the File menu, choose Import.

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

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

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

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