Access Import Bug ? ? ?

[JVS]

I have an Excel Spread sheet with 2 columns "No." & "Notes" both are
formatted as "text"
There are about 60 rows of data in the sheet all the info in A1:A58 = 260
A59 & A60 "by260".

I created a simple table in Access with 2 columns (text format) when I
import my spread sheet I get a "Type Conversion Failure" error message and
the everything is imported but
the A59 & A60 data (by260) the notes for those 2 records imports correctly ?
? ?

It's as if Access is over writing the format that I assigned and assumed
that "all" the data in column A was a number vs. text.

I can reproduce the problem even if I assign "No." as a memo field.

If I make the first record in my spread sheet, A1 "by260" and the last
record A60 "by260" the data imports correctly. I tested importing about 10
rows of data with the last 2 records being "by260" it also imports
correctly?

Both products are version 2000, running on XP.

I am assuming that this is a bug?
Is there a known work around for this?

Thanks!
jvs

 

[Allen Browne]

Access only looks at the first few rows to determine the data type of each
column, so the behavior you describe is expected.

Modifying the first row so the column contains text is a good workaround for
a one-off import. If you are performing regular imports, create a temp table
with exactly the same field names and the desired data types and sizes, and
then import into the existing table instead of creating a new table.
Typically you then massage the data and transfer into the normalized tables
where you really want the data.

 

[Adrian Jansen]

Access makes assumptions about the datatype in Excel based on the first few
rows in the sheet. It doesnt matter what the table field types are, if
Access decides a column from the sheet contains a number, it will cause this
error, even if the field its going to is text.

The only easy work around I have seen is to place at least one alpha
character in the first row of the sheet, in each column, which forces Access
to treat it as text. Import the sheet into a temp table, then use a query
to append the records you want into your final data table, and fix up any
data typing etc within the query.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

 

[Dale]

If you set the column in excel to format numbers as text
then this could also resolve the issue far simpler than
the other suggestions.

D.

 

[harrell geron]

the period in the field name is causing the error.