Converting cell array from excel to numbers
11 次查看(过去 30 天)
显示 更早的评论
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](https://www.mathworks.com/matlabcentral/answers/uploaded_files/219933/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 个评论
采纳的回答
Stephane Dauvillier
2019-5-17
编辑:Stephane Dauvillier
2019-5-17
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}
更多回答(2 个)
Stephane Dauvillier
2019-5-17
编辑:Stephane Dauvillier
2019-5-17
OK, so if your names are not unique:
data=readtable(FileName,'ReadVariableNames',false,'TreatAsEmpty' ,'-');
iHeight=strcmp(raws,'Height');
height = data{iHeight,3:end}
Stephane Dauvillier
2019-5-17
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 个评论
另请参阅
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!