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

2 views (last 30 days)
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.
.

Accepted Answer

Mathieu NOE
Mathieu NOE on 24 Oct 2022
hello again Augusto
try this code :
make sure you have downloaded the File Exchange submission first :
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

More Answers (1)

dpb
dpb on 24 Oct 2022
%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

Products


Release

R2021a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!