Importing NUmbers From Excel Problem

  • Thread starter Thread starter Carl Jarvis
  • Start date Start date
C

Carl Jarvis

Hello

I am having trouble importing a 28,000 record excel spreadhseet into access.

The problem occurs in the way access treats my customer number field
(typically 891227123456)

Access shows this as 891227+11 or such.

The excel spreadsheet has this formatted as a number but still access won't
import it properly.

I have tried adding column headings, removing them, saving it as a txt and
csv file - all with the same results.

Does anyone have an idea how to make the number appear as it does in Excel?

Many thanks in advance.

Carl.
 
Carl

I suspect Access displays the value in a table as 8.9122... +11. This is
scientific notation (8.9122 ... E + 11).

If you open your table in design mode, what data type does Access use?

An alternate approach is to create a table in Access, with the data types
you desire. Then, instead of importing directly from Excel, you can link to
the Excel and use queries to "parse" the import data into your permanent
Access table, doing any necessary conversions at the same time.
 
The max of Long (whole number) is 2 ^ 31 (2 billion approx.) and your
Customer Number are too big to be stored as Long hence Access import them as
Double. Double numbers are not accurate representation of the whole numbers
if the whole numbers are too big.

OTOH, if you don't do arithmetic calculations with the Customer "Number",
you can import then as Text and Access will import them exactly as they are.
 
Thanks to you both!

I got it working by changing it to a text cell (not a number) in the table
design.

Many, many thanks!

Carl.
 
Back
Top