It is my understanding that you want to extract column data from excel sheets in subdirectories and rename each column by the sheet name and write all the extracted columns to one file.
To insert columns into a new table with a certain column name you can use the dot syntax with parenthesis and quotation since the name of the sheet can be not a valid MATALB identifier, refer to this documentation for more details on tables. The code below shows how to read the files from subdirectories and extract the columns and rename them as filename_sheetname (to avoid the file from being if sheet name is same) The final table is written as an output excel file. The sample data used was a folder with two excel files, you can replicate the same using the commands below.
mkdir demo2
load patients
T = table(Gender,Smoker,Weight);
T2= table(Gender,Smoker,Height);
writetable(T,'demo2/allPatientsBMI_Weight.xls');
writetable(T2,'demo2/allPatientsBMI_Height.xls');
dircontent=dir(); %current directory listing
subdirs=dircontent([dircontent.isdir]) % filter all dir from current directory list
data=table(); %final table
%iterate all subdirs
for i=1:numel(subdirs)
if strcmp(subdirs(i).name,'.') || strcmp(subdirs(i).name,'..')
continue
end
%obtain all files from the subdir
subdirPath=fullfile(subdirs(i).folder,subdirs(i).name);
subdirContent=dir(subdirPath);
subdirsFiles=subdirContent(~[subdirContent.isdir])
%iterating the subdirs for file
for j=1:numel(subdirsFiles)
filePath=fullfile(subdirsFiles(j).folder,subdirsFiles(j).name)
tempData=readtable(filePath,"VariableNamingRule","preserve");
sheetName=sheetnames(filePath);
colName=extractBefore(subdirsFiles(j).name,'.')+"_"+sheetName
data.(colName) = tempData.Gender;
end
end
writetable(data,'output.xls')
