ACC1x: Importing Delimited Text Files Strips Off Decimals

• Microsoft Access version 1.0

SYMPTOMS

You import a delimited text file that contains numbers with decimal places, and the resulting table does not show the decimal places.

CAUSE

When Microsoft Access creates the table, it looks at the first record in the file to decide which data type to assign to the field. If the first record contains a .00 decimal value in a field, Microsoft Access gives the field a Number, Long Integer data type. Then it strips all the decimal values when importing the file because the Number, Long Integer data type does not support decimal values.

RESOLUTION

There are two workarounds. The first is to create a destination table that already has the fields defined. Choose Number, Double as the data type. It supports decimal places. When you import the text file, append it to the existing destination table rather than allowing Microsoft Access to create a new table.

Alternatively, you can work around the problem by making sure the number fields in the first record do not contain .00 as decimal values.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access version 1.0. This problem was corrected in Microsoft Access version 1.1.

MORE INFORMATION

If any of the fields in the first record contain numbers that have decimal values of .00, Microsoft Access strips the decimal places from all the records for the corresponding fields in the resulting table.

Steps to Reproduce Behavior

1. Use a text editor such as Notepad or MS-DOS Edit to create two text files (FILE1.TXT and FILE2.TXT) using the following as a guide:

         FILE1.TXT
         ---------
         234.50
         350.75
   

         FILE2.TXT
         ---------
         100.00
         276.93
   

2. Import the first file as a delimited text file and have Microsoft Access create a new table. Notice that the resulting table has the numbers in the correct format. The data type for this field was defined as Number, Double.

3. Import the second file as a delimited text file and have Microsoft Access create a new table. Notice that the resulting table imports the numbers as follows:

         100
         276
   

REFERENCES