combine or merge multiple excel files into one file.

hi, i have 100 excel files and they have one of the three following types.

type1:

name    id     2010 2011 2012 2013 2014 2015 2016 2017 2018 2019
david     a
james    a
tome     a
alim       a

type2:

name    id     2010 2011 2012 2013 2014 2015 2016 2017 2018
david     a
james    a
tome     a
alim       a

type3:

name    id     2011 2012 2013 2014 2015 2016 2017 2018 2019
david     a
james    a
tome     a
alim       a

is there any way that i can combine all of the 100 files into one by using simple loop or any other feasible way?.

Thanks

Answer 1

[Walter Roberson]

name = cell(0,0); id = cell(0,0); 
x2010 = []; x2011 = []; x2012 = []; x2013 = []; x2014 = []; x2015 = []; x2016 = []; x2017 = []; x2018 = []; x2019 = [];
result = table(name, id, x2010, x2011, x2012, x2013, x2014, x2015, x2016, x2017, x2018, x2019);

dinfo = dir('*.xlsx');
nfiles = length(dinfo);
for K = 1 : nfiles
    thistable = readtable(result, 'readvariablenames', true);
    thisvars = thistable.Properties.VariableNames;
    if ~ismember( 'x2010', thisvars)
        thistable.x2010 = nan(height(thistable),1);
    end
    if ~ismember( 'x2019', thisvars)
        thistable.x2019 = nan(height(thistable,1));
    end
    result = [result; thistable];   %will automatically rearrange variables to match first table
end