Compiling excel files from subfiles

1 view (last 30 days)
Jen
Jen on 25 Feb 2019
Commented: GT on 5 Mar 2019
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!

Accepted Answer

GT
GT on 27 Feb 2019
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.
  7 Comments
GT
GT on 5 Mar 2019
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

Sign in to comment.

More Answers (2)

GT
GT on 25 Feb 2019
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.
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.
  1 Comment
Jen
Jen on 25 Feb 2019
Edited: Jen on 25 Feb 2019
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?

Sign in to comment.


GT
GT on 25 Feb 2019
Try the following:
ds = spreadsheetDatastore(pwd,'IncludeSubfolders',true)
where pwd is the root folder.
  1 Comment
Jen
Jen on 27 Feb 2019
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?

Sign in to comment.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by