read and write xlsx files

Hi All,

I have a folder with 20 xlsx files. i want matlab to read all the xlsx files and take column 3 from each file and write in a new table. (please note i want column 3 of each xlsx files to be in a sepeate column in the new table so i can plot them against each other.

Thanks,

Answer 1

[Mathieu NOE]

hello see example code below

you can either write the result as a simple numeric array (as here) with writematrix, but if you really need the result as a table, simply use writetable instead.

I assuled here all input data files have same amount of data (number of rows) so it was pretty easy to do thevertical concatenation. Otherwise we might have to do some padding

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 = [];

for k = 1:length(S)
    filename = S(k).name % to actually show filenames are sorted (see command window)
    tmp = readmatrix( fullfile(fileDir, filename)); %
    out_data = [out_data tmp(:,3)]; % store column 3 of each xlsx files and do vertical concatenation
end

% store out_data in excel file 
writematrix(out_data,fullfile(fileDir,outfile)); 

[Matin jaberi]

thanks,

the data come in different number of rows. I guess that is why it only gives me the output for the very first data file.

[Mathieu NOE]

hello again

improved code below , tested with variable length files

I was lazzy this morning so I figure out the simplest code would only require from you an estimate by excess of how many rows must be retrieved.

Of course we can make it smarter and adapt at each iteration

all the best

fileDir = pwd; % current directory (or specify which one is the working directory)
outfile = 'OUT.xlsx'; % output file name 

S = dir(fullfile(fileDir,'data00*.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)

max_rows = 1e4; % rough estimate (by excess) of max number of rows           
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",'C:C'); % extract column 3 (C) of each xlsx files
    nn = numel(out);
    tmp = NaN(max_rows,1); % initiate tmp vector with NaN's
    tmp(1:nn) = out;  % fill tmp vector with data (from the top)
    out_data = [out_data tmp]; % horizontal concatenation
    max_nn = max(max_nn,nn); % store longest file rows qty (see below : retrieve trailing nan's)
end

% retrieve trailing nan's 
out_data = out_data(1:max_nn,:);

% store out_data in excel file 
writematrix(out_data,fullfile(fileDir,outfile)); 

[Mathieu NOE]

Finally , came up with yet improved code where no need anymore to give estimated max row qty

enjoy it !

clc
clearvars

fileDir = pwd; % current directory (or specify which one is the working directory)
outfile = 'OUT.xlsx'; % output file name 

S = dir(fullfile(fileDir,'data00*.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",'C:C'); % extract column 3 (C) 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)); 

[Mathieu NOE]

hello

if my answer has helped you, do you mind accepting it ?

thanks