Importing Excel file, text formatted number fields change to scienti

[ProgrammingB52]

I have imported an excel file into access. After importing though, the text format changes the numbers to scientific notation, for example 723252238464 changes to 7.23252E+11. In excel the field was formatted to text, and is text as well for access. All of the fields with long numbers after importing have changed to scientific notation. How do I prevent that?

 

[Lizziefred]

If you import it as a new table you should be able to verfify the field type. The screen after you select import to New Table will show you all of your fields. It will also give you the data type it is trying to import to. YOu can change it here.

E

 

[psemianonymous]

This is what I am reading:

1. You have a huge number in Excel, and you want to import it into Access. The numbers are stored as text in Excel.
2. You imported them into Access, and suddenly they're showing E+11.
3. You want them to specifically import as they are shown in Excel.


A few things:

1. Your number is too big for the Long Integer format. That only stores up to 2 billion, and your example shows 700 billion. So access instead stored the number as a double-precision floating-point, which CAN handle numbers that size (while losing precision).
2. Access did not store your number as text. Re-import, and make sure the field shows up as "text" instead of double. If it was importing as text, you would have seen your exact numbers (gibberish example: "23984293847293847293874" instead of 2.3E+15).


--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[ProgrammingB52]

Unfortunately it is shown as text during the access import table steps. The problem happens after it is imported and I dont know how I can prevent the text format from turning into a number format.

 

[psemianonymous]

Blah. Here's a workaround then.

1. Open excel. Do a "Save As" of your datasheet to a text file with comma-separated values.
2. Import from this CSV/text file into Access. You should THEN be able to set it as text.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[ProgrammingB52]

Foolio12, that worked!!!!!!
.....thank you very much.