Combining Hundreds of tables

[Commander Vimes]

Hello, I have a two part question which i'm hoping someone can help with.

I am going to need to combine/append several hundred tables into single tables for use. In this case i am asking users to complete their own weekly information into 3 tables within a worksheet. There are several hundred users with their own worksheets. all tables are the same format and require the same information, but are required to be kept separately.

My intention is to combine each one of the 3 tables in the worksheets from all individuals into a final large table.

So 3 tables (a, b and c) in each worksheet x 500 individual worksheets, into 3 final combined tables (a combined, b combined, c combined)

I know that Power Query/ Get & Transform can combine/append tables from multiple sources so i was intending on using this, hopefully using the get data from folder functionality to speed things up.

My question therefore is, does this sound like the best approach and does anyone have any idea on how well excel would cope, particularly in terms of speed. (I know it can handle millions of rows of data, but what if there are tens of thousands, but coming from 500 worksheets?)

 

[VBA Geek]

Power Query can handle this no problem but it may take a while to refresh the data, importing from excel is not as far as when importing from a database. Give it a shot and see how long it takes, i'd say around 30 mins but I could be totally off

 

[Commander Vimes]

Cheers, yeah i'll give it a go and see how long it takes to run. Hopefully it's close to the 30 min mark or below

 

[SpillerBD]

My money is on a longer time frame.
Had a project with about 100 files, single tables. Ran about 40 minutes.
Your mileage will vary depending on CPU clock speed and read/write of the storage device.

 

[Commander Vimes]

Hmmm ok, cheers. Any ideas on whether breaking the combination into smaller chunks would be more efficient? eg if i had 500 files, rather than get all 500 at once, break them into smaller 100 sheet chunks, query them into a table. Then use each of those to feed into a final sheet? in terms of cpu usage would it be less strain on the comp?

 

[SpillerBD]

It will depend on the Query processes. If you have any Merge steps, it will depend on if each chunk needs to look outside of itself to get the right answer.
I am currently in similar situation and working on breaking it down into chunks. Hopefully I won't have to do each file separately. If I need to to I hope I can see how to easily generate the multiple query groups.