[AccessD] Import errors from Excel

"in Access as 5-digit numbers"

If Excel is reading converting the date text into actual dates, the
underlying value will be a 5 digit number.  If you are then upload that
date into Access without forcing into a date format (even IF IT IS a
date format in Excel), it is likely to be imported as a 5 digit number.
If you re-export this number to excel will can re-read it as a date,
although you would be far better off loading it into access as
yyyy-mm-dd format to enforce date compatibility.

Excel often tries to be 'helpful' when reading text and values and will
auto convert stuff for for.  This is often a right PITA and can cause
major problems.  I wish Excel would let me do the thinking and it can
focus on mashing the numbers.  Bah!

If you need help on how to load dates into Access from Excel using ADO,
email the list and I can post some samples for you.

Regards
Darryl.




-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Nathan
Hosburgh
Sent: Tuesday, 8 December 2009 5:40 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Import errors from Excel

Hi everyone - I'm rather new to the list and don't have incredible
experience with Access.

I've exported data to Excel from a website.  All the data looks ok when
it's in Excel, but when I import it as a new table into Access there are
errors.  There is a date field that has various date formats: 1995,
SUMMER 2002, SEPT-OCT 2005, Dec-08, etc.  I'm not concerned about
normalizing the data into consistent date formats... I can leave it as
text.  I have done this before with other tables and the data came
through fine in Access.  But, this time, some of the dates are coming
through in Access as 5-digit numbers.

It's really bizarre.  This happened to me originally a couple months
ago, then I did something to make it import correctly.  Now, of course,
I cannot remember what it was that I did.  I have a feeling it has
something to do with how I save the Excel file, but I'm not sure.

Thanks,

Nate

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

"This e-mail and any attachments to it (the "Communication") is, unless otherwise stated, confidential,  may contain copyright material and is for the use only of the intended recipient. If you receive the Communication in error, please notify the sender immediately by return e-mail, delete the Communication and the return e-mail, and do not read, copy, retransmit or otherwise deal with it. Any views expressed in the Communication are those of the individual sender only, unless expressly stated to be those of Australia and New Zealand Banking Group Limited ABN 11 005 357 522, or any of its related entities including ANZ National Bank Limited (together "ANZ"). ANZ does not accept liability in connection with the integrity of or errors in the Communication, computer virus, data corruption, interference or delay arising from or in respect of the Communication."