Multiple excel files import to single file and read

33 ビュー (過去 30 日間)
Chandra Sekhar Kommineni
Chandra Sekhar Kommineni 2022 年 5 月 19 日
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

採用された回答

Seth Furman
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))
data1 = 4×4 table
Var1 Var2 Var3 Var4 ____ ____ ____ ____ 16 2 3 13 5 11 10 8 9 7 6 12 4 14 15 1
data2 = array2table(magic(5))
data2 = 5×5 table
Var1 Var2 Var3 Var4 Var5 ____ ____ ____ ____ ____ 17 24 1 8 15 23 5 7 14 16 4 6 13 20 22 10 12 19 21 3 11 18 25 2 9
fname = "MyData.xlsx";
writetable(data1, fname, Sheet="Sheet1");
writetable(data2, fname, Sheet="Sheet2");
readtable(fname, Sheet="Sheet1")
ans = 4×4 table
Var1 Var2 Var3 Var4 ____ ____ ____ ____ 16 2 3 13 5 11 10 8 9 7 6 12 4 14 15 1
readtable(fname, Sheet="Sheet2")
ans = 5×5 table
Var1 Var2 Var3 Var4 Var5 ____ ____ ____ ____ ____ 17 24 1 8 15 23 5 7 14 16 4 6 13 20 22 10 12 19 21 3 11 18 25 2 9
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)
data = 9×1 table
Var1 ____ 16 5 9 4 17 23 4 10 11
writetable(data, "MyDataColumn1.xlsx", Sheet="Sheet1")
readtable("MyDataColumn1.xlsx", Sheet="Sheet1")
ans = 9×1 table
Var1 ____ 16 5 9 4 17 23 4 10 11
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")
ans = 9×1 table
Var1 ____ 16 5 9 4 17 23 4 10 11
  1 件のコメント
Chandra Sekhar Kommineni
Chandra Sekhar Kommineni 2022 年 5 月 31 日
Hello Seth Furman,
Thank you for your detailed message, I would really appreciate. This is how I did, but this is very close to your solution. Problem here is I can't give names for every sheet, I have 20+ files in my directory. Do you know How to define sheet names as per the file name or something I can generate automatically in loop.
and
When I'm writing table, I'm loosing my header file, so, I'm not able to retrive columns based on variable names.
Can you please help me with that?
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);
end
%%
sheets = sheetnames("MASTER.xlsx");
for k=1:numel(sheets)
data{k}=xlsread('MASTER.xlsx',sheets{k});
end

サインインしてコメントする。

その他の回答 (0 件)

カテゴリ

Help Center および File ExchangeSpreadsheets についてさらに検索

製品


リリース

R2021b

Community Treasure Hunt

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

Start Hunting!

Translated by