Data in multiple separate .xlsx sheets and move to only one .xlsx sheet
3 views (last 30 days)
Show older comments
Augusto Gabriel da Costa Pereira
on 23 Oct 2022
Commented: Mathieu NOE
on 24 Oct 2022
- cpc_global_precip_precip.2020.xlsx
- cpc_global_precip_precip.2019.xlsx
- cpc_global_precip_precip.2018.xlsx
- cpc_global_precip_precip.2017.xlsx
- cpc_global_precip_precip.2016.xlsx
- cpc_global_precip_precip.2015.xlsx
- cpc_global_precip_precip.2014.xlsx
- cpc_global_precip_precip.2013.xlsx
- cpc_global_precip_precip.2012.xlsx
- cpc_global_precip_precip.2011.xlsx
- cpc_global_precip_precip.2010.xlsx
- cpc_global_precip_precip.2009.xlsx
- cpc_global_precip_precip.2008.xlsx
- cpc_global_precip_precip.2007.xlsx
- cpc_global_precip_precip.2006.xlsx
- cpc_global_precip_precip.2005.xlsx
- cpc_global_precip_precip.2004.xlsx
- cpc_global_precip_precip.2003.xlsx
- cpc_global_precip_precip.2002.xlsx
- cpc_global_precip_precip.2001.xlsx
- cpc_global_precip_precip.2000.xlsx
- cpc_global_precip_precip.1999.xlsx
- cpc_global_precip_precip.1998.xlsx
- cpc_global_precip_precip.1997.xlsx
- cpc_global_precip_precip.1996.xlsx
- cpc_global_precip_precip.1995.xlsx
- cpc_global_precip_precip.1994.xlsx
- cpc_global_precip_precip.1993.xlsx
- cpc_global_precip_precip.1990.xlsx
- cpc_global_precip_precip.1992.xlsx
- cpc_global_precip_precip.1991.xlsx
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.
.
0 Comments
Accepted Answer
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
2 Comments
More Answers (1)
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)
0 Comments
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!