Keeping spreadsheet name as column header when looping through multiple excel files
1 vue (au cours des 30 derniers jours)
Afficher commentaires plus anciens
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')
0 commentaires
Réponse acceptée
Mathieu NOE
le 1 Mar 2022
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")
8 commentaires
Plus de réponses (0)
Voir également
Catégories
En savoir plus sur Spreadsheets dans Help Center et File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!