Keeping spreadsheet name as column header when looping through multiple excel files

Hi. I have a code where I am extracting the second column of all spreadsheets in my folder and putting them into one new spreadsheet. I want the column headers of the new spreadsheet to have the name of the spreadsheet they were originally extracted from.

For instance, one of the 173 spreadsheets in my folder may be named 123.xlsx, another may be named 543.xlsx. My code is going to extract the second column of 123.xlsx and the second column of 543.xlsx. So, in this new spreadsheet, I want the extracted column of 123.xlsx to have a header that reads "123" and the extracted column of 543.xlsx to have a header that reads "543". And so forth, for each file.

Any help would be appreciated. Thank you for your time.

This is my code:

myDir = 'C:\Users\woldey\Desktop\Combine99percentile';
myFiles = dir(fullfile(myDir,'*.xlsx'));
numfiles = 173;
data = [];  
for k = 1:length(myFiles)
    baseFileName = myFiles(k).name;
    fullFileName = fullfile(myDir, baseFileName);
    t= readmatrix(fullFileName);
    if k == 1
        data = zeros(size(t, 1), numfiles);  
    end
    data(:, k) = t(:, 2); 
    end
%end
writematrix(data, '99percentileconsolidated1.csv') 

Answer 1

[Mathieu NOE]

hello

this is just a piece of demo code to show the principle ; first we save the filenames (cell array) using writecell, then the data (writematrix in append mode)

baseFileName  = {'123','ABBA'};
data = rand(10,2);
writecell(baseFileName, '99percentileconsolidated1.csv') 
writematrix(data, '99percentileconsolidated1.csv',"WriteMode","append") 

adapted to your code, this turns into (hopefully without bugs) :

myDir = 'C:\Users\woldey\Desktop\Combine99percentile';
myFiles = dir(fullfile(myDir,'*.xlsx'));
numfiles = 173;
data = [];  
for k = 1:length(myFiles)
    baseFileName = myFiles(k).name;
    fullFileName = fullfile(myDir, baseFileName);
    t= readmatrix(fullFileName);
    if k == 1
        data = zeros(size(t, 1), numfiles);  
    end
    data(:, k) = t(:, 2); 
end
baseFileName_storage{1,ci} = baseFileName;

%end
writecell(baseFileName_storage, '99percentileconsolidated1.csv') 
writematrix(data, '99percentileconsolidated1.csv',"WriteMode","append") 

[CMatlabWold]

Thank you!

For

baseFileName_storage{1,ci} = baseFileName;

what is ci? There is an error with this.

[Mathieu NOE]

hello

sorry, it's a typo from my code and also the line should be inside the for loop

myDir = 'C:\Users\woldey\Desktop\Combine99percentile';
myFiles = dir(fullfile(myDir,'*.xlsx'));
numfiles = 173;
data = [];  
for k = 1:length(myFiles)
    baseFileName = myFiles(k).name;
    fullFileName = fullfile(myDir, baseFileName);
    t= readmatrix(fullFileName);
    if k == 1
        data = zeros(size(t, 1), numfiles);  
    end
    data(:, k) = t(:, 2); 
    baseFileName_storage{1,k} = baseFileName;
end
%end
writecell(baseFileName_storage, '99percentileconsolidated1.csv') 
writematrix(data, '99percentileconsolidated1.csv',"WriteMode","append") 

[CMatlabWold]

They give this new error:

Unrecognized function or variable 'baseFileName_storage'.

Error in Combinecolumns (line 17)

writecell(baseFileName_storage, '99percentileconsolidated3.csv')

[Mathieu NOE]

hi

this sounds strange

can you put a break point at the line

baseFileName_storage{1,k} = baseFileName;

and check what's going on there ?

[Mathieu NOE]

FYI

I retested the code with 3 dummy excel input files (attached)

the code is working as expected with

baseFileName_storage = {'A1.xlsx'} {'A2.xlsx'} {'A3.xlsx'}

also the output file (99percentileconsolidated1.csv) is OK

this is the code I tested (simply changed the working directory)

% myDir = 'C:\Users\woldey\Desktop\Combine99percentile';
myDir = pwd;
myFiles = dir(fullfile(myDir,'A*.xlsx'));
numfiles = 3;
data = [];  
for k = 1:length(myFiles)
    baseFileName = myFiles(k).name;
    fullFileName = fullfile(myDir, baseFileName);
    t= readmatrix(fullFileName);
    if k == 1
        data = zeros(size(t, 1), numfiles);  
    end
    data(:, k) = t(:, 2); 
    baseFileName_storage{1,k} = baseFileName;
end
%end
writecell(baseFileName_storage, '99percentileconsolidated1.csv') 
writematrix(data, '99percentileconsolidated1.csv',"WriteMode","append") 

[Mathieu NOE]

hello again

forgot the excel files - attached now

something else : numfiles should probably be equal to length(myFiles) , so to make the code more robust , I would suggest these small mods :

% myDir = 'C:\Users\woldey\Desktop\Combine99percentile';
myDir = pwd;
myFiles = dir(fullfile(myDir,'A*.xlsx'));
numfiles = length(myFiles); % here
data = [];  
for k = 1:numfiles % here
    baseFileName = myFiles(k).name;
    fullFileName = fullfile(myDir, baseFileName);
    t= readmatrix(fullFileName);
    if k == 1
        data = zeros(size(t, 1), numfiles);  
    end
    data(:, k) = t(:, 2); 
    baseFileName_storage{1,k} = baseFileName;
end
%end
writecell(baseFileName_storage, '99percentileconsolidated1.csv') 
writematrix(data, '99percentileconsolidated1.csv',"WriteMode","append") 

[CMatlabWold]

It works. Thank you!

[Mathieu NOE]

My pleasure !