Compiling excel files from subfiles

3 views (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
Jen
Jen on 4 Mar 2019
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!
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!