Consolidating numerous Excel worksheets

[Excelgerman]

Consolidating numerous Excel worksheets into a unified database is my current task. Although I'm not particularly knowledgeable about Access, I excel in Excel. My company has decades of data scattered across spreadsheets, which we're keen on integrating into a database for streamlined querying and report generation. The data encompasses various numerical values such as time, date, and temperature. To gauge feasibility, I'm focusing on our most intricate spreadsheet, which spans an entire month with individual tabs for each day. I've created a new tab to aggregate daily data into a monthly table for importation into Access. Experimenting a bit, I've managed to create new tables in Access for each workbook. Opting for linking to the data source seems most advantageous, ensuring real-time updates in Access if the source data undergoes changes. Extrapolating over the past decades, this would entail approximately 200 linked tables, with an additional one each month, a prospect I'm comfortable with. However, I'm deliberating on the most efficient method to consolidate all this information into a single table, given that all tables share identical 46 fields. Any insights would be greatly appreciated!

[Welshgasman]

Any reason for such a small font?

I would likely look at processing each file in turn and appending to a table, or a temp table if the data needs to be massaged first.

https://www.google.com/search?q=acce...hrome&ie=UTF-8

[NoellaG]

Starting from Excel structures for creating a database structure is not the best of ideas. Relational databases work differently: they keep all data that describe one topic in one table so one table would contain all data for all months and years) and avoid having redundant data (not having the same data in different tables). Best spend some time on database design and normalization before you start to create your tables.

[mike60smart]

Hi
Can you upload a zipped copy of a daily spreadsheet containing no confidential data?

[madpiet]

use PowerQuery. then when you get everything consolidated, import that into Access.

PowerQuery absolutely blows the doors off VBA. Go on YouTube and look up maybe Rick de Groot (BIGorilla) or Chandeep Chhabra (goodly) or Purna Duggirala (Chandoo) and watch one of their videos on even simple PowerQuery. (Pick one, there are tons). Or one of Mynda Treacy's.

Seriously. don't use Access for the cleaning and transforming. It's terrible. And PowerQuery is in Excel. so you can do it all inside. And folder sources (LOTS of sources are built in, so you can process an entire folder at once if you want. (The problem is that VBA uses strong typing, and so does Access. The only way you could do it 100% in Access is to declare every column text and then use a variant data type in your code to clean it up.

[Welshgasman]

use PowerQuery. then when you get everything consolidated, import that into Access.

PowerQuery absolutely blows the doors off VBA. Go on YouTube and look up maybe Rick de Groot (BIGorilla) or Chandeep Chhabra (goodly) or Purna Duggirala (Chandoo) and watch one of their videos on even simple PowerQuery. (Pick one, there are tons). Or one of Mynda Treacy's.

Seriously. don't use Access for the cleaning and transforming. It's terrible. And PowerQuery is in Excel. so you can do it all inside. And folder sources (LOTS of sources are built in, so you can process an entire folder at once if you want. (The problem is that VBA uses strong typing, and so does Access. The only way you could do it 100% in Access is to declare every column text and then use a variant data type in your code to clean it up.

Makes me wish I was still working then I would find a need for that.

[madpiet]

Can you obfuscate enough of maybe two sheets so I can see what you're trying to do? (Just easier to see it instead of conjecturing about how it's structured etc). Maybe I can sort out how to do it in PowerQuery so that the resulting cleaned table lands in Excel. Then I can link to that in Access and just grab the new records.