Long loading times using xlsread

Hello, I am trying to extract measurements from an .xlsx sheet into a matrix. Because of the format I extract everything as raw. I get long loading times (~40 minutes +) by using following code.

Is there any way to decrease the time needed to read the excel-sheet or increase the reading speed?

Help would be highly appreciated.

Dennis

pkg load io
daten_matrix = xlsread('FLL_Messdaten_100Hz.xlsx');
[~, ~, raw_head] = xlsread('FLL_Messdaten_100Hz.xlsx', 'A1:ZZ1');
spaltennamen = raw_head(1,:);
gesuchte_namen = {
    "Loggerdata 100 Hz: Time",
    "Az_ax1_WH_Right",
    "Ay_ax1_WH_Right",
    "Ax_ax1_WH_Right",
    "An_ax1_WH_Right",
    "Az_ax1_WH_Left",
    "Ay_ax1_WH_Left",
    "Ax_ax1_WH_Left",
    "An_ax1_WH_Left",
    "Az_ax2_WH_Right",
    "Ay_ax2_WH_Right",
    "Ax_ax2_WH_Right",
    "An_ax2_WH_Right",
    "Az_ax2_WH_Left",
    "Ay_ax2_WH_Left",
    "Ax_ax2_WH_Left",
    "An_ax2_WH_Left"};
idx = zeros(1, length(gesuchte_namen));
for i = 1:length(gesuchte_namen)
    idx(i) = find(strcmp(spaltennamen, gesuchte_namen{i}));
end
daten = daten_matrix(:, idx)
zeit = daten(:,1);
figure;
plot(zeit, daten(:,17));
xlabel('Zeit [s]');
ylabel('An_{ax2, WH, Left}');
title('An_{ax2, WH, Left} über Zeit');
grid on;

[Cris LaPierre]

The first line is not valid MATLAB code. This appears to be Octave code, in which case, perhaps the Octave implementation is slow.

Out of curiousity, how does the code perform if you run it here (i.e. in MATLAB)? Use the paperclip icon to upload your spreadsheet, and then run your code by clicking the green play button.

[dpb]

As a second curiosity, check out how readcell compares...

[Stephen23]

+1 dpb. From the plotting and header handling, it looks like READTABLE might also work (and probably simplify the code).

Answer 1

[dpb]

daten_matrix = xlsread('FLL_Messdaten_100Hz.xlsx');
[~, ~, raw_head] = xlsread('FLL_Messdaten_100Hz.xlsx', 'A1:ZZ1');

column 'ZZ' --> 702 columns which is a humongous size. Simply reading the sheet and return only used data range will probably make order of magnitude difference.

You're reading the file twice't above, at least do it only once...

[daten_matrix, ~, raw_head] = xlsread('FLL_Messdaten_100Hz.xlsx');
raw_head=raw_head(1,:);     % cut down to first row only

Without the file to test for being well-formed we can't tell, but

[daten_matrix,raw_head] = xlsread('FLL_Messdaten_100Hz.xlsx');

with MATLAB syntax would be expected to give you the variable names directly; can't speak for Octave and this is not an Octave support group so is really an off-topic question other than to demonstrate the advantages of the real thing, perhaps.

If Octave isn't able to discern only the header row from the data as above, then at least limit the range to the size of the data you did retrieve...

daten_matrix = xlsread('FLL_Messdaten_100Hz.xlsx');
hdr_range=['A1:' xlsAddr(1,width(daten_matrix),0)];       % the actual range to match data
[~, ~, raw_head] = xlsread('FLL_Messdaten_100Hz.xlsx',hdr_range);

where xlsAddr is one of my own Excel utility functions built to ease manipulations of spreadsheets with MATLAB. All the above is valid MATLAB syntax; no promises it will be compatible with Octave. NOTA BENE: I don't recall with xlsread but the new(ish) readcell and friends do not like Excel absolute addresses, hence the flag variable.

function rnge=xlsAddr(row,col,absflag)
% Build Excel cell address from row, column
%
% ADDR=XLSADDR(ROW,COL) will return an absolute Excel cell address 
% formed from the input ROW,COL values.  Either input may be
% string or numeric and will be converted to canonical form
%
% ADDR=XLSADDR(ROW,COL,FLAG) returns a relative Excel cell address 
% FLAG is one or two-element logical for BOTH or ROW,COL individually

  if nargin==2, flag=true(1,2); end
  if nargin==3, flag=absflag; end
  if isscalar(flag), flag(2)=flag(1); end
  assert(numel(flag)==2,'Absolute address flag must be no more than two elements.')
  if isnumeric(col)
    if ~isscalar(col),  error('Input Column Not Scalar'), end
    rnge=strcat('$',num2letter(col));
  else
    col=char(col);
    rnge=['$' col];
  end
  if ~flag(1), rnge=strrep(rnge,'$','');end
  if isnumeric(row)
    if ~isscalar(row),  error('Input Row Not Scalar'), end
    row=num2str(row,'$%d');
  else
    assert(all(ismember(row,'0':'9')), 'Invalid Excel Address: Row not numeric')
    row=['$' row];
  end
  if ~flag(2), row=strrep(row,'$','');end
  rnge=[rnge row];
end

But, as @Stephen23 noted, since it's splitting by column, if it is supported then

tDeata=readtable('FLL_Messdaten_100Hz.xlsx');

will make things much simpler.