Merge multiple XLSX files with one header mismatch
4 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
Nicolò Dall'Acqua
le 27 Oct 2021
Commenté : Walter Roberson
le 28 Oct 2021
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.
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/780918/image.png)
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!
0 commentaires
Réponse acceptée
Cris LaPierre
le 27 Oct 2021
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"]
4 commentaires
Walter Roberson
le 28 Oct 2021
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.)
Plus de réponses (1)
Walter Roberson
le 28 Oct 2021
Modifié(e) : Walter Roberson
le 28 Oct 2021
%% 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
...
Voir également
Catégories
En savoir plus sur Tables dans Help Center et File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!