Fields are not mapped correctly when you use the Import Text Wizard to import a comma-delimited text file (824182)


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
  • Microsoft Access 2000
This article applies only to a Microsoft Access database (.mdb).

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

SYMPTOMS

When you import a comma delimited text file by using the Import Text Wizard, the following behaviors occur:
  • When you import a text file by using the Import Text Wizard, a field that contains data that exceeds the range of Long Integer is still mapped to the Long Integer.
  • When you import the same file by using the TransferText method, the field is now correctly mapped to the Double data type. However, another field that contains alphanumeric characters is incorrectly mapped to the Double data type.

WORKAROUND

To work around these behaviors, follow these steps:
  1. Start Access.
  2. Create a new database.
  3. On the File menu, point to Get External Data, and then click Import.
  4. In the Import dialog box, click Text Files in the Files of type section.
  5. Locate the Source.txt file.
  6. Click the text file, and then click Import.
  7. In the Import Text Wizard, click Advanced.
  8. In the Import Specification dialog box, in the Field Information section, click the row selector for the Field5 field.
  9. Click the Data Type column, and then in the list box, click Double.
  10. Click OK to close the Import Specification dialog box.
  11. Click Next in the Import Text Wizard, and then click Finish to close the Import Text Wizard.
  12. Click OK in the message box.
  13. In the Database window, click Tables.
  14. Double-click the Source table to open it.

Note that the ESN field is correctly mapped to the Double data type, and the data is imported.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce the Problem

Create the Comma Delimited Text File

To create the comma delimited text file, follow these steps:
  1. Start Notepad.
  2. Paste the following text in Notepad:
    MasterPack,QCSN,FNCI,MyHex,ESN.
    P1005C4J5,N108B08V4,FNCI21000000037,7402051F,11600132383
    P1005C4J5,N108B08T5,FNCI21000000069,74020522,11600132386
  3. Save the file as source.txt.

Import the Text File into the Access Database

To import the text file, follow these steps:
  1. Start Access.
  2. Create a new database.
  3. On the File menu, point to Get External Data, and then click Import.
  4. In the Import dialog box, click Text Files in the Files of type section.
  5. Locate the Source.txt text file.
  6. Click the text file, and then click Import.
  7. In the Import Text Wizard dialog box, click Next. Click Next in the other Import Text Wizard dialog boxes, and then click Finish to exit the Import Text Wizard.
  8. Click OK in the message box.
  9. In the Database window, click Tables.
  10. Right-click the source table, and then click Design View to open the table in Design view.
  11. Click the row selector for the Field5 field, and verify the Field Size value in the Field Properties pane.

    You may see that the Field Size is populated with the Long Integer value.
  12. Close the Design view window, and then double-click the source table to open the table in Datasheet view.

    The data that corresponds to the Field5 column is not imported.
  13. Close the table.

Use the TransferText Method

To use the TransferText method, follow these steps:
  1. In the Database window, click Modules in the Objects section.
  2. Click New.
  3. On the View menu, click Immediate Window.
  4. Type the following information, and then press Enter: DoCmd.TransferText acImportDelim, "Source_DataViaCode", "C:\Source.txt", True
  5. Close the Visual Basic Editor.
  6. In the Database window, click Tables.
  7. Right-click the Source_DataViaCode table , and then click Design View to open the table in Design view.
  8. Click the row selector for the ESN field , and then verify the Field Size value in the Field Properties pane.
  9. Click the row selector for the MyHex field, and then verify the Field Size entry in the Field Properties pane.
Note that the ESN field is correctly mapped to the Double data type, but the MyHex field is also incorrectly mapped to the Double data type.

REFERENCES

For more information about data import, click Microsoft Access Help on the Help menu, type import in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
For more information about TransferText method, click Microsoft Access Help on the Help menu, type TransferText in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MinorLast Reviewed:6/9/2004
Keywords:kbImport kbprb KB824182 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.