469,623 Members | 899 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,623 developers. It's quick & easy.

Importing "dirty" Excel file into Access

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

Nov 13 '05 #1
2 3270
"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.

Nov 13 '05 #2
On 17 May 2005 08:54:04 -0700, "nutthatch" <ri*****@184systems.com> wrote:
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


One trick that works well is to make sure the top row is all dirty, so Access
thinks all the columns must be text. Then, you can run queries to detect
problems and do data clean-up, delete the junk row, then use an append query
to copy the cleaned data into a final destination table if you need the data
in correctly typed columns.
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

By using this site, you agree to our Privacy Policy and Terms of Use.