How can I reduce file size of workbook referencing sheets in another workbook?

[tmwsiy]

So I have a project where is receive 1500+ .csv sheets of data. Each file size is about 12kb and contains one column with timestamps, and one column with data that i need to extract based on the timestamp. Each sheet is sorted and grouped (these are power readings from rack PDUs in a data center, each sheet represents 1 PDU and each rack has 4 PDUs, they need to be grouped by rack). I breakup the 1500 sheets into chunks of around 100 for workability, and have 15 of these groups.

I process each individual sheet as a separate tab in one Group Workbook. I then have a Template file that simply references external link to the Group Workbook (i do this to sort/associate each PDU to the racks and this will be an ongoing thing for the immediate future). The file size for each Group set is around 6MB, but after it is processed and pulling data from external links, each of the Template files comes out to 100MB+, some are 200+ and can take a few minutes to open and save.

My process is clunky and inefficient, but it works. However, I will likely be processing more data in the future and expect larger files sizes and longer loading times. I'm sure there is a more feasible option that I am missing. It's becoming unworkable and I need some help to make it better. Thanks

Raw data that I receive (individual rPDU readings) ~12kb

I group them by location and process associated sheets as separate tabs in an index workbook. ~600kb

I then use a Template to sort/group is reading and reference links to the Index Workbook. This puts all the PDUs together sorted by rack. Then I total the readings, and get Peak, Avg, and Min. This comes out to 100MB+ file size.

 

[bobsan42]

I've always tried to keep similar data together instead of scattering it across multiple sheets - much faster and easier to process and analyze.
So instead of keeping each csv as separate sheet, I would recommend to keep them all in one sheet by adding a column to contain the name of the file/rack.
Something like this:

number of rows is 1048576, so for 1500 files you can have about 700 lines /per file (12 KB will be about 450 lines)
it may be a bit on the limit, but you can always split it ini two or more sheets instead of 1500+.
A xlsx file with 1 sheet, first three columns completely filled in the manner above is about 12 MB.

Then you can use PowerQuery (the data will have to be stored in a table(s), though) or anything to process and analyze the data. So the only tedious process will be importing all the data from CSVs.

 

[RoryA]

Power Query can import all the csv files too.

 

[tmwsiy]

I've always tried to keep similar data together instead of scattering it across multiple sheets - much faster and easier to process and analyze.
So instead of keeping each csv as separate sheet, I would recommend to keep them all in one sheet by adding a column to contain the name of the file/rack.
Something like this:
View attachment 118319
number of rows is 1048576, so for 1500 files you can have about 700 lines /per file (12 KB will be about 450 lines)
it may be a bit on the limit, but you can always split it ini two or more sheets instead of 1500+.
A xlsx file with 1 sheet, first three columns completely filled in the manner above is about 12 MB.

Then you can use PowerQuery (the data will have to be stored in a table(s), though) or anything to process and analyze the data. So the only tedious process will be importing all the data from CSVs.

Thanks. Initially I imported all the grouped .csv (100 .csv per group) and then I built the 'template' within the workbook with internal links. But each week I will be going through new data sets, so the tabs are removed and added for each new set. This breaks the links and then I have to manually re-link everything. So I separated it, and now the file size is becoming unworkable. I guess I should have taken a few steps back and really simplified this...Is there a way to used static links within the workbook that self-references sheets within the workbook. These 'data sheets' will be removed and replaced weekly. Anytime I replace with a new data set, the links go #REF!.

 

[bobsan42]

Thanks. Initially I imported all the grouped .csv (100 .csv per group) and then I built the 'template' within the workbook with internal links. But each week I will be going through new data sets, so the tabs are removed and added for each new set. This breaks the links and then I have to manually re-link everything. So I separated it, and now the file size is becoming unworkable. I guess I should have taken a few steps back and really simplified this...Is there a way to used static links within the workbook that self-references sheets within the workbook. These 'data sheets' will be removed and replaced weekly. Anytime I replace with a new data set, the links go #REF!.

I think you're missing my point. Put everything on one sheet and you will not need references to worksheets. Or am I missing something here?
Also, in my experience, changing a file too frequently may overload it with unneeded/junk data and eventually bring to file corruption.
If you still insist on using your original approach I would suggest to import the csvs in a new file every time and then just update the links to the new filename (if most of the sheet names remain the same).