ACC1x: Importing Delimited Text Files Strips Off Decimals

ID: Q95446

The information in this article applies to:

Microsoft Access 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

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 
```

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.

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 
```

Microsoft Access stripped the decimal places from the second file because the data type for the field was defined as Number, Long Integer--a data type that does not support decimal values.

REFERENCES

Microsoft Access "User's Guide," version 1.0, Chapter 4, "Importing, Exporting, and Attaching," pages 72-78

Keywords : kb3rdparty IsmTxtd
Version : 1.0
Platform : WINDOWS
Issue type : kbbug