How can I import Excel data in a cell array?

Hi, I have a cell array as I coded here:

tt = cell (4,1);
for i = 1:4
  tt {i,1} = cell (16,1);
  for j = 1:16
      tt {i,1}{j,1} = zeros (2,6);
  end
end

and the data format in the Excel file is as the attached table. Would you please help me to import it into MATLAB properly?

[Ganesh Hegade]

You can import the data in excel using xlsread command.

Data = xlsread('TTT.xlsx');

Then import the data into cell.

[Sherwin]

Could you please explain the second step a little more?

[Guillaume]

Yes, Ganesh answered the easy bit, importing the data in matlab is trivial. The hard bit is reformatting it in the cell array format you want.

To answer that, more explanation is needed.

• Does the cell array exist before the import and is it the correct size, so that we can infer the structure of the excel file from the size of the cell arrays?
• Otherwise, is the structure of the excel file fixed (i.e. always 17 big blocks of 4x6 smaller block of 1x2 numbers?
• Or the size of big, medium and small black is all variable and to be determined from the excel file?

[Sherwin]

Thank you so much. Yes, the code exists before I import the data but I can change it easily. The size doesn't change at all (and of course through the 17 big blocks) but actually there are two 1x6 vectors, for each 17 big blocks and 4 rows. Maybe It's better to be coded like this:

tt = cell (4,1);
for i = 1:4
  tt {i,1} = cell (16,1);
  for j = 1:16
      tt {i,1}{j,1} = cell (2,1);
      for k = 1:2
          tt {i,1}{j,1}{k,1} = zeros (1,6);
      end
  end
end

and the data fits into this format exactly.The length of cells don't change at all.

Answer 1

[Marlies]

I would approach this in a different direction. Instead of making the framework first, and then filling that up, I would 'package' the data into cells from the lowest level to the highest level. This is something that MAT2CELL can do for you.

The only thing I struggled with is that your 'framework' has room for sixteen 'blocks', while your Excel-file has seventeen blocks. So my solution is:

• a 4x1 cell
• where each cell contains a 17-by-1 cell
• where every cell conrains a 2-by-1 cell

%%Import the data from excel
data = xlsread('TTT.xlsx');

%%Remove the 'headers' from the file
% Since I could not fit 17 blocks in sixteen cells, this might be the place
% where you use indexing to get rid of the 'irrelevant' block.
  data = data(3:end, 2:end);

%%First, place ever set of six corrsponding vectors inside one cell
divRow_1 = ones(size(data,1),1);                  % Keep the 8 rows as 8 rows
divCol_1 = repmat(6,1,size(data,2)/6);            % Place the columns per six into one cell (17 times)
perSix = mat2cell(data,divRow_1,divCol_1);

%%Second, pair every set of two vectors
divRow_2 = repmat(2,size(data,1)/2,1);            % Place the rows per two into one cell (4 times)
divCol_2 = ones(1,size(perSix,2));                % Keep the 17 columns as 17 columns
perTwo = mat2cell(perSix,divRow_2,divCol_2)';     % Transpose the output

%%Last, pair every set of seventeen vectors
divRow_3 = size(perTwo,1);                         % Place rows per 17 into one cell (four times)
divCol_3 = ones(size(perTwo,2),1);                 % Keep the four columns as four columns
perFour = mat2cell(perTwo,divRow_3, divCol_3)';    % Transpose the output

I hope this helps. Kind regards,

Marlies

[Sherwin]

I am so sorry, the last block (block 17) wasn't supposed to enter the code, I must've forgotten to delete them. Thank you so much for your comprehensive answer, it helped a lot.

Answer 2

[Marlies]

There is a second alternative, which is not answering the question you asked, but that might be giving a better starting point to work with your data.

If the data in the various blocks is numerical, I assume you want to be able to perform numerical analysis on it. For instance, compute the average value of the six data points of the ninth dataset for the third observation, but only for the top-observation ("AY7:BD7"). Or what if you would like to do that for every of the 8*16 sets of six-measurements.

Getting data out of a cell-array is not trivial, and getting data out of a cell inside a cell inside a cell is definitely not trivial. An alternative could be to place all data in a table-datatype (available since R2013b), and have the various row- and column numberings as a separate grouping variable. This allows the use of grouping commands like findgroups or splitapply.

You can use the code below on your data, but I also made up a numerical dataset, so you can see better what is going on. See attachment 'TTTdata.xlsx'.

I invented some names for the various grouping 'variables' to keep the code as readable as possible. I hope they resonate with the problem you have.

%%Import into table, and work with groupings

%%Import the data
data = xlsread('TTTdata.xlsx');
data = data(3:end, 2:end-6);

% Transpose the data (so it can be processed in an convenient way), and
% then put it in one long list
dataT = data';
dataL = dataT(:);

%%define some dimensions
%
% In total there are 16 Locations (defined as the columns). 
% Each location contains 6 sensors, and each sensor measures two properties.
% There are in total 4 observations ('measurements'), defined over the rows.

numSensor = 6;        
numProp = 2;

numLoc = size(dataT,1) / numSensor;             % This should give 16
numObs = size(dataT,2) / numProp;               % This should give 4
numPerObs = numLoc * numSensor * numProp;       % Amount of datapoints per observation
numPerProp = size(dataL,1) / numSensor;         % Unique Location-Observation combinations?

%%Create the grouping for the Sensors
grpSensor = (1:numSensor)';
grpSensor = repmat(grpSensor,numPerProp,1);

% If the sensors have indicative names, CATEGORICAL can be usefull here
% sensorNames = {'top','bottom','left','right','front','back'};
% grpSensor = categorical(grpSensor,1:6,sensorNames);

%%Create the grouping for properties (prop1 or prop2)
grpProp = [ones(numSensor*numLoc,1);2*ones(numSensor*numLoc,1)];
grpProp = repmat(grpProp,numObs,1);

% If the properties have indicative names, CATEGORICAL can be usefull here
% propNames = {'temperature','flow'};
% grpProp = categorical(grpProp,1:2,propNames);

%%Create a grouping for the Locations
grpLoc = repelem(1:numLoc,numSensor)';           % repelem has been around since R2015a
grpLoc = repmat(grpLoc,numProp*numObs,1);

%%Create a grouping for the Observations
grpObs = repelem(1:numObs,numPerObs)';           % repelem has been around since R2015a

%%Now combine all data:
Data = table(dataL, grpSensor, grpProp, grpLoc, grpObs);

[Sherwin]

That's amazing how you can easily handle the data, I learned a lot. Thank you so much, it means the world to me.