Import from Excel

[Secret Squirrel]

Not sure if this belongs in this group or the Excel group but I figured I'd
start here.
I import data from a spreadsheet into one of my tables but for some reason
one of my fields doesn't import correctly. The column is set as "General"
format in excel. It has mostly numbers in it but there are some rows that are
a combination of letters and numbers. The records that have this combination
do not import from this column.
I have the field set to "Text" in my table but it still won't import them.
Any idea why?

 

[Maarkr]

Sometimes it don't matter what you set in Excel or Access for the data. When
you import stuff, it's formatted on import and is usually based on it's best
guess of the data type in the first few rows in the column. (search in the
excel groups for a technical explanation) If you want to import mixed data as
text, try putting text values in the first few rows of the column (you can
hide them by using a text color the same as the background). If that doesn't
work, let me know what values are in the column cells, and maybe I can make
sense of it.

 

[Clif McIrvin]

Not sure if this belongs in this group or the Excel group but I
figured I'd
start here.
I import data from a spreadsheet into one of my tables but for some
reason
one of my fields doesn't import correctly. The column is set as
"General"
format in excel. It has mostly numbers in it but there are some rows
that are
a combination of letters and numbers. The records that have this
combination
do not import from this column.
I have the field set to "Text" in my table but it still won't import
them.
Any idea why?

Hmm. My experience is more with linking; I'd have thought what you
describe should work well.

With linking, Access sets the column data type on an initial scan of the
top row (or few), and Excel has this wonderfully flexible mechanism of
setting the variant data type for each individual cell. It drives Access
bonkers.

At any rate, here's some information I've gathered along the way -- hope
some of it proves helpful!

(815277) - Explains the "Numeric Field Overflow" error message that
occurs when you query a table that is linked to an Excel spreadsheet.
This article provides a workaround to resolve this problem. Requires
basic macro, coding, and interoperability skills.
http://support.microsoft.com/kb/815277/en-us
More insight in posting from Mark:
http://groups.google.com/group/microsoft.public.access/msg/d828f84b12333396
or http://tinyurl.com/3vt5rm

Seems like I ended up working around the issue (again, with a linked
table from Excel) by writing a query to append new rows into another
table which was carefully formatted; I also invested some time into the
VBA code that creates the Excel sheet that collects the source data to
insure that data typing didn't ever change between rows in the same
column (and removing the previously existing type variations.)

I'll keep an eye on this thread if you have some more questions ....

 

[Ken Snell \(MVP\)]

Perhaps this will help:
Avoid DataType Mismatch Errors when Importing Data from an EXCEL File or
when Linking to an EXCEL File
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#DataTypeErr