"nutthatch" <ri*****@184systems.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
I want to be able to import an Excel spreadsheet into Access 2K using
the macro command Transferspreadsheet.
However, the file I am importing (over which I have no control)
contains some records that are "dirty" i.e. the field contents do not
comply with the expected format (date/time) and they end up in a
seperate table of import errors. (The records in "error" are actually
empty fields.)
This is a regular event and I do not want to have to manipulate the
Excel spreadsheet before I import it.
I want to be able to import all the data into a table and then "clean"
it up using an update query according to standard parameters.
Any suggestions would be appreciated
Richard
Importing dirty spreadsheets is never much fun. After trying a number of
different solutions my favourite is to use Excel automation. It is not the
fastest, but it gives you the most flexibility when you are cannot rely on
the data format. Other solutions, eg creating linked tables can be quick if
the data is consistent, but if not, I open the spreadsheet and go through
each cell importing the data into a 'plain' text table (that is, each field
is text, not indexed, not required, 255 long).
That is the first stage, after which you can go about trying to convert data
into its proper format and writing the logic which decides what to do when
someone from Human Resources has written 'no idea' in the date of birth
column. Your import routine could either write the record to an 'errors
table' and say the record can't be imported or delete the value, show a
warning but still import the record.
At the end of the day, there is no single standard solution and the more
time you put into your import routine, the better it will be.
