Merge multiple XLSX files with one header mismatch
4 次查看(过去 30 天)
显示 更早的评论
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 个评论
采纳的回答
Cris LaPierre
2021-10-27
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 个评论
Walter Roberson
2021-10-28
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.)
更多回答(1 个)
Walter Roberson
2021-10-28
编辑:Walter Roberson
2021-10-28
%% 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
...
另请参阅
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!