Converting cell array from excel to numbers

2019 5 17
3 个回答
回答已采纳
更新时间：2019 5 17
5 次查看（30 天）

0 个投票

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?

请先登录，再回答此问题。

 采纳的回答

Stephane Dauvillier
编辑：Stephane Dauvillier 2019-5-17

0 个投票

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 个评论
显示 -1更早的评论 隐藏 -1更早的评论

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

请先登录，再进行评论。

更多回答（2 个）

Stephane Dauvillier
编辑：Stephane Dauvillier 2019-5-17

0 个投票

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

1 个评论
显示 -1更早的评论 隐藏 -1更早的评论

Saeid
Saeid 2019-5-17
编辑：Saeid 2019-5-17
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)?

请先登录，再进行评论。

0 个投票

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

类别

在 帮助中心 和 File Exchange 中查找有关 Cell Arrays 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by