importing multiple excel workbooks with multiple sheets into a cell array.

Hi,

I have 11 different excel workbooks and all labeled 001 through 011. what i would like to do with them is have a cell matrix with each cell being one of the excel workbooks and each cell within Those cells being a sheet from the workbooks. I have some code where i can import only one workbook at a time but it does not split up the sheets like i would like, instead it just imports all sheets from a workbook into one large table.

clc;

clear;

close all;

fn='001.xlsx';

tBC=[];

opt=detectImportOptions(fn);

shts=sheetnames(fn);

for i=1:numel(shts)

tBC=[tBC;readtable(fn,opt,'Sheet',shts(i))];

end

I also have to manually change the name of each workbook when i want to import a new one which i would prefer be automated up until 011. Im really not the best at MATLAB but am trying to learn so any help would be appreciated.

Thank you.

[Stephen23]

Context:

https://www.mathworks.com/matlabcentral/answers/2083868-changing-names-of-excl-files-automatically-and-converting-into-cell-array

Answer 1

[Stephen23]

Based on my comment to your previous question:

P = 'D:/foo/bar'; % absolute or relative path to the parent directory
S = dir(fullfile(P,'Non*BP*','*S*','*Subject*ECG.xlsx'));
for ii = 1:numel(S)
    F = fullfile(S(ii).folder,S(ii).name);
    E = sheetnames(F);
    C = cell(size(E));
    for jj = 1:numel(E)
        C{jj} = readtable(F, "Sheet",E(jj));
    end
    S(ii).data = C;
end

All of the imported data is stored in the structure S. For example, the 2nd file:

S(2).folder % filepath
S(2).name   % filename
S(2).data   % imported file data in a cell array

If all XLSX files have the same number of worksheets then you could also concatenate the cell arrays into one large cell array:

Z = horzcat(S.data)

Or alternatively you could just use a

https://www.mathworks.com/help/matlab/ref/matlab.io.datastore.spreadsheetdatastore.html

to do this all for you.

[NeedHelp55]

Thank You very much for your help