Imported numbers missing decimals

[Matt]

I have a .CSV file that I need to import into an Access table. I just
noticed that when a column of numbers starts with a number without a
decimal, if any of the values in that same column have a decimal, the
numbers after the decimal are dropped. I'll provide an example:

Quantity
5000
400
650.50

In this case, what ends up being in the table is:

5000
400
650

So the numbers after the decimal are getting dropped. However if the
first value has a decimal, then all values get imported correctly.
Any idea?

 

[Maurice]

Write an import specification for your import. When doing so you can define
the fields and datatypes..

In the import wizard look for the [advanced] button (i believe it's on the
second step when using the wizard).

When you save the importspecification you can reuse this everytime.

hth

 

[Matt]

Write an import specification for your import. When doing so you can define
the fields and datatypes..

In the import wizard look for the [advanced] button (i believe it's on the
second step when using the wizard).

When you save the importspecification you can reuse this everytime.

hth
--
Maurice Ausum

I have a .CSV file that I need to import into an Access table. I just
noticed that when a column of numbers starts with a number without a
decimal, if any of the values in that same column have a decimal, the
numbers after the decimal are dropped. I'll provide an example:

In this case, what ends up being in the table is:

So the numbers after the decimal are getting dropped. However if the
first value has a decimal, then all values get imported correctly.
Any idea?

Well I already have the table set up with the correct datatypes
specified, I'm just clearing any previous records and importing data
from the CSV into the table using Excel VBA. I don't have a problem
when most of the data has decimals but when only a few do and the
first record has no decimals then I have a problem.

 

[Jerry Whittle]

You have two choices.

Open up the CSV file and add decimals to the numbers in the first record.
(You often have to do this for Excel spreadsheets too.)

OR

Create the table in Access first making sure that the data type is Single or
Double number and NOT one of the integer fields. Then import the CSV into the
table.

 

[Matt]

You have two choices.

Open up the CSV file and add decimals to the numbers in the first record.
(You often have to do this for Excel spreadsheets too.)

OR

Create the table in Access first making sure that the data type is Single or
Double number and NOT one of the integer fields. Then import the CSV into the
table.

Actually I went with Maurice's suggestion of using an import
specification. It took me some time and research to get it working
but now it works great. Thanks for the help.

 

[John W. Vinson]

I have a .CSV file that I need to import into an Access table. I just
noticed that when a column of numbers starts with a number without a
decimal, if any of the values in that same column have a decimal, the
numbers after the decimal are dropped. I'll provide an example:

Quantity
5000
400
650.50

In this case, what ends up being in the table is:

5000
400
650

So the numbers after the decimal are getting dropped. However if the
first value has a decimal, then all values get imported correctly.
Any idea?

Access can't tell in advance what datatype to use for text files; it must
guess based on the first few (I believe 8) records. Long Integers are
"simpler" than Floats or Doubles or Currency, so if the first few rows have
only integers Access will use that datatype.

The solution is probably to create the table, empty, first, with the
appropriate datatypes and text sizes; and then import into that table, rather
than trying to import directly into a new table. Alternatively, Link to the
external file and run an Append query to populate the new table; or use the
Import Wizard advanced option to set up an import specification defining all
the datatypes.