Data in multiple separate .xlsx sheets and move to only one .xlsx sheet
3 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
Augusto Gabriel da Costa Pereira
le 23 Oct 2022
Commenté : Mathieu NOE
le 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 commentaires
Réponse acceptée
Mathieu NOE
le 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 commentaires
Plus de réponses (1)
dpb
le 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 commentaires
Voir également
Catégories
En savoir plus sur Spreadsheets dans Help Center et File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!