MATLAB: Import of different sized spreadsheets

spreadsheets different size cell2mat

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 -> 1×2 cell with 282×1 double 282×1 double
Angle_1_ -> 282×2 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

Best Answer

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