How do I create an Access database from multiple excel files

[lz]

I'm trying to merge multiple excel spreadsheets saved under different file
names into one Access worksheet. All the columns are identical. I'm able to
import the first file into Access but then when I try to go thru the same
process with the next spreadsheet and I choose the existing dabase it gives
me an error message that just says it did not go through? What do I do?

Thanks!

 

[Jeff Boyce]

Please note -- an Access table may look like a spreadsheet, but it is not
one. Access doesn't have "worksheets".

Is this a one-time "loading" of Access from Excel, or does your situation
call for this process to happen repeatedly (e.g., every month)?

Before you plan on 'stuffing' your Excel data into a table in Access and
walking away, there's a couple of considerations. First, why bother? If
you already have the data in Excel, what will having it in Access do for
you?

Second, Access expects (and works best with) well-normalized data. If you
simply stuff your Excel data into an Access table, odds are very high that
it is NOT well-normalized. Both you and Access have to work overtime to
overcome 'sheet data.

That said, a common approach in similar situations is to load the Excel data
into Access but treat it as 'raw data'. You'll need to revisit the data
you're working with and design a well-normalized table structure first.
Then you can use queries to "parse" the raw data into the well-normalized
table structure.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[lz]

Thanks for your response back Jeff.

The reason I'm trying to load the data into access is to create one main
database because I'm afraid at one point I'm going to be running out of space
in Excel. Every week I will be adding about 16000 rows of data so I thought
maybe Access would be the best choice in keeping the data and to manipulate?

 

[Jeff Boyce]

You know your situation better than we do...

16,000 rows of data per week describes the general situation. Now, how many
characters in each row?

So this is NOT a one-time situation. You're looking for a way to handle a
weekly update...?

Which version of the data is the "right" version? The version that's in
Excel, or the version that's in Access? What happens when the data in Excel
that's already been loaded to Access is changed in Excel?

There are a few more issues to resolve before getting down to the "which
button do I push" stage.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[lz]

24 columns with each cell containing about 8 characters so about 192
characters per row.

Yes, new data is uploaded weekly but the old data is not erased; so in
other words I just add new data to the already historical data so the
database will continue to grow. My plan is not to save the excel files
because in reality I'm actually downloading the data into excel from another
program so then the only place I would be storing the data would be Access.

I was thinking of creating 2-3 different tables for items that repeat
themselves so to decrease the amount of data.

 

[Ken Snell [MVP]]

See these articles for example code for how to do this in ACCESS:

Import Data from Specific Worksheets in All EXCEL Files in a single Folder
via TransferSpreadsheet (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#ImpFldWrkFiles

Import Data from A Specific Worksheet in All EXCEL Files in a single Folder
into Separate Tables via TransferSpreadsheet (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpWktFilesSepTbls

 

[Jeff Boyce]

"thinking of creating 2-3 different tables" is not something to undertake
lightly. Access is a relational database ... and "expects" well-normalized
data. If you feed it 'sheet data, both you and Access will have to work
overtime to overcome the lack of well-normalized data.

If "normalization" and "relational" are unfamiliar, plan on spending some
time learning your way up this curve. Even if you decide not to use Access,
you might still decide to use a relational database (e.g., SQL-Server) to
store your data, so the lessons apply.

Here's a back-of-the-envelope calculation ...

You have approx. 200 characters X 16,000 / week, or about 3 Mbytes per
week. In a year, you'd have over 150 Mbytes. If your table structure is
well-normalized, and your tables properly indexed, Access and you can handle
this volume for several years before approaching limits. You could consider
doing this in Access to start/learn, then migrate the data (but not the
front-end/application) to something more robust (e.g., SQL-Server) after
performance becomes an issue...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP