How to read gaps from an Excel in MATLAB
2 views (last 30 days)
Show older comments
José Javier Rubio Rubio
on 18 Nov 2020
Commented: José Javier Rubio Rubio
on 18 Nov 2020
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
0 Comments
Accepted Answer
dpb
on 18 Nov 2020
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.
More Answers (0)
See Also
Categories
Find more on Data Import from MATLAB in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!