Split an excel file into separate workbooks

Hi there,

I have an excel file with 500,000 rows. I want to split this file into 200 separate workbooks and put them into a single struct in MATLAB for later analysis. Separation should be based on 200 different categories (strings) I have in one of the columns (Indeed, column no. 8, which has 200 strings in it that represent different type of buildings; They are like 'Steel01', 'Wood08', 'Masonry02', 'Concrete13', etc. All in all 200 different strings). Since the name of the strings are completely different I have no clue how I can do this! Any help will be highly appreciated. Thanks!

Answer 1

[Guillaume]

readtable your excel file, then use findgroups and splitapply to split your table.

[Antonio]

Tnx, but how do I put the data in struct after that? I've just never worked with those commands you mentioned and that's probably why I have no clue!

[Guillaume]

You haven't given enough details about what (and more importantly why) you want to do for me to be able to answer that. What goes into the fields of the structure? Should it be a 200x1 structure with just one field containing the relevant workbook section? The same with another field containing the key? A scalar structure with one field for each workbook section (a bad idea!)? Something else?

It's possible that splitapply is not the right tool. A loop or old-school accumarray may work better for you. E.g.: to split a table according to the unique values in column 8:

%yourtable: table to split according to unique values in column 8
[groupid, groupname] = findgroups(yourtable{:, 8})
subtables = accumarray(groupid, find(groupid), [], @(rows) {yourtable(rows, :)});

Conversion into a struct array with key:

subtables = struct('key', groupname, 'table', subtables)

But perhaps, the best answer is to advise you not to do any of this splitting. Simply apply your analysis to the portion of the whole table that is relevant. Whenever you were going to analyze the 4th subtable, you analyse yourtable(groupid == 4, :) instead.