MATLAB Answers

Jen
0

Compiling excel files from subfiles

Jen
さんによって質問されました 2019 年 2 月 25 日
最新アクティビティ GT
さんによって コメントされました 2019 年 3 月 5 日
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!

  0 件のコメント

サインイン to comment.

タグ

3 件の回答

GT
回答者: GT
2019 年 2 月 27 日
 採用された回答

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 件のコメント

Jen
2019 年 2 月 27 日
It worked, thank you so so much!!
Jen
2019 年 3 月 4 日
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
2019 年 3 月 5 日
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

サインイン to comment.


GT
回答者: GT
2019 年 2 月 25 日

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 件のコメント

Jen
2019 年 2 月 25 日
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?

サインイン to comment.


GT
回答者: GT
2019 年 2 月 25 日

Try the following:
ds = spreadsheetDatastore(pwd,'IncludeSubfolders',true)
where pwd is the root folder.

  1 件のコメント

Jen
2019 年 2 月 27 日
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?

サインイン to comment.



Translated by