Data in multiple separate .xlsx sheets and move to only one .xlsx sheet
3 Ansichten (letzte 30 Tage)
Ältere Kommentare anzeigen
Augusto Gabriel da Costa Pereira
am 23 Okt. 2022
Kommentiert: Mathieu NOE
am 24 Okt. 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 Kommentare
Akzeptierte Antwort
Mathieu NOE
am 24 Okt. 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 Kommentare
Weitere Antworten (1)
dpb
am 24 Okt. 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 Kommentare
Siehe auch
Kategorien
Mehr zu Spreadsheets finden Sie in Help Center und File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!