Hi!

I was importing the large array of accounting data into Access from Excel through File - Get External Data - Import. Several columns contained tangible accounting data (e.g. registration numbers) that had different formats like "23423 34", "32-3545" and in some cases even contained letter constants. Therefore, I have assigned TEXT type to this column while importing and was sure that all values are going to be stored as text. However, what has happened is: 18307769 turned into 1.83078e+007! this means tangible accounting data that was used as a key field for further linking turned into bull&&$%! :-(

I know this problem is very simple, so please, can you give me a hint as to how do I solve it in the future.

Thank you in advance.

--
Regards,
vb707
securely sell goods and services on-line (http://www.securemarket.org)

The column has to be defined as text and all the values have a leading single quote in Excel for it to import properly. It seems the problem in this case is more Excel than Access.

There is also the chance that Excel converted the numbers for display purposes or that Access did so.

I'm going out on a limb here. It is entirely possible that somehow the field in question was considered as a VARIANT in Access, which is not quite typeless. But if a number is large enough during the import, it might be varied to look like a DOUBLE.

There is always the chance that you could export the data from Excel into a comma-delimited, quote-enclosed CSV or TXT format, then import it. I don't think Access would change the format if everything was quote-enclosed. Once it was in, you could do edits with update queries or you could do format conversions on-the-fly.

My thought here is, if Access can't bring it in correctly, at least try to get Excel to put it out correctly and go from there. Sort of a divide-and-conquer approach to the problem.

Or as a less elegant alternative, make the table in access manually: making sure the fields are all correctly defined wrt text, integer, double etc. Save the empty table.

Then copy the data cells in excel to the windows clipboard. Then go back to access and open the new table up. Select the field headers and right click then paste the information into the table.

This method has helped by bypass several importing field type issues before.