type mismatch error importing from excel

M

mary anne

I am trying to import a large spreadsheet that should have been a db. One
column has data in it that consists of an identifying number. Some of the
entries in this field contain just numbers (e.g. 9334948) and some contain a
letter at the end (e.g. 430181B). When I tried to import the four columns I
want to start the db with, I received a "type mismatch" error for all the
entries that have a letter at the end. (approximately 16,000 of the 54,000
entries)This produced two tables - one with correct data and the remainder in
the error table with no data.
I reformatted the Excel column to be "text" rather than the "general" it was
created under and saved just those . Now, When I try to import it, I
receive no errors and all the data is imported but Access shows the numbers
as "9.33495e+006" rather than 9334948. The 430181B type identifiers show
correctly. Any suggestions how to fix this?
 
V

Van T. Dinh

What I normally do with the mixed Excel Column is to create a "Calculated"
Column in Excel using the spreadsheet function TEXT() to convert all values
from the mixed Column to Text.

When I import the Excel file into Access, I skip the original Column and
only import the "Calculated" Column.

HTH
Van T. Dinh
MVP (Access)
 
Top