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

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);

Answer 1

[CAM]

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);

[Valerio Gianforte]

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);