type conversion failure

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JRS - 31 Jan 2005 02:29 GMT

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.

Reply to this Message

Ken Snell [MVP] - 31 Jan 2005 03:00 GMT

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)?

Signature

Ken Snell
<MS ACCESS MVP>

>I am impoting a excel spreadsheet into my access database as a new table.
>I
[quoted text clipped - 3 lines]
> 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.

Reply to this Message

JRS - 31 Jan 2005 05:01 GMT

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

> 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
[quoted text clipped - 9 lines]
> > 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.

Reply to this Message

Ken Snell [MVP] - 31 Jan 2005 05:35 GMT

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.

Signature

Ken Snell
<MS ACCESS MVP>

> these are the type of numbers causing the error.
> 9210 GH1 08526
[quoted text clipped - 19 lines]
>> > never
>> > had this problem before and I've used the source before.

Reply to this Message

whouser - 31 Jan 2005 18:37 GMT

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?

> 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,
[quoted text clipped - 33 lines]
> >> > never
> >> > had this problem before and I've used the source before.

Reply to this Message

Ken Snell [MVP] - 31 Jan 2005 19:46 GMT

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.

Signature

Ken Snell
<MS ACCESS MVP>

>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
[quoted text clipped - 49 lines]
>> >> > never
>> >> > had this problem before and I've used the source before.

Reply to this Message