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

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.

To work around these behaviors, follow these steps.

Access 2007

1. Start Access.
2. Create a new database.
3. On the External Data tab, click Text File in the Import group.
4. Locate the Source.txt, click Open, and then click OK.
5. Click Advanced in the Import Text Wizard dialog box, and then click the Data Type column of Field5.
6. In the list, click Double, and then click OK.
7. Click Finish, and then click Close.
8. In the Database window, click Tables.
9. Double-click the Source table to open it.

Other versions of Access

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 Filesunder Files of type.
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, click the row selector for the Field5 field under Field Information.
9. Click the Data Type column, and then click Double in the list.
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.
13. In the Database window, click Tables.
14. Double-click the Source table to open it.

Note Fields that contain data that exceeds the range of Long Integer are correctly mapped to the Double data type, and the data is imported. If you want to import the ESN field, change the double data type to text.

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

Steps to Reproduce the Problem in Access 2002

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.

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.