type conversion failure

[Guest]

I am impoting a excel spreadsheet into my access database as a new table. I
am getting an import error message.....type conversion failure....the fields
that are failing are numeric values. I've tried changing them to text and
number on the spreadsheet for iomporting and it does not work. I've never
had this problem before and I've used the source before.

 

[Ken Snell [MVP]]

When the import process is done, open the table that was created in design
view, and see what data type ACCESS/Jet is assigning to the field where the
numeric values are supposed to go. Is it something other than Number? If
yes, what? What types of numeric values are failing (ACCESS creates an error
table that tells you the row number in the EXCEL sheet that failed)?

 

[Guest]

these are the type of numbers causing the error.
9210 GH1 08526
when I look in desgin view....they say number everything else is text

 

[Ken Snell [MVP]]

The problem is that Jet is seeing numbers in the first 25 or so rows of your
EXCEL spreadsheet, so it assigns a number data type to that field, and then,
if it finds a text value in the field in a later row, it errors and won't
import that value.

You can work around this by adding apostrophes to the beginning of the
number values in the EXCEL cells (which tells Jet that the numbers in fact
are text), or you can put a text value (such as what you posted) in the
first row in the EXCEL sheet.

There are ways to make changes in the registry to force Jet to examine all
rows before deciding on a data type, but that probably is overkill for what
you want to do right now.

 

[Guest]

I have the same problem. I tried moving the alpah numeric entries to the
first rows and they converted fine. The pure numeric values still tried to
convert as numbers - "4471314201" appears as"4.47131e+009" in the database.
The field is defined as text in the design view. How do I convert the pure
numbers as text?

 

[Ken Snell [MVP]]

Try making the column width wider in your table (the table that holds the
imported data)...I think you'll see that the "exponential" number changes
over to a long number string.