I want to merge more xlsx files in one xlsx file.

1 view (last 30 days)
Hi Everyone,
I want to merge the data contents of 6 xlsx files in one xlsx file. Can someone helps me? Thanks. I'm attacching the code below.
format long g;
folderData = 'D:\Valerio\data\ERA_5';
filePattern = fullfile(folderData, '*.xlsx');
xlsFiles = dir(filePattern);
nFiles = length(xlsFiles);
for i = 1:nFiles
filename = fullfile(xlsFiles(i).folder, xlsFiles(i).name);
files{i} = xlsread(filename);
DIR = files(1);
Hs = files(2);
time = files(3);
Tp = files(4);
U_wind = files(5);
V_wind = files(6);
end
YYMMDD = cell2mat(time);
Years = YYMMDD(:,1);
Month = YYMMDD(:,2);
Days = YYMMDD(:,3);
HH = YYMMDD(:,4);
H_s = cell2mat(Hs);
T_p = cell2mat(Tp);
uwind = cell2mat(U_wind);
vwind = cell2mat(V_wind);
M = [Years Month Days HH H_s T_p uwind vwind];
nameFile = 'D:\Valerio\data\ERA5.xls';
xlswrite(nameFile,M);
I obtain this error:
C_extract_ERA5
Index exceeds the number of array elements (1).
Error in C_extract_ERA5 (line 11)
Hs = files(2);

Answers (1)

CAM
CAM on 6 Mar 2020
First thing: I personally would avoid using "i" as a counter, since it could also signify the imaginary number. Use "ii" instead to avoid confusion.
Secondly, you are assigning the variables (DIR, hs, time, ...) in every loop. The first time through the loop (ii=1), only files(1) is defined. Because files(2) has not been created yet (ii has not reached 2), the code is throwing an error at hs=files(2).
Move the assignments of DIR, hs, etc to outside the loop.
Also, it appears you have mixed data types (strings and numbers). I would suggest that you use the "raw" data from Excel (both numbers and strings in a cell array), so use the 3rd output.
[~, ~, files{ii}] = xlsread(filename);
  1 Comment
Valerio Gianforte
Valerio Gianforte on 6 Mar 2020
Now I have this error:
C_extract_ERA5
Error using xlswrite (line 224)
The specified data range is invalid or too large to write to the specified file format. Try writing to an XLSX file and use Excel A1
notation for the range argument, for example, A1:D4.
Error in C_extract_ERA5 (line 31)
xlswrite(nameFile,M);

Sign in to comment.

Products


Release

R2018b

Community Treasure Hunt

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

Start Hunting!