Data in multiple separate .xlsx sheets and move to only one .xlsx sheet

I have these 31 files in .xlsx and I want to extract the data (information) from all these 31 files from columns A, B, C, D, E, F, G, H from row 4 to the last and put them all together in just a .xlsx worksheet.

It is worth mentioning that each line represents a day, so in this file that the data would all be together, I want each day to be below the other. For example. Column 1: year; Column 2: month;

Column 3: day;

Column 4: blank data;

Columns 5 to 8: Precipitation data.

I would be very grateful for the help.

.

Answer 1

[Mathieu NOE]

hello again Augusto

try this code :

make sure you have downloaded the File Exchange submission first :

Natural-Order Filename Sort - File Exchange - MATLAB Central (mathworks.com)

fileDir = pwd; % choose your working directory

S = dir(fullfile(fileDir,'*.xlsx')); % get list of data files in directory
S = natsortfiles(S); % sort folders / file names into natural order : https://fr.mathworks.com/matlabcentral/fileexchange/47434-natural-order-filename-sort

%% main loop
out = [];

for k = 1:numel(S)
    F = fullfile(S(k).folder,S(k).name)
    [data,TXT,RAW] = xlsread(F);
    [m,n] = size(data);
    out = [out;data(4:m,:)]; % vertical concatenation (lines 4 to end)
end

%% export
filename_export = 'out_file.xls';
ff = fullfile(fileDir,filename_export); 

writecell(TXT,ff);% export first 3 lines (header)
writematrix(out,ff,"Sheet",1,"Range",'A4'); % export data

[Augusto Gabriel da Costa Pereira]

Thanks for another help, Mathieu. You are amazing as always.

Best regards,

Augusto.

[Mathieu NOE]

My pleasure !

Answer 2

[dpb]

%fn=websave('cpc_global_precip_precip.2020.xlsx','https://www.mathworks.com/matlabcentral/answers/uploaded_files/1166433/cpc_global_precip_precip.2020.xlsx');
%d=dir(fn);

Above to run online to illustrate; use below for local...

d=dir(cpc_global_precip_precip.*.xlsx');
tP=[];
for i=1:numel(d)
  M=readmatrix(d(i).name,'numheaderlines',3);
  tP=[tP;array2timetable(M(:,5:end),'rowtimes',datetime(M(:,1:3)),'variablenames',cellstr("P"+[1:4]));];
end
head(tP)

       Time          P1        P2        P3        P4  
    ___________    ______    ______    ______    ______

    01-Jan-2020    6.2219    5.9471    7.9459    5.8571
    02-Jan-2020    51.572    33.056    24.695    17.925
    03-Jan-2020    9.2545    9.9083    9.4523    10.939
    04-Jan-2020    11.514    16.251    10.712    13.848
    05-Jan-2020    4.6511    6.5999    7.3446     6.434
    06-Jan-2020    27.791    22.095    12.013    9.1903
    07-Jan-2020    25.607    19.536    8.4016    7.8619
    08-Jan-2020    4.0166    4.5942    1.8593    2.4655