Data in multiple separate .xlsx sheets and move to only one .xlsx sheet
1 次查看(过去 30 天)
显示 更早的评论
- 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 个评论
采纳的回答
Mathieu NOE
2022-10-24
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
更多回答(1 个)
dpb
2022-10-24
%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 个评论
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Data Import from MATLAB 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!