Combine multiple tables into one table and export as an excel file

98 views (last 30 days)
I need to generate an excel file from multiple tables in my workspace, catch is all the tables have different number of rows, so I want to align them as best as possible using the first column of each table and push them out to an excel file.
I also need to put out a separate excel file for each table, is there a way to loop through the tables and generate an excel file for each of them…. So basically, how can I change the file name (meaning the excel file that i want to generate) every iteration to the appropriate table name which is at very last part of the directory/path
If it’s any help, I’ve attached a few tables and a piece of my code for a reference.

Accepted Answer

KSSV
KSSV on 3 Nov 2021
files = dir('*.mat') ; % you are in the folder where mat files are present
N = length(files) ;
T = cell(N,1) ;
for i = 1:N
thisfile = files(i).name ;
temp = struct2cell(load(thisfile));
T{i} = temp{1} ;
% Write each tble to excel file
[thepath,name,extn] = fileparts(thisfile) ;
outfile = [files(i).folder,filesep,name,'.xlsx'] ;
writetable(T{i},outfile);
end
  2 Comments
HabenG
HabenG on 3 Nov 2021
Edited: HabenG on 3 Nov 2021
Thanks Fellas. I used outjoin with merge to combine the tables and i've also decided to push all the table into a single excel file in different sheets but it seems like i can only output an excel file with only one sheet. When i try to add another table into a different sheet it wipes out data i had in another sheet.

Sign in to comment.

More Answers (1)

HabenG
HabenG on 3 Nov 2021
Edited: HabenG on 4 Nov 2021
Figured it out. I combined all the table and iterated through each column and made a separate sheet for each column....hopefully this will help someone
Directory = ('C:\Users\hgheb\OneDrive\Desktop\TestForlder\Logistic.xlsx'); % Change to your directory
k = numel('your table name here'.Properties.VariableNames);
for i = 1: width(k)
writetable('your table name here'(:,[1, i]),Directory,"Sheet",i,"WriteMode","inplace"); % Here i'm keeping column 1 for all sheets while iterating through all other columns.
end

Community Treasure Hunt

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

Start Hunting!

Translated by