Multiple excel files import to single file and read

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

Answer 1

[Seth Furman]

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.

https://www.mathworks.com/help/matlab/ref/writetable.html#d123e1627732

https://www.mathworks.com/help/matlab/ref/readtable.html#d123e1238578

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.

https://www.mathworks.com/help/matlab/import_export/read-spreadsheet-data-into-table.html#ReadSpreadsheetDataIntoTableEx59168851Example-4

https://www.mathworks.com/help/matlab/ref/matlab.io.datastore.spreadsheetdatastore.html

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 

[Chandra Sekhar Kommineni]

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