How to read gaps from an Excel in MATLAB

I am developing the code below in order to read two columns from several Excel. With "active_energy" I don't have any problem because there any gap in the excel but with "reactive_energy" I am not able to read the data because there are empty cells in the Excel. How can I solve it? After reading the data, I need to sum all of them.

If any of you has a better idea, please tell me and I will be very grateful

% parameters
range1 = 'C2:C3000'; %  range  to read active energy
range2 = 'D2:D3000'; % range to read reactive energy
numRows = 2999; % number of rows to save
% get list of .xlsx files
list = dir('2019_*.xlsx') % use wild card to get matching file names, e.g myfile01, myfile02 etc
% determine how many files there are
numFiles = length(list);
% prellocate an array to hold the results
results1 = zeros(numRows,numFiles);
results2 = zeros(numRows,numFiles);
% loop through .xlsx files reading data from desired column specified by range
for k = 1:numFiles
    filename = list(k).name;
    % read data and store  as column in result array
    results1(:,k) = readmatrix(filename,'Range',range1);
    results2(:,k) = readmatrix(filename,'Range',range2);    
end

for k = 1:numFiles
    active_energy (:,k) = sum(results1(:,k),'omitnan')
    reactive_energy (:,k) = sum(results2(:,k),'omitnan')
end

Answer 1

[dpb]

It's readmatrix probably that's the problem as it is designed for matrix input and you have what looks like two (or several) matrices for the one column instead of just one. You can try subterfuge by specifying the range as the two columns something like:

% parameters
range='C:D';
% get list of .xlsx files
d=dir('2019_*.xlsx');       % 'list' is built-in MATLAB function -- don't alias it...
numFiles = length(d);
energy=zeros(numFiles,2);   % allocate for the energy sums
for k = 1:numFiles
  energy(k,:)=sum(readmatrix(d(k).name,'Range',range,'NumHeaderLines',1),'omitnan');
end

and I'm guessing it may work altho didn't try it.

If it still aborts on the missing data in column D, switch to readtable or use the detectImportOptions function first to build a SpreadsheetImportOptions object that can pass in which you can define how to treat missing data and the ranges that will override the internal logic that tries to impute what the data format of the input file is. But, I believe just using the two columns together where the one is complete will work (although it undoubtedly would break again if that column ever were to also have any missing value(s). The more robust coding solution would be to use the import options object.

NB: You don't need this redone every time, build one for the file structure and use it for each inside the loop.

[José Javier Rubio Rubio]

Hi dbp,

Thank you very much for your answer. I will try it and I hope it works.