Linked Excel files

[Guest]

I have linked an Excel file to import data into an Access 2003 table.

Access is interpreting the Excel data type incorrectly.

I have set the data type to Number in Excel and Number in the Access table I
am importing the Excel file into. When I try to import the data, Access sees
it as text and does not import the data correctly.

Is there a way around this?

Thank you...Wendy

Thank you.

 

[Nikos Yannacopoulos]

Wendy,

To the best of my knowledge, Access doesn't bother at all with the data
type in Excel; it tries to "guess" the data type based on the data
alone, and to that end it first reads the first few rows of the Excel
sheet, and bases its guess on those (not sure how many rows). So, one
thing you can do is try to get your "proper" rows at the top of the
Excel sheet - by "proper", I mean try to not have rows with blank cells
in the first few rows, as this can be confusing for Access.
Another trick is to intentionally insert a couple of dozen dummy rows at
the top of the Excel sheet, all with "proper" data, and delete them in
the target table in Access after the import.
Of course, these tricks are more useful for one-off imports rather than
for repeated ones, I fear, as they are highly manual.

HTH,
Nikos

 

[John Nurick]

Hi Wendy,

If you want the gory details behind what Nikos said, see
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/
and http://support.microsoft.com/?id=257819