Importing multiple excel worksheets to one table.

  • Thread starter Thread starter Nigel Watkinson
  • Start date Start date
N

Nigel Watkinson

I'm a beginner with Access and have hit the following
problem.

I have an Excel spreadsheet with a number of workbooks.
Each workbook contains a different set of columns; some
columns occur in all workbooks but most columns only
appear in 1 or 2.

I want to import all the workbooks into 1 table and then
split out into sub-tables. I have manually created a
table that has entries for every possible column but only
the first workbook imports, presumably because the column
names and order of this one match with the table but
subsequent ones don't. Reordering columns in the
remaining workbooks doesn't help. There are too many
columns for me to add 'dummy' columns to every workbook!

Is there any way of mapping excel column names to the
table so that I can do this? Or of course, another way
of achieving just one table?

Any hints welcomed.
Thanks,
Nigel Watkinson.
 
Hi Nigel,

You'll need to import or link a worksheet to a separate, new (and
temporary, because you'll delete it later) table. Then create and run an
append query that maps the fields in the temporary table onto those in
your "real table". Then delete the temporary table and repeat with the
next worksheet.

Obviously I have no idea of the real-world entities you're modelling.
But if when you say "split out into sub-tables" you mean normalisation,
it's usually possible to move the data directly from the temporary
tables into the normalised structure without needing to bring it
together in one big table with a very large number of columns.
 
Hi John,

Thanks for the reply. I was obviously trying to go in
completely the wrong direction by thinking that dealing
with one table was the easiest way to get started.

I did mean normalisation, so I'll have a go at your
suggestion there as well. If I break it, I've still got
the source data!

Thanks again.
 
Back
Top