xlsread multiple files from multiple folders

I want to xlsread and xlswrite every .xls file within each folder from a group of folders (58 total folders).

This code works for one file:

f = dir('Y:\Projects\Round\test.xls')
sheet = 'Logic';
[num,str] = xlsread(f,sheet,'D5:D5');
n = 675;
    if str == "Test"
        xlRange = 'D1';
        xlswrite(f,n,sheet,'D7:D7')
    end

This is semi-finished code where I think it would work for one folder, but I want it to loop through every folder within a folder.

files = dir('Y:\Projects\Round\*.xls');
sheet = 'Logic';
nFiles = length(files);
for i = 1:nFiles
    fName = files(i).name
    [num,str] = xlsread(fName,sheet,'D5:D5');
    sttd = 675;
    if str == "Teset"
        xlRange = 'D1';
        xlswrite(fName,sttd,sheet,'D7:D7')
    end
end

Problem with this code is that when I get the name of the file and put it into xlsread, it assumes I am working in my current folder and not somewhere else. instead of fName I need the whole directory.

Answer 1

[Rahul]

Hi @Daniel Green,

In order to achieve the task of navigating through directories and subdirectories and reading '.xls' files, consider using the 'dir' function which provides information regarding all the files and directories present in the directory passed as argument.

Further, properties like 'isdir' and 'name' can be leveraged to identify all required subdirectories which can then be iterated through a for loop.

Function like 'fullfile' allows to obtain the full path of the '.xls' file which can then be read using 'xlsread'.

Here is an example:

baseDir = pwd; % Current directory
allFolders = dir(baseDir);
allFolders = allFolders([allFolders.isdir]); 
allFolders = allFolders(~ismember({allFolders.name}, {'.', '..'})); 


for folderIdx = 1:length(allFolders)
    folderPath = fullfile(baseDir, allFolders(folderIdx).name);
    files = dir(fullfile(folderPath, '*.xls'));
    
    % Iterate over each .xls file
    for fileIdx = 1:length(files)
        fName = fullfile(folderPath, files(fileIdx).name); % Full path to the .xls file
        sheet = 'Logic';
        [num, str] = xlsread(fName, sheet, 'D5:D5');
        n = 675;
        if strcmp(str, "Test")
            xlRange = 'D1';
            xlswrite(fName, n, sheet, 'D7:D7');
        end
    end
end

Refer to the following MATLAB Answers for more information:

https://www.mathworks.com/matlabcentral/answers/401204-how-do-i-read-folders-with-subfolders

https://www.mathworks.com/matlabcentral/answers/437494-how-to-loop-through-all-files-in-subfolders-in-a-main-folder

The following MathWorks documentations can be referred:

'dir': https://www-test1.mathworks.com/help/releases/R2018a/matlab/ref/dir.html

'fullfile': https://www-test1.mathworks.com/help/releases/R2018a/matlab/ref/fullfile.html

Thanks.

Answer 2

[Stephen23]

S = dir('Y:\Projects\*\*.xls');
W = 'Logic';
for k = 1:numel(S)
    F = fullfile(S(k).folder,S(k).name);
    [num,str] = xlsread(F,W,'D5:D5');
    sttd = 675;
    if strcmp(str,'Teset')
        xlswrite(F,sttd,W,'D7:D7')
    end
end