MATLAB Answers

Appending tables from excel with different column order

2 views (last 30 days)
Wesso
Wesso on 11 Feb 2019
Commented: Wesso on 11 Feb 2019
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)

Answers (2)

KSSV
KSSV on 11 Feb 2019
  2 Comments

Sign in to comment.


Wesso
Wesso on 11 Feb 2019
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.
  1 Comment
Wesso
Wesso on 11 Feb 2019
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

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!

Translated by