Tools to import complex Excel spreadsheets into ACCESS

[Guest]

Hello,

We have a client who has a set of 1,000+ Excel workbook files that they call
'customer records'. Each workbook has 11 worksheet tabs and they are
interested in importing and reporting on (in Access) 2 of those 11
worksheets.

1) The first question is: Is there a tool of some kind that can selectively
import worksheets from multiple Excel workbook files if all of the files have
the exact same set of worksheets within them? As mentioned above, I only
want 2 of the 11 worksheets imported from each file.

One of the above worksheets of interest is made of separate cell ranges that
are made to look like separate 'tables' within that worksheet. In other
words, a regular import into Access won't be pretty because columns mean
different things the farther down the worksheet you travel.

2) So, the second question is: Is there any way to name a Range of cells
that can be extracted from a particular worksheet into an Access table, and
be able to do this for the 1,000+ files they are interested in reporting on?

I do realize there is some measure of ridiculousness in my above request,
but is there anything I can come back to the client with besides, 'You've
made your bed (by inputting all of your data into unstructured Excel
spreadsheets), and now you have to sleep in it?'. I figure that some of you
have come across similar challenges and would be interested in any creative
ways you've seen to deal with such a challenge.

Thanks.
Pat Dools

 

[MH]

There is certainly no automated way to import individual ranges scattered
all over a worksheet, as I'm sure you are aware already.

If the naming convention of the worksheets is EXACTLY the same in each of
the 1000+ workbooks and the data is stored in EXACTLY the same format in
each identically named worksheet and that format is a table resembling a
database table with strict datatypes (i.e. no text in the numeris fields
etc.) then it may be possible to use VBA in Excel to export each sheet
individually to a folder as a csv file and then stitch them all together
using a tool such as text collector:

http://bluefive.pair.com/txtcollector.htm

Other than that it's "bite the bullet" time.

Good luck

MH

 

[(PeteCresswell)]

Per Pat Dools:

I do realize there is some measure of ridiculousness in my above request,
but is there anything I can come back to the client with besides, 'You've
made your bed (by inputting all of your data into unstructured Excel
spreadsheets), and now you have to sleep in it?'. I figure that some of you
have come across similar challenges and would be interested in any creative
ways you've seen to deal with such a challenge.

I've been importing data form .XLS files that are pretty much
what you describe just about all day, every day, for about a
year.

What I've come around to is:

- It's innately time-consuming. Figure a factor of 10 or 12
over just importing formatted data.

- It is best done in stages:
----------------------------------------------
1) Validate the sheet being imported.
Are all the column headers present?
Is there any information where there should not be?

2) Pull the data into a series of work tables

3) Validate the data once the info is the work tables.
- Do the date fields contain dates?
- Do the amount fields contain numerics?
- Are the value ranges reasonable
- If you've normalized certain values into lookup tables,
do you need to add more records to a lookup table bco
this sheet's data?

4) Maintain any lookup tabled that you are using to normalize
recurring values.

5) Once all the validation is done and passed, import
from the work tables into your app's back end.

 

[PY & Associates]

How about rearranging all files and sheets into a clean Excel workbook
before importing into Access please?

 

[MH]

With over 1000 workbooks that may be tricky, unless the OP is using Excel
2007 then the row limit is 65,536! Which leaves roughly 65 rows per
workbook.

Besides, it's still a job that nobody will volunteer for.

MH

 

[Guest]

There doesn't seem to be a clean way to pull this off, but of course, the
client wants it done quickly and cheaply. I'll take a look at the Text
Collector, MH, to see if that can be helpful. Thanks for the feedback, all!

 

[MH]

Just to confirm:

You have to export the excel sheets as text or csv for text collector to do
it's thing.

MH