How to join multiple Excel files of varying lengths
2 visualizzazioni (ultimi 30 giorni)
Mostra commenti meno recenti
Murtaza Mohammadi
il 5 Ott 2022
Commentato: Mathieu NOE
il 17 Ott 2022
Hello
So I have a folder containing a large number of Excel files. Each file has one sheet only, and the data is located in the 1st column. However the number of rows of data varies between the files. I would like to join all of these files into a single variable in Matlab, such that the number of columns is equal to the number of files, and the number of rows in each column corresponds to the data in the file. Empty cells can be filled with NaN.
The objective is that I have a number of such folders, each represents certain physical measurement. I would then like to conduct mathematical operations between the variables once I get all files/folders on to Matlab.
Thank you
5 Commenti
Risposta accettata
Cris LaPierre
il 6 Ott 2022
Here's something based on using fileDataStore. This assumes your data is numeric, as it uses readmatrix to load the data from your spreadsheets.
dsFiles = fileDatastore("file*.xlsx","ReadFcn",@readmatrix);
data = readall(dsFiles);
D = cellfun(@length,data)
L = max(D)
for a = 1:length(D)
C = nan(L-D(a),1);
out(:,a) = [data{a};C];
end
Più risposte (1)
Mathieu NOE
il 6 Ott 2022
Modificato: Mathieu NOE
il 6 Ott 2022
hello
try this :
fileDir = pwd; % current directory (or specify which one is the working directory)
outfile = 'OUT.xlsx'; % output file name
S = dir(fullfile(fileDir,'*.xlsx')); % get list of data files in directory
S = natsortfiles(S); % sort file names into natural order , see :
%(https://fr.mathworks.com/matlabcentral/fileexchange/47434-natural-order-filename-sort)
out_data = [];
max_nn = 0;
for k = 1:length(S)
filename = S(k).name % to actually show filenames are sorted (see command window)
out = readmatrix( fullfile(fileDir, filename),"Range",'A:A'); % extract column 1 (A) of each xlsx files
nn = numel(out);
oo = size(out_data,1);
% case 1 : out_data longer (strictly) than new data (out)
if oo<nn % need to padd some NaN's to out_data before doing the final horizontal concatenation
out_data = [out_data; NaN(nn-oo,size(out_data,2))]; % vertical concatenation
end
% case 2 : out_data shorter (strictly) than new data (out)
if oo>nn % need to padd some NaN's to out before doing the final horizontal concatenation
out = [out; NaN(oo-nn,1)]; % vertical concatenation
end
% case 3 : out_data same length as new data (out)
% nothing to do !!
out_data = [out_data out]; % final horizontal concatenation
end
% store out_data in excel file
writematrix(out_data,fullfile(fileDir,outfile));
2 Commenti
Mathieu NOE
il 17 Ott 2022
hello Murtaza
as I wrote in my comments , this is a function usefull to make sure the files are sorted out correctly (what matlab does not by default)
so look at the File Exchange (FEX in short) and get this excellent submission :
all the best
Vedere anche
Categorie
Scopri di più su Spreadsheets in Help Center e File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!