How to import row data into Access when Excel does a date conversion?

Question

I have an Excel worksheet that looks like this:

MONTH      WIDGET      QUANTITY      COST
Jul-09     Widget1     1000          1.00
... <repeat a few thousand times> ...

I would like to import this into Access, but unfortunately the Access date/time formats don't seem to be compatible with Excel's.

How can I import this data? I'm using the latest versions of Excel and Access 2010.

duffbeer703 · Accepted Answer · 2011-08-05 16:13:05Z

0

I found a sub-optimal way to do this -- if you export the spreadsheet to CSV, Access will interpret "Jul-10" as text instead of an integer.

In order for Access to import as a date/time entry, you must change the formatting to a more common date format in Excel first.

answered Aug 5, 2011 at 16:13

duffbeer703

2311 silver badge7 bronze badges

What CSV exporting has to do with this question, I don't know. Date formatting in Excel (which is all that's involved in the question) itself is similar to Access, i.e., it's a display format, not a data storage format. Unless you're actually storing the literal string "Jul-09" in Excel, it's actually stored as the numberic value for Jul 1, 2009, just as it would be in Access.
– David W. Fenton
Commented Aug 5, 2011 at 19:59

Add a comment |

1 Answer 1