I was very happy (and surprised) to see an article on this topic. It's about time.
I haven't completed reading the piece yet, but I wanted to say, I've been dealing with import/export issues between Excel and Access for about 13 years now, and that includes getting a multitude of different types of files into Access - various delimited files, fixed width files, and so on.
So if anyone is struggling, and Susan's excellent article did not answer your question, I MIGHT be able to help - feel free to email me, and if I can, I will.
I used to experience a lot of frustration with this, and there are some very simple precautions you can take to minimise unwanted conversions.
But I mainly just wanted to express my delighted SURPRISE to even SEE an article on this topic - one of the things that most people never talk about or deal with, and I can see for myself that many, many people just use the import wizard, import the data, and NEVER LOOK AT IT - and often, more often than not, the move from Excel to Access has damaged some or most of the data.
And you don't always get an IMPORT ERROR when there is a problem, either - you can't count on that.
I suppose for me, it goes back to a very basic concept that is so, so often overlooked - you have to SPEND TIME actually LOOKING AT the data.
Most people "are too busy" or think, "that's why I have these tools, the import wizard will take care of it". But Microsoft maybe should have given us some proper disclaimers about import/export wizards and maybe they should have been a little more upfront about just how difficult it can be to get Excel and Access to talk.
Anyway, if anyone else is interested, or you have a current difficulty with importing, drop me a line - staffordd@stirling.gov.uk.
I will try to help if time permits, and if I know the answer, which I may or may not! Even with many years experience, I approach each new import task as a potential disaster waiting for me to avert it by exercising skill and care in ensuring that the data quality is NOT compromised.
That's why I am here. I think data quality is paramount, it's our data that MATTERS, so I become almost...resentful when a program such as Excel "decides" when something is a date (when it isn't) or decides to reformat something just because it feels like it !! What a nerve !
Stick with TAB DELIMITED TEXT FILES for input. If I have one primary rule, that is it. DO NOT USE COMMA QUOTE DELIMITED or CSV or EXCEL files - use a TAB DELIMITED TEXT FILE. None/Tab to be precise.
Happy importing !
Dave
