Retrieving data from Excel in multiple sub folders with different names

14 views (last 30 days)
Hi everyone.
I'm just progressing beyond basic for loops and matrix manipulation in Matlab so this one is new for me.
I've got a folder in my working directory called "Test Data"
Inside "Test Data" I've got many subfolders, they are numbered like this:
PR1.2
PR1.3
PR2.1
PR2.2
etc....
The point is their names are different and there isn't really a pattern so I have to dive into each directory sequentially without being able to reference their name, ie "go into first folder, then go back, then go into the next folder"
Inside each PR subdirectory is an excel spreadsheet, again it's name is more or less random. They are named by the date they were exported, so again no pattern here.
Inside each spreadsheet I'm after the value in one cell.
So the pathway would look like this:
Test Data
PR2.2
Results.11.23.2021
Cell C5 = ......
Any idea how I could write a code to go into each PR subdirectory, then into each spreadsheet and retrieve the value I need?
Much appreciated.
B

Answers (1)

dpb
dpb on 23 Nov 2021
Edited: dpb on 23 Nov 2021
There's a lot of structure in those directory names -- you have two choices in how to access them and write the code to do so in whatever order you wish -- or specific folder(s).
First, just iterate over the two indexing variables --
for i=1:nPrj
for j=1:nTst
dn=compose('PR%d.%d',i,j);
% use variable dn (for directory name) here to access the given folder
d=dir(fullfile(dn,'*.xls*')); % return dir() struct of all files in the folder
% process each file here...
for k=1:numel(d)
data=readtable(fullfile(d(k).name,d(k).folder));
% do whatever with this file data here...
end
end
end
Alternatively, just get a directory listing of all the PR* folders(*) and iterate over it...
d=dir('PR*.*'); % dir() struct of all PRn.m folders
d=d([d.isdir]); % save only those that are folders (in case you also have files which would be a bad idea)
for i=1:numel(d) % now just iterate over the folders and then do another dir() inside each...
f=dir(fullfile(d(i).folder,'*.xls*'));
for j=1:numel(f)
data=readtable(fullfile(f(k).name,f(k).folder));
% do whatever with this file data here...
end
end
(*) And, of course, you don't have to iterate over all folders,
d=dir('PR1.*'); % dir() struct of all PRn.m folders
gives you all the subfolders for the chosen top level folder...and, you can build that string programmatically, as well, it doesn't have to be a literal string.

Community Treasure Hunt

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

Start Hunting!