How to extract data from data file?

I have an .xls file which consists of 1st column is dates and time and other n rows and n columns data.... in this file, I want to extract data from between 8/9/2015 21:00 to 8/10/2015 9:00 then next 8/10/2015 21:00 to 8/11/2015 9:00 like wise for same all interval in one output file.

Input: 8/9/2015 9:00 1392 14.51 11.57 11.37 8/9/2015 10:30 1395 13.97 15.4 14 8/9/2015 12:00 1398 13.93 17.11 16.4 8/9/2015 13:30 1401 13.87 18.64 17.44 8/9/2015 15:00 1404 13.86 19.66 17.73 8/9/2015 16:30 1407 13.88 19.23 16.19 8/9/2015 18:00 1410 13.61 16.55 14.56 8/9/2015 19:30 1413 12.92 13.51 12.38 8/9/2015 21:00 1416 12.58 10.91 10.95 8/9/2015 22:30 1419 12.54 10.24 10.53 8/10/2015 0:00 1422 12.51 9.74 10.13 8/10/2015 1:30 1425 12.49 9.52 9.98 8/10/2015 3:00 1428 12.47 10.28 10.13 8/10/2015 4:30 1431 12.46 9.4 8.96 8/10/2015 6:00 1434 12.46 9.02 8.97 8/10/2015 7:30 1437 13.22 9.45 9.59 8/10/2015 9:00 1440 14.52 11.44 10.5 8/10/2015 10:30 1443 14.03 13.71 11.88 8/10/2015 12:00 1446 13.99 14.58 12.76 8/10/2015 13:30 1451 13.97 16 14.13 8/10/2015 15:00 1452 13.96 15.99 13.57 8/10/2015 16:30 1455 14.01 14.77 12.96 8/10/2015 18:00 1458 13.96 14.56 12.3 8/10/2015 19:30 1461 12.98 11.28 10.47 8/10/2015 21:00 1464 12.6 9.17 9.45 8/10/2015 22:30 1467 12.55 7.962 8.86 8/11/2015 0:00 1470 12.52 7.958 8.65

Output: 8/9/2015 21:00 1416 12.58 10.91 10.95 8/9/2015 22:30 1419 12.54 10.24 10.53 8/10/2015 0:00 1422 12.51 9.74 10.13 8/10/2015 1:30 1425 12.49 9.52 9.98 8/10/2015 3:00 1428 12.47 10.28 10.13 8/10/2015 4:30 1431 12.46 9.4 8.96 8/10/2015 6:00 1434 12.46 9.02 8.97 8/10/2015 7:30 1437 13.22 9.45 9.59 8/10/2015 9:00 1440 14.52 11.44 10.5 8/10/2015 21:00 1464 12.6 9.17 9.45 8/10/2015 22:30 1467 12.55 7.962 8.86 8/11/2015 0:00 1470 12.52 7.958 8.65 .......... ... ... ... ...

and so on ....

[Preethi]

hi,

you can use xlsread() function.

[Sunil Oulkar]

I have hourly data from that .xls file I want to extract data between 6 am to 6 pm for each day in one file.

[Shubhangi Saini]

If it is a csv file then you can use

Pin_data = csvread('spect - Sheet1.csv');

col1 = Pin_data(:, 1); // to extract the data of 1st column

col2 = Pin_data(:, 2); // to extract the data of 2nd column

I hope this may help .

Answer 1

[Guillaume]

Rather than the outdated and less powerful xlsread you can use readtable to load your excel file into a table. You can then convert that table into a timetable with table2timetable which will make manipulating rows of data based on time much easier. You can then use timerange or standard comparison and logical operators to filter the rows of the timetable.

It's going to be something like:

t = readtable('yourxlsfile.xlsx');  %may need some extra options
t = table2timetable(t);
dates = t.Date;  %variable name 'Date' will be something else
tokeep = hour(dates) >= 6 & hour(dates) <= 18;   
filteredt = t(tokeep, :);  %only keep rows whose hour is between 6am and 6pm

[Sunil Oulkar]

I am using Matlab 2013b. I am getting error. For the reference please see the below attached input and output file.

[Guillaume]

That is a version that predates the very useful datetime type (came with 2014b) that makes date manipulation so much easier, the table type that makes reading excel files so much easier and the timetable type that makes manipulating tables based on time so much easier.

My advice: upgrade to a newer version.

A possible way to make it work in R2013b:

[~, ~, xlcontent] = xlsread('yourxlsfile.xlsx');
dates = datevec(xlcontent(:, datecolumn), 'dd/mm/yyyy HH:MM') %or whichever date format you use
tokeep = dates(:, 4) >= 6 & dates(:, 4) <= 18;
filteredcontent = xlcontent(tokeep, :);

[Sunil Oulkar]

Thank you!

Answer 2

[Nnamdi Onwuegbuchu]

Hi roomies, pls how can i extract imported data on matlab before sending it to model trainer