Merging many excel worksheets into one database

[Ramblin Racer]

First, let me start off by saying that I know very little about Access. I am however very good at Excel. My company has 20 years of data in Excel spreadsheets and continue to use them, but we are looking at ways to get that data into a database so it can be queried and used to generate reports if needed. Our data is all numerical, things like time/date, temperature, etc... I am trying to determine if this is possible so I am trying to work on the most complex spreadsheet we have first. The Excel workbook is for an entire month, with a tab for each day of the month. I have made a new tab to pull data from each day (tab) to compile a monthly table that I want to import into Access. I have played around with it a little and can get each workbook to make a new Table in Access. I have found that Linking to the Data Source would be the most ideal, that way if changes were made to the source data, it would be reflected in Access. What that would look like for the past 20 years worth of data is 240 linked tables, plus a new one each month. I am okay with that, but what would be the best way to get all that information into one Table? Each table has the same 46 fields, so they all match up. Thanks!

[Micron]

You REALLY need to read and learn database normalization. Your post reveals what is a common mindset of the Excel proficient crowd - trying to build a database with an Excel approach. If you do this, you will struggle to perform some of the simplest tasks from the beginning and will find other tasks impossible. There are ways to deal with linked spreadsheets if that's what you must have. It's probably not the best approach but I think it's too early to worry about that. Maybe check out normalization first.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

[Ramblin Racer]

Thanks, I am trying to learn as much as I can and appreciate the direction. I just don't want to put weeks worth of work into something that isn't going to achieve what we want.

Unfortunaly, this particular Excel Spreadsheet is so complex in the calculations that it does and the exporting to reports, that we have not had any luck in finding a way not to use it. We have been trying to get this done for several years and have even looked at other software products in our industry, they all get to this one spreadsheet and can not replicate the calculations so we have to start over.

[Gicu]

Linking and maintaining the links for) 240+ plus tables would be a lot....
The linking can be automated via VBA if the 240 Excel file are all in the same folder (many examples on the web, here is one:https://www.access-programmers.co.uk...mation.233117/). Once you have them linked you can put them together in a "table" by using a union query:
"SELECT * FROM tblExcel1 UNION SELECT * FROM tblExcel2 UNION......SELECT * FROM tblExcel240;"
Now a better approach would be to use an actual Access table and load the data in it as needed; you would run a delete query first to empty the existing records then a series of appends from each of the linked Excel tables (once the Access table is loaded you can delete the links to avoid the clutter).

Cheers,

[Ramblin Racer]

Thank you, I will look into this and you are right, 240+ tables is very cumbersome. We did create a form that we could complete every hour with all the data we need, and it went into a single table. The problem again was the calculations that we needed with that data, was just too many moving parts for Access and we were still inputting the same data into an excel worksheet creating redundant work. Which saying that has me thinking, Maybe that is what I need to look at, use Access to input and store and export the data to Excel.

[Gicu]

Can you post a sample Excel file with some "dummy" data so we could get an idea? Yes, you can input data in Access and have it exported to Excel (but usually not directly via a linked Excel as that is non-updatable). Again lots of code available to do that. You could build in your form a file selector to allow you to choose which spreadsheet you want to look at and\or edit.

Cheers,

[Micron]

There wouldn't be 240 tables if it was properly normalized.

[Gicu]

Of course, but the OP mentions industry-wide spreadsheet with "complicated calculations" and seems unwilling to start from scratch so I am offering suggestions that would work with what they currently have.

[CJ_London]

Fascinating to think nothing has changed for 20 years. Excel data is not indexed so working through linked tables will be very slow.

You mention one reason is if the data changes - but realistically what is the scope for that? Last month? Year?

Would be better to import the data to access table(s) and run updates as required.

Would be helpful if you could upload an example workbook to understand the complex calculations. Access does it differently, might be better, might be worse 😀

[ArviLaanemets]

First, let me start off by saying that I know very little about Access. I am however very good at Excel. My company has 20 years of data in Excel spreadsheets and continue to use them, but we are looking at ways to get that data into a database so it can be queried and used to generate reports if needed. Our data is all numerical, things like time/date, temperature, etc... I am trying to determine if this is possible so I am trying to work on the most complex spreadsheet we have first. The Excel workbook is for an entire month, with a tab for each day of the month. I have made a new tab to pull data from each day (tab) to compile a monthly table that I want to import into Access. I have played around with it a little and can get each workbook to make a new Table in Access. I have found that Linking to the Data Source would be the most ideal, that way if changes were made to the source data, it would be reflected in Access. What that would look like for the past 20 years worth of data is 240 linked tables, plus a new one each month. I am okay with that, but what would be the best way to get all that information into one Table? Each table has the same 46 fields, so they all match up. Thanks!

Even for Excel, this design is suboptimal!
It looks like in every monthly workbooks you have to calculate a monthly summary sheet which collects data from up to 31 daily sheets. And e.g. to get a yearly report, you have to calculate it over 12 different workbooks! No wonder your formulas are complex!

You can't transfer your old data into access, and continue with adding new data into old design Excel workbooks (from your post, I understood this is the case - for some reason you don't want switch all data entry into Access) - with current design you have to repeat the same task again and again, without any hope to add new data into Access automatically. So you have to:
1. Design a better Excel workbook, which can be linked to Access workbook, so all new data can be read in automatically. And my advice is, for reading previous data into Access, create another workbook with tables like the one you'll use in future, which reads data from selected old workbook. When you import old data, you link this workbook to Access DB temporarily, in temporarily linked workbook set up the source workbook from where data for given month are read in, and in Access DB run a procedure which imports all data from temporarily linked workbook. Then you set up another source workbook, etc.;
2. Design an Access DB.

My advice is in linked workbook(s) to use table(s) organized vertically on single sheet (like a database). E.g. table with columns like
Date, Datafield1, Datafield2, ...
Another advice is, define the data entry table as Table. Then in any formulas you use in this workbook, when you use Table formula syntax, there is no danger some data are left out from datarange (unless you leave entirely empty row into Table, the Table is expanded automatically whenever a new row is added). And unless you mess up with column containing formulas, those formulas are expanded into new rows automatically.
And the is no need to fear tat there is many rows in such table - user's can easily set Autofilter, do display only e.g. rows up from certain date (A task not more difficult as entering a date in valid format).

Users simply add new rows when reporting (you have to ensure there are columns provided for all needed identifiers). Based on your post I think such workbook may work efficiently with data for one year, but maybe it will for you enough to have only previous and current month kept there. All older entries as decided will be cleared periodically, to keep workbook size in reasonable limits.
In case there is a need for users to have to see some summaries in Excel workbook, you can design any number of report sheets (e.g. MonthlySummary), where users can select some parameter(s), and according report is displayed.
This workbook will be linked to Access database. In Access database the linked table is used only as source of data for procedure(s), which will read info from linked table, check is this new info or not, and when yes, then updates according Access DB table(s) with this info. The import procedure is run from some Event (OnOpen event of some form, OnClick event of button), or from Window's Task Manager. Or you can design back-end for Access DB as e.g. SQL Server database, and schedule there a Job which runs the import procedure (with SQL Server, you can't link Excel table, you have to read data using a pass-through query).

[NoellaG]

If you're working for 20 years with this solution, I suppose you can do it for 1 year more. Take this time to analyse what you want to achieve, but the crucial point: don't start from the Excel calculations! For example: start to create user stories like: I want to see the average temperature per region per month in a chart. Or draw a picture of the end reports you want to see. Once you have a clear picture of what you want, look at the data you have and where you get them from. Then decide how you want to work: do you want to work with a web based application, an app on your smartphones, a classic work station application, ...? Then decide the development platform: office application, office 365 power app, web development, ERP solution with added features, ... . The project cycle analysis + decisions + development and testing will take some time, but in the end it will be a cheaper solution than sticking with the current approach.