Compiling excel files from subfiles

I have 200 excel files, each in their own subfolder of one big folder. They need to be compiled into one master excel sheet. How can i identify the excel sheets in the subfolders and put them into one big excel sheet?

Each excel sheet is named differently but all have "-inflection" at the end of the name.

Thank you!

Answer 1

[Gareth]

There might be a more elegant way than what I am proposing but this should work:

ds = spreadsheetDatastore(pwd,'IncludeSubfolders',true);
ds.Files = ds.Files(contains(ds.Files,'inflection'));
ds.readall();

This way you can use string manipulation to choose which files you want the DataStore to use. Probaly you can also do this in the creation of the ds.

[Jen]

Sorry, last thing hopefully! I'm getting an error that says undefined function 'contains' for input arguments of type 'cell'. What does this mean?

[Gareth]

I am using R2018b, and that seems to work for me... it could be that contains in your release does not support the cell:(

I am sorry for not doing this right the first time... but this will work (less elegant) but still:)

ds = spreadsheetDatastore(pwd,'IncludeSubfolders',true);
aux = string(ds.Files);
ds.Files = ds.Files(aux.contains('inflection'));
ds.readall();

If this does not work, which version of MATLAB are you using?

[Jen]

Agh! it still gives me an error for undefined function 'string' for input argument 'cell'

I'm using R2016a.

[Gareth]

Ah okay, now I understand. Sorry I should have asked sooner. I got hooked on the latest releases and MATLAB has become easier for these cases... anyway.. .here is a very crude way of solving this... but it should work in R2016a (I don't have that version installed on my machine).

ds = spreadsheetDatastore(pwd,'IncludeSubfolders',true);
idx = [];
for i = 1:length(ds.Files)
    if strfind(ds.Files{i},'inflection')
        idx = [idx;i];
    end
end

ds.Files = ds.Files(idx);
data = ds.readall();

[Jen]

It worked, thank you so so much!!

[Jen]

Hey GT,

I have a follow-up on this question, is there a simple way to put the subfolder name before each data set on the excel folder? Or would that require the name being in the excel data? Thanks!

[Gareth]

ds = spreadsheetDatastore(pwd,'IncludeSubfolders',true);
idx = [];
for i = 1:length(ds.Files)
    if strfind(ds.Files{i},'Copy')
        idx = [idx;i];
    end
end
out = table();
for i = 1:length(ds.Files)
   data = ds.read();
   filepath = fileparts(ds.Files{i});
   data.folder = repmat({filepath},height(data),1);
   out = [out;data];
end

out

Answer 2

[Gareth]

Hi Jen ,

I am using R2018b, and there is a wonderful datatype: datastore which does this for you. A variation of it is the spreadsheetdatastore.

https://nl.mathworks.com/help/matlab/ref/matlab.io.datastore.spreadsheetdatastore.html

if you do something like:

ds = datastore('*inflection*.xlsx');
mydata = ds.readall();
writetable(mydata,'mymasterexcelfile.xlsx');

This should give you what you are looking for. I would spend sometime looking at datastores and the spreadsheetdatastore as they are both very useful comands.

[Jen]

Thank you for your answer!

I'm trying this and getting an error that says "Cannot find files or folders matching: '*inflection*.xls'." Is this because the file name is longer than just inflection?

Answer 3

[Gareth]

Try the following:

ds = spreadsheetDatastore(pwd,'IncludeSubfolders',true)

where pwd is the root folder.

[Jen]

Thank you! That got rid of the error, but now it is compiling all the excel files not just the inflection ones, can i add the inflection.xls somewhere to identify those?