Converting cell array from excel to numbers

Reading from a large excel sheet using xlsread, I am trying to read data and find numerical values assigned to specific lines in the data sheet. Part of the data sheet looks like this:

The code I am using is:

[numbers,strings,raws]=xlsread(FileName);
iHeight=find(strcmp(raws,'Height'))
ShowRaws=raws(iHeight,3:end)
height=cell2mat(raws(iHeight,3:end))

But after running it I get the following results:

iHeight=find(strcmp(raws,'Height'))
iHeight =

     1

ShowRaws =

  1×10 cell array

  Columns 1 through 6

    {'cm'}    {[NaN]}    {[62.29]}    {[68.5]}    {[73.8]}    {[83.81]}

  Columns 7 through 10

    {[70.43]}    {[70.57]}    {[71.06]}    {[109.88]}

And for thew last line:

  height=cell2mat(raws(iHeight,3:end))

I get the error message:

Error using cell2mat (line 45)
All contents of the input cell array must be of the same data type.

Error in CompareDataBases (line 12)
height=cell2mat(raws(iHeight,3:end))

Here I am trying to read the value "height" knowing where the title appears, but the raw format data cannot be converted to numeric because obviously not all the data values in the loaded array are of the ame format. It also looks like the '-' symbol in the excel sheet has not even been read.

How can I overcome this prbolem?

Answer 1

[Stephane Dauvillier]

Hi,

Before answering your question, know that most of the time the call of function find is useless. In your case you can do the following:

[numbers,strings,raws]=xlsread(FileName);
iHeight=strcmp(raws,'Height')
ShowRaws=raws(iHeight,3:end)
height=cell2mat(raws(iHeight,3:end))

As for your problem, it came from the "-" data which is transform into a '-' which canot be convert into number (even NaN).

I strongly suggest you you the function readtable:

data=readtable(FileName,'ReadVariableNames',false,'ReadRowNames' ,true,'TreatAsEmpty' ,'-');
height = data{'Height',2:end}

You can also do the following

height = data{1,2:end}

[Saeid]

Hi Stephane, thanks for the answer. It looks like it can solve my problem, but what if the term "Height" occurs in more than one row? I tried it on the real database and your suggestion seems to find only the first row.

Answer 2

[Stephane Dauvillier]

OK, so if your names are not unique:

data=readtable(FileName,'ReadVariableNames',false,'TreatAsEmpty' ,'-');
iHeight=strcmp(raws,'Height');
height = data{iHeight,3:end}

[Saeid]

Thanks again, Stephane. We're almost there! I trid this again on the real database and since it has a lot of empty spaces in the row where the heights are given, it turns out a cell array like this:

{'63.85' }    {'61.07' }    {'69.47' }    {'65.82' }    {0×0 char}    {'63.99' }    {'58.21' }    {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}
    {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}
    {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}

And trying to convert these into numeric using cell2mat turns the error message:

Error using cat
Dimensions of arrays being concatenated are not consistent.

Error in cell2mat (line 78)
        m = cat(1,m{:});

What am I doing wrong again (it could also work for me if it helped to turn all the {0×0 char} to numerical zeros)?

Answer 3

[Stephane Dauvillier]

OK,

Well dealing with table, each variables (columns) of the table will have ONE datatype. So that means you have something in your column that is not a number. Try to identify that and put what you find in the TreatAsEmpty property of the readtable