Import of different sized spreadsheets

Hello everybody,

I have a problem concerning the import of spreadsheets.

My goal is, to read/load multiple xlsx Files at once. Those files do all look the same, except for the length of the vectors. Some files are four or five data points longer some are shorter. I use this code:

[Filename, Path] = uigetfile({'*.xlsx','DEWE Files(*.xlsx)'}, 'MultiSelect', 'on','C:\.....');
  for k = 1:numel(Filename)
      File = fullfile(Path, Filename{k});
      [~, ~, raw] = xlsread(File);
      raw(cellfun(@(x) ~isempty(x) && isnumeric(x) && isnan(x),raw)) = {''};

        %%Replace non-numeric cells with NaN
        R = cellfun(@(x) ~isnumeric(x) && ~islogical(x),raw); % Find non-numeric cells
        raw(R) = {NaN}; % Replace non-numeric cells

        %%Create output variable
        data = reshape([raw{:}],size(raw));

        %%Allocate imported array to column variable names
        Angle_1{k} = data(:,1);
        Distance_1{k} = data(:,2);
        Angle_2{k} = data(:,3);
        Distance_2{k} = data(:,4);
      
      
      
      Angle_1_=cell2mat(Angle_1);                 %cell->double            
        Distance_1_=cell2mat(Distance_1);           %cell->double 
        Angle_2_=cell2mat(Angle_2);                 %cell->double         
        Distance_2_=cell2mat(Distance_2);           %cell->double
      
      
    ...
    end

Sometimes I read/load files which are accidentally the same size/length. For example:

I load two files with the same length:

Angle_1 -> 1x2 cell with 282x1 double 282x1 double

Angle_1_ -> 282x2 double

For those cases I do not face any issues. But when there are some files which differ in size cell2mat does not work. I think I have to fill up all the shorter files with NaN. But for now, I don’t know how to do this.

I would really appreciate any help!

Cheers

Christian

[Robert U]

Dear Christian:

Did you think about clearing all size-dependent variables before restarting the read-out of the spreadsheet. Another way to obtain the same effect is to pre-define the size of the size-dependent variables by zeros(Max_N).

Kind regards,

Robert

[Christian]

Hello Robert,

before the "end" I use this code:

%%Clear temporary variables
    clearvars data raw R;

is that what you mean?

[Robert U]

Dear Christian: I think the main problem is that the size of variables as Angle_1 differ for each k. In case that size of Angle_1 grows greater for k+1 that is no problem. But in case that Angle_1 would have less elements you are not deleting the content of the previous k.

To solve that you would have to reinitialize the variables.

Kind regards,

Robert

[Christian]

Robert, thank you! But I'm pretty new to Matlab so I understand things better, if I see actual code. Maybe you can show me how you would reinitialize variables?

Thank you

Christian

[Robert U]

Before closing your for-loop

   clear raw data Angle_1 Distance_1 Angle_2 Distance_2

Make sure you save the data you need for later calculations to other variables.

[Christian]

Hell yeah, that seems to work!

Thank you Robert!

[Guillaume]

There's absolutely no point clearing raw. A new matrix is created at each step of the loop by

[~, ~, raw] = xlsread(File);

Same for data. A new matrix is created by:

data = reshape([raw{:}],size(raw));

And you certainly don't want to clear the other variables, since they're the ones that the loop fills.

In general, there is never any need for clear in functions (and even in scripts it's usually not needed despite people liking to start their scripts with clear all.

Answer 1

[Guillaume]

Note, these lines:

        Angle_1_=cell2mat(Angle_1);                 %cell->double            
        Distance_1_=cell2mat(Distance_1);           %cell->double 
        Angle_2_=cell2mat(Angle_2);                 %cell->double         
        Distance_2_=cell2mat(Distance_2);           %cell->double

should be outside the loop, after the end. There's no point recalculating them at each step of the loop.

The problem is that indeed all the matrices in the cell array need to be the same size. I would add a function:

function c = equaliserows(c)
   %c: a row vector cell array of 2D matrices. Shorter matrix get rows of nan so that they all have the same height
   validateattributes(c, {'cell'}, {'row'})
   maxsize = max(cellfun(@(m) size(m, 1), c));
   c = cellfun(@(m) [m; nan(maxsize - size(m, 1), size(m, 2))], c, 'UniformOutput', false);
end

You can then easily convert all your cell arrays to matrices:

Angle_1_ = cell2mat(equaliserows(Angle_1));
Distance_1_ = cell2mat(equaliserows(Distance_1));
Angle_2_ = cell2matequaliserows((Angle_2));
Distance_2_ = cell2mat(equaliserows(Distance_2));

[Christian]

Thank you!!! That works perfect!