MATLAB Answers

Converting cell array from excel to numbers

13 views (last 30 days)
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:
cell2mat.png
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?

  0 Comments

Sign in to comment.

Accepted Answer

Stephane Dauvillier
Stephane Dauvillier on 17 May 2019
Edited: Stephane Dauvillier on 17 May 2019
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}

  1 Comment

Saeid
Saeid on 17 May 2019
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.

Sign in to comment.

More Answers (2)

Stephane Dauvillier
Stephane Dauvillier on 17 May 2019
Edited: Stephane Dauvillier on 17 May 2019
OK, so if your names are not unique:
data=readtable(FileName,'ReadVariableNames',false,'TreatAsEmpty' ,'-');
iHeight=strcmp(raws,'Height');
height = data{iHeight,3:end}

  1 Comment

Saeid
Saeid on 17 May 2019
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)?

Sign in to comment.


Stephane Dauvillier
Stephane Dauvillier on 17 May 2019
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

  0 Comments

Sign in to comment.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!