Combining Hundreds of tables

Commander Vimes

New Member
Joined
Aug 8, 2016
Messages
11
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?)
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top