Clear Filters
Clear Filters

Multiple excel files import to single file and read

4 views (last 30 days)
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

Accepted Answer

Seth Furman
Seth Furman on 30 May 2022
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 Comment
Chandra Sekhar Kommineni
Chandra Sekhar Kommineni on 31 May 2022
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

Sign in to comment.

More Answers (0)

Products


Release

R2021b

Community Treasure Hunt

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

Start Hunting!