Read spreadsheet with 2 values per cell as (n, j, 2) matrix

Question

I have used xlsread in order to read a spreadsheet as a two dimensional matrix but I need to read a spreadsheet in the form of

2,2    2,0
0,2    1,1

As a 2,2,2 matrix

Is there a way to use Matlab to separate the values of the cells or do I have to use two separate matrices in the spreadsheet

Answer 1

If the data in the spreadsheet is one number per cell, just import the data into a 2D array. The command 'reshape' can change the dimensionality of the array, but it's sometimes a little tricky to get things to reorder the way you want them. This code will take a 2D vector M of size Rx(2C) and turn it into a 3D data block of size [R C 2] alternating pages across the rows of the original. It's hard to tell with your numbers that it's working so I used a different set that's easier to keep track of.

    M = [1 2 3 4
        5 6 7 8];
    M = reshape(M,[size(M,1) 2 size(M,2)/2]);
    M = permute(M,[1 3 2])

Which results in:

    M(:,:,1) =
         1     3
         5     7
    M(:,:,2) =
         2     4
         6     8

If the data in the spreadsheet has two values per cell separated by a comma (as suggested in the comments), it will import into MATLAB as a series of cell arrays. Consider a spreadsheet with 4 cells (2x2) with the following data:

[  1,2  ][  3,4  ]
[  5,6  ][  7,8  ]

In MATLAB we can load this using

    [~,TXT]=xlsread('filename.xlsx');

And variable TXT would be:

    TXT =
      2×2 cell array
        {'1,2'}    {'3,4' }
        {'5,6'}    {'7,8'}

Operating on cells is a pain. I can't think of a way to do this without 'for' loops but once you're doing that, assigning to the third dimension is easy.

    M = zeros([size(TXT,1) size(TXT,2) 2]);
    for ii = 1:size(TXT,1)
        for jj = 1:size(TXT,2)
            temp = sscanf(char(TXT(ii,jj)),'%f,%f');
            M(ii,jj,:) = reshape(temp,[1 1 2]);
        end
    end

For the values above

    M(:,:,1) =
         1     3
         5     7
    M(:,:,2) =
         2     4
         6     8