Importing number shows 8.91223E+18 instead of 8912230000100800000

[yomero]

I am having trouble importing into MS access 2010 from Excel.

I have tried changing the formatting in Excel TEXT or Number (no decimal places) however when imported to Access, it shows: 8.91223E+18 instead of 8912230000100800000

 

[Micron]

I would say either your target table numeric data type is not large enough or where you are looking at it, the width of the field is not enough. You don't say where your're making this observation, but increase the width and see what happens. If the Excel value is really a number, don't change it to text without a good reason.

 

[Joe4]

However, if the value isn't to be used in any mathematical computation, i.e. it is some sort of ID number (or credit card number, etc), you may want to change it to Text. Especially if it is possible that it might have leading zeroes that you would like to retain.

One of my big pet peeves is that in importing Excel data into Access, it does not let you denote the field formats of the incoming data. Access tries to "guess" what it is, and sometimes guesses wrong. If it looks like a number, it will import it as a number. To get around this, you can export your Excel data as some sort of text file (CSV, tab-delimited, fixed width, etc), and then import that text file into Access. When you do that, you will be able to denote the format of the fields that you are importing (and can choose Text, if that is appropriate).

 

[shg]

Is there any convenient way to stick another character before the number before importing, and then strip it afterward?

 

[yomero]

The original file is a CSV, which I open in Excel. The format of the column is "General" by default.
Even making the column wider the number in the cell displays 8.91223E+18
However when i click in the cell, the Formula Bar displays 8912230000100800000

The value is not going to be used in any formula, hence it could be Text formatted if that solved my problem.

I have tried different combinations modifying the CSV format and determining the format in Access. Or changing the CSV to Excel. Still I eaither get the short version of the number, or I get Null (empty) records.

Like shg mentioned, I will try inserting an apostrphe or something at the begingin of the string, and assume Access will treat it as text.

 

[Joe4]

Why even bother bringing it into Excel then?
Just import the CSV file directly into Access, and when going through the Import Wizard, designate that field to be Text.

 

[Micron]

I don't get it. In Excel 2007, it shows 8912230000100800000.00 if I make the data type 'Number'. The decimal places is just the result of my default settings.

 

[MarkN]

The problem is the result of Excel choking on numbers greater than 15 digits. Since this is a value that is (apparently) an ID number, not an actual numerical value, enclose it in quotes and treat it as text. Functionally, that's what it is. A product ID, a phone number, a part number, are all examples of LABELS that happen to be represented by numerical digits, but are not actually numbers.

If these LABELS happened to be all letters, or a mix of letters and digits, nobody would ever think of treating them as numbers. But since they happen to be all digits, people (and programs) think they are numbers and try to treat them that way.

They are not numbers. They are labels. Treat them with the proper respect due to an honest working label and they will serve you well. Treat them as numbers and they will revolt under your oppression, wreaking havoc on your spreadsheets and databases.

 

[xenou]

Looks to me that if you intend to store this number as a number in Access you will have to use a floating point data type (float or double). It is too large for integer data types. So that is why you see the notation for large floating point numbers. As others have said, if this is not a number (it is an ID or Label) you can use a text data type instead. Along with sng's suggestion, another trick is getting some text in even the first few rows:

text
text
text
8912230000100800001
8912230000100800002
8912230000100800003
8912230000100800004
...


then after importing delete the rows that have the values "text"


Note:
I would guess also that if you import into an existing table that already has the field with a text data type, it would convert to text on the import (but maybe in the EE notation which would be disappointing)