Appending tables from excel with different column order

Hi,

I am using teh following codes to append a large number of excel data

List = dir(fullfile(Base,'*.xls'));
Result = cell(1,numel(List));
for k = 1:numel(List)
File = fullfile(Base,List(k).name);
if k==1
opts = detectImportOptions(File);    
A = readtable(File,opts); %     
t20142018=A;
else
A = readtable(File,opts);
t20142018=[t20142018;A];  
end
end

However, I discovered that some files don't have the same order which resulted data of different columns to be merged in a single column . So my question is ow to make sure that columns with the same names from different excel files are appended together with the same format. In other words, if an excel file has the following header:

Rank Date Name Hostile

and another excel file has the following order

Rank Name Date Hostile

How can I append these files correctly in an aggregate table (t2014t2018 in my codes)

Answer 1

[KSSV]

Read about join

https://in.mathworks.com/help/matlab/ref/table.join.html

[Wesso]

I am receiving an error" The key variables for A and B cannot contain any missing values". Note that I have NaNs in these columns.

so my code is now:

List = dir(fullfile(Base,'*.xls'));
Result = cell(1,numel(List));
for k = 1:numel(List)
File = fullfile(Base,List(k).name);
if k==1
opts = detectImportOptions(File);    
A = readtable(File,opts); %     
t20142018=A;
else
A = readtable(File,opts);
t20142018=join(t20142018,A);  
end
end

[KSSV]

Fill the missing values suing: fillmissing

https://in.mathworks.com/help/matlab/ref/fillmissing.html

Answer 2

[Wesso]

the issue is that I have a large number of columns that can have different column formats :cell,double,datetime etc... . Is there a way to replace all NaN values using fillmissing by the default missing value that corresponds to the format of the column? otherwise it will be a mess to fill them using loops.

[Wesso]

I tried to use KSSV comments by incorporating join and fillmissing. I ended up with the following code:

List = dir(fullfile(Base,'*.xls'));
Result = cell(1,numel(List));
for k = 1:numel(List)
File = fullfile(Base,List(k).name);
if k==1
opts = detectImportOptions(File);    
A = readtable(File,opts); %     
A= fillmissing(A,'constant',-99999,'DataVariables',@isnumeric);
A=fillmissing(A,'constant',datetime('now'), 'DataVariables', @isdatetime);
A=fillmissing(A,'constant','Missing', 'DataVariables', @iscellstr);
t20142018=A;
else
A = readtable(File,opts);
A= fillmissing(A,'constant',-99999,'DataVariables',@isnumeric);
A=fillmissing(A,'constant',datetime('now'), 'DataVariables', @isdatetime);
A=fillmissing(A,'constant','Missing', 'DataVariables', @iscellstr);
t20142018=join(t20142018,A);  
end
end

The problem is that I am still receiving errors:

The key variables for B must contain all unique combinations of values in the key variables for A.

I am wondering why it is that complex to append tables if there are NaNs