How to read excel data include specific word? cell?

2 views (last 30 days)
Hi, all
[~,~,data] = xlsread('kpmarch.xlsx' );
data=[data(:,1) data(:,3) data(:,4) ];
There's 6columns in my excel file and I took A,C,D 3colomns form excel
next step, i only wanna take rows which include 'Planetary' in column C(OBSRVT_NM)
How can i do this..

Accepted Answer

Star Strider
Star Strider on 2 Aug 2022
Edited: Star Strider on 2 Aug 2022
Try this —
T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1085430/kpmarch.xlsx')
T1 = 744×6 table
OBSR_YMD A_VALUE OBSRVT_NM K_VALUE CRT_DT OBSR_VYMD _______________________ _______ __________________ _______ ______________ __________________ {'2022-03-31 21:00:00'} {'27'} {'Planetary' } {'2'} {'2022-04-30'} {'20220331210000'} {'2022-03-31 21:00:00'} {'18'} {'Fredericksburg'} {'2'} {'2022-04-30'} {'20220331210000'} {'2022-03-31 21:00:00'} {'35'} {'College' } {'2'} {'2022-04-30'} {'20220331210000'} {'2022-03-31 18:00:00'} {'27'} {'Planetary' } {'4'} {'2022-04-30'} {'20220331180000'} {'2022-03-31 18:00:00'} {'18'} {'Fredericksburg'} {'3'} {'2022-04-30'} {'20220331180000'} {'2022-03-31 18:00:00'} {'35'} {'College' } {'4'} {'2022-04-30'} {'20220331180000'} {'2022-03-31 15:00:00'} {'27'} {'Planetary' } {'4'} {'2022-04-30'} {'20220331150000'} {'2022-03-31 15:00:00'} {'18'} {'Fredericksburg'} {'4'} {'2022-04-30'} {'20220331150000'} {'2022-03-31 15:00:00'} {'35'} {'College' } {'4'} {'2022-04-30'} {'20220331150000'} {'2022-03-31 12:00:00'} {'27'} {'Planetary' } {'4'} {'2022-04-30'} {'20220331120000'} {'2022-03-31 12:00:00'} {'18'} {'Fredericksburg'} {'3'} {'2022-04-30'} {'20220331120000'} {'2022-03-31 12:00:00'} {'35'} {'College' } {'6'} {'2022-04-30'} {'20220331120000'} {'2022-03-31 09:00:00'} {'27'} {'Planetary' } {'4'} {'2022-04-30'} {'20220331090000'} {'2022-03-31 09:00:00'} {'18'} {'Fredericksburg'} {'3'} {'2022-04-30'} {'20220331090000'} {'2022-03-31 09:00:00'} {'35'} {'College' } {'5'} {'2022-04-30'} {'20220331090000'} {'2022-03-31 06:00:00'} {'27'} {'Planetary' } {'5'} {'2022-04-30'} {'20220331060000'}
Lv = ismember(T1{:,3},'Planetary');
Result = T1(Lv,[1 3 4])
Result = 248×3 table
OBSR_YMD OBSRVT_NM K_VALUE _______________________ _____________ _______ {'2022-03-31 21:00:00'} {'Planetary'} {'2'} {'2022-03-31 18:00:00'} {'Planetary'} {'4'} {'2022-03-31 15:00:00'} {'Planetary'} {'4'} {'2022-03-31 12:00:00'} {'Planetary'} {'4'} {'2022-03-31 09:00:00'} {'Planetary'} {'4'} {'2022-03-31 06:00:00'} {'Planetary'} {'5'} {'2022-03-31 03:00:00'} {'Planetary'} {'5'} {'2022-03-31 00:00:00'} {'Planetary'} {'4'} {'2022-03-30 21:00:00'} {'Planetary'} {'2'} {'2022-03-30 18:00:00'} {'Planetary'} {'2'} {'2022-03-30 15:00:00'} {'Planetary'} {'1'} {'2022-03-30 12:00:00'} {'Planetary'} {'2'} {'2022-03-30 09:00:00'} {'Planetary'} {'1'} {'2022-03-30 06:00:00'} {'Planetary'} {'1'} {'2022-03-30 03:00:00'} {'Planetary'} {'1'} {'2022-03-30 00:00:00'} {'Planetary'} {'2'}
There are likely also other approaches.
.

More Answers (1)

Walter Roberson
Walter Roberson on 2 Aug 2022
It is not possible to read rows selectively, other than by consecutive position.
You will need to do something such as
mask = ismember(data(:,3), 'Planetary');
subset = data(mask, [1 4]);
Note: we recommend that you switch to readtable()
data = readtable('kpmarch.xlsx');
mask = ismember(data.OBSRVT_NM, 'Planetary');
subset = data(mask, [1 4]);
Then subset{:,2} would be datetime objects.
Your xlsx file is odd: all of your numbers are represented as text.
  1 Comment
Nicolas Alfred
Nicolas Alfred on 2 Aug 2022
Thank you, I didn't know that rows can't be read selectively, I searched it for hours ..

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!