Merge multiple XLSX files with one header mismatch

Hello everyone, I'm stuck with this problem at the moment, and I would be really grateful if you could help me.

I'm reading a considerable number of XLSX files, with multiple sheets per file, everyone with the same layout: 16 columns and n entries. All these files are stored inside a table named fileID.

What I did with the vertcat function worked fine because all the files inside my directory had the same headers. The problem is that now I need to add other files with identical headers as the previous ones, except for one that varies slightly (EPS Density vs EPP Density, in column #6). Since there is this mismatch on one column header of these new files, the vertcat approach is useless at the moment.

Therefore, what I wanted to achieve was:

• Specify a common "Density" column without considering the EPS and EPP columns of the various files, so replace column #6 in the image attached and merge everything.

or

• Having two different columns close by, adding a new EPP Density column between columns #6 and #7.

close all
clear all %#ok<CLALL>
clc
%warning('off')

%% IMPORT
D = '/Users/nda/Documents/Helmet/Cube/Data/C2/Impacts';
J = fullfile(D,'*.xlsx');
S = dir(J);

% READ ALL XLSX IN DIRECTORY, READ ALL SHEETS PER FILE
% ADD EXTRA COLUMN AND STORE FILENAMES IN IT
for j = 1:numel(S)
    F = fullfile(D,S(j).name);
    [~,sheet_name]=xlsfinfo(F);
    opts = detectImportOptions(F,'NumHeaderLines',0,'ImportErrorRule','omitrow');
    for k=1:length(sheet_name)
      fileID{j,k} = readtable(F,opts,'Sheet',k);
      fileID{j,k}{:,end+1} = string(S(j).name);     
    end
end

fileID = fileID(~cellfun('isempty',fileID));
fileID = vertcat(fileID{:});

%% ELABORATE
...

Thanks for any help you gurus can provide!

Answer 1

[Cris LaPierre]

Consider manually naming your variables. Then, as long as you have the same number of columns in each sheet, you are able to import without worrying about the names used in the file.

...
opts = detectImportOptions(F,'NumHeaderLines',0,'ImportErrorRule','omitrow');
opts.VariableNames = ["Name1","Name2"]

[Nicolò Dall'Acqua]

Thanks you Cris!

Unfortunately now when I'm running the script now I get this error:

Cannot concatenate the table variable 'density' because it is a cell in one table and a non-cell in another.

That's because the EPS values are numbers (i.e. 98) and the EPP values are numbers+letter (i.e. 11P).

How can I fix this?

[Walter Roberson]

At the time you read in the data, replace the density column with string() of the density column.

This means that all numeric values will get converted to string, losing their numeric identities; it also means that the cell array of character vector for EPP Density will become a string array instead.

[Nicolò Dall'Acqua]

Understood, thank you! Updated code:

close all
clear all %#ok<CLALL>
clc
%warning('off')

%% IMPORT
D = '/Users/nda/Documents/Helmet/Cube/Data/C2/Impacts';
J = fullfile(D,'*.xlsx');
S = dir(J);

% READ ALL XLSX IN DIRECTORY, READ ALL SHEETS PER FILE
% ADD EXTRA COLUMN AND STORE FILENAMES IN IT
for j = 1:numel(S)
    F = fullfile(D,S(j).name);
    [~,sheet_name]=xlsfinfo(F);
    opts = detectImportOptions(F,'NumHeaderLines',0,'ImportErrorRule','omitrow');
    opts.VariableNames = ["date","codeID","model","size","headform","density","certification",...
    "condition","type_test","type_anvil","location","peakG","CSF","SF","result","notes","path"];
    opts.VariableTypes = ["string","string","string","string","string","string","string",...
    "string","string","string","string","double","double","double","string","string","string"];
    for k=1:length(sheet_name)
      fileID{j,k} = readtable(F,opts,'Sheet',k);
      fileID{j,k}{:,end} = {S(j).name};     
    end
end

fileID = fileID(~cellfun('isempty',fileID));
fileID = vertcat(fileID{:});

%% ELABORATE
...

[Walter Roberson]

Ah, changing the variable type to always be string is an interesting approach that should work too.

Side note: you can construct those two string() vectors outside the loop, since they are not changing inside the loop.

I think you might even be able to construct the entire opts outside the loop (construct it on the first file.)

Answer 2

[Walter Roberson]

https://www.mathworks.com/help/matlab/ref/table.movevars.html

%% IMPORT
D = '/Users/nda/Documents/Helmet/Cube/Data/C2/Impacts';
J = fullfile(D,'*.xlsx');
S = dir(J);

% READ ALL XLSX IN DIRECTORY, READ ALL SHEETS PER FILE
% ADD EXTRA COLUMN AND STORE FILENAMES IN IT
for j = 1:numel(S)
    F = fullfile(D,S(j).name);
    [~,sheet_name]=xlsfinfo(F);
    opts = detectImportOptions(F, 'VariableNamingRule', 'preserve', ImportErrorRule','omitrow');
    for k=1:length(sheet_name)
      T = readtable(F,opts,'Sheet',k);
      T.Filename = repmat(string(S(j).name), height(T), 1);
      if ismember('EPS Density', T.Properties.VariableNames)
         T.('EPP Density') = repmat("N/A", height(T), 1);
      else
         T.('EPP Density') = string(T.('EPP Density'));
         T.('EPS Density') = nan(height(T),1);
      end
      T = movevars(T, {'EPS Density', 'EPP Density'}, 'After', 'HeadFormSize');
      fileID{j,k} = T;
    end
end

fileID = fileID(~cellfun('isempty',fileID));
fileID = vertcat(fileID{:});

%% ELABORATE
...

[Nicolò Dall'Acqua]

I realized it will be more easy for me using Cris's approach, but thanks a lot Walter for your reply.