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

3 次查看（过去 30 天）
CMatlabWold
CMatlabWold 2022-2-28
评论： Mathieu NOE 2022-3-3
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')

请先登录，再回答此问题。

采纳的回答

Mathieu NOE
Mathieu NOE 2022-3-1
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 个评论
显示 6更早的评论

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

产品


版本

R2021a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by