Multiple excel files import to single file and read
9 次查看(过去 30 天)
显示 更早的评论
Hello Sir/Madame
!) I'm trying to merge multiple excel files to single file,
2) I want names of each sheet should be same as above different files(I couldn't solve this)
2) the single excel file that I got from above process, after I want to read data from several sheets. I want to extract a column from each sheet to separate single file.(I'm not sure how to read data from several sheets in single excel files)
Thank you for you answer in advance
clear all; clc;
fileDir = 'C:\2022_05_19_calibration';
outfile = 'C:\\MASTER.xlsx';
addpath(fileDir);
fileNames = dir(fileDir);
fileNames = {fileNames.name};
fileNames = fileNames(cellfun(@(f)~isempty(strfind(f,'.xlsx')),fileNames));
for f = 1:numel(fileNames)
fTable = readtable(fileNames{f},'VariableNamingRule','preserve');
writetable(fTable,outfile,'Sheet',f); %how to change the name of each sheet?
end
%%
calibration = readtable('C:\2022_05_19_calibration\MASTER.xlsx','VariableNamingRule','preserve');
sheets = sheetnames(filename)
for k=1:numel(sheets)
data{k}=xlsread('filename.xlsx',sheets{k}) % extracting column from each sheet and write to another excel file?
end
0 个评论
采纳的回答
Seth Furman
2022-5-30
I want the name of each sheet to be the same as each different file
We can pass a sheet name to writetable with the Sheet name-value pair.
data1 = array2table(magic(4))
data2 = array2table(magic(5))
fname = "MyData.xlsx";
writetable(data1, fname, Sheet="Sheet1");
writetable(data2, fname, Sheet="Sheet2");
readtable(fname, Sheet="Sheet1")
readtable(fname, Sheet="Sheet2")
How to read data from several sheets in a single excel file?
We can use a spreadsheetDatastore to read from multiple sheets at once.
ds = spreadsheetDatastore(fname);
ds.Sheets = ["Sheet1","Sheet2"];
ds.Range = "A:A";
data = readall(ds)
writetable(data, "MyDataColumn1.xlsx", Sheet="Sheet1")
readtable("MyDataColumn1.xlsx", Sheet="Sheet1")
Otherwise, we can just use readtable in a loop.
data = {};
sheetNames = ["Sheet1","Sheet2"];
for i = 1:numel(sheetNames)
data{i} = readtable(fname, Sheet=sheetNames(i), Range="A:A");
end
writetable(vertcat(data{:}), "MyDataColumn1.xlsx", Sheet="Sheet1");
readtable("MyDataColumn1.xlsx", Sheet="Sheet1")
更多回答(0 个)
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!