By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,663 Members | 2,157 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,663 IT Pros & Developers. It's quick & easy.

Importing "dirty" Excel file into Access

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
"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

P: n/a
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.