extract data for a specified date and time period

33 views (last 30 days)
I have an excel sheet from which I have to extract data for a specified date and time period
In my data sheet the data and time period is given in a single column and I am not able to read the data
also I will be working in different files so the lightning time will not be same, so I want the data to read it till HH: and after that it could take all the values
suppose I want data between 2019-03 - 31T23:51:48.502573..., to 2019-03 - 31T23:51:52.49048526..,
for this I have tried to with 2019-03 - 31T23:51:48** to 2019-03-31T23:51:52** , but it did not work
  2 Comments
Luna
Luna on 17 May 2019
Please add your excel file and your codes what you have done so far as attachment, so that we can see what is wrong.
Adam Danz
Adam Danz on 17 May 2019
"I have an excel sheet from which I have to extract data for a specified date and time period"
One option is to read in the entire file and then select the desired rows once you have all of the data. If the file is huge, another option is to just read in the first column, identify the desired rows, and then read in those rows.
" I am not able to read the data"
Why not? What have you tried and why isn't it working?

Sign in to comment.

Accepted Answer

Guillaume
Guillaume on 17 May 2019
Screenshots are useless. We can't test code on screenshots, we can't open screenshots to see what the actual formatting is. Attach an actual file instead.
Selecting a range of time should be as simple as:
data = readtable('C:\somewhere\somexlsfile.xlsx');
infmt = 'yyyy-MM - dd''T''HH:mm:ss.S'
starttime = datetime('2019-03 - 31T23:51:48.502573', 'InputFormat', infmt);
endtime = datetime('2019-03 - 31T23:51:52.49048526', 'InputFormat', infmt)
selected = data(isbetween(data.LightningTime, starttime, endtime), :)
  3 Comments
Guillaume
Guillaume on 18 May 2019
Your spreadsheet is nothing like your screenshot. It only has three columns, Latitude, Longitude and Amplitude. There's no date or time in it.
Abhishek
Abhishek on 20 May 2019
I am very sorry for that, it was by mistake I attached different file. I am attaching the original file with this

Sign in to comment.

More Answers (1)

Peter Perkins
Peter Perkins on 20 May 2019
Abhishek, you have not made clear what problem you are actually running into. One of the problems is that every 400 rows or so, your file repeats the column headers. It would be better if that didn't happen, but it's not insurmountable.
In a recent version of MATLAB, use detectImportOptions, set the format for the timestamps, and read the file using readtable. In very recent versions, read it using readtimetable.
>> io = detectImportOptions('Lightning.xlsx')
io =
SpreadsheetImportOptions with properties:
Sheet Properties:
Sheet: ''
Replacement Properties:
MissingRule: 'fill'
ImportErrorRule: 'fill'
Variable Import Properties: Set types by name using setvartype
VariableNames: {'LightningTime', 'Latitude', 'Longitude' ... and 4 more}
VariableTypes: {'char', 'double', 'double' ... and 4 more}
SelectedVariableNames: {'LightningTime', 'Latitude', 'Longitude' ... and 4 more}
VariableOptions: Show all 7 VariableOptions
Access VariableOptions sub-properties using setvaropts/getvaropts
PreserveVariableNames: false
Range Properties:
DataRange: 'A2' (Start Cell)
VariableNamesRange: 'A1'
RowNamesRange: ''
VariableUnitsRange: ''
VariableDescriptionsRange: ''
To display a preview of the table, use preview
>> io.VariableTypes{1} = 'datetime';
>> io = setvaropts(io,"LightningTime","InputFormat","uuuu-MM-dd'T'HH:mm:ss.SSSSSSSSS");
>> io = setvaropts(io,"LightningTime","DatetimeFormat","uuuu-MM-dd'T'HH:mm:ss.SSSSSSSSS");
>> tt = readtimetable('Lightning.xlsx',io);
>> head(tt)
ans =
8×6 timetable
LightningTime Latitude Longitude Height FlashType Amplitude Confidence
_____________________________ ________ _________ ______ _________ _________ __________
2019-03-31T23:51:48.502573226 22.83785 91.4414 0 0 -20561 70
2019-03-31T23:51:51.198730000 22.8673 91.5916 0 0 -34473 100
2019-03-31T23:51:51.535409000 22.906 91.6179 0 0 -29527 100
2019-03-31T23:51:50.391883220 23.66386 91.86991 0 0 -13396 100
2019-03-31T23:51:51.264972698 22.82961 91.52161 0 0 -34294 81
2019-03-31T23:51:52.928333000 22.9863 91.8522 0 0 -30974 100
2019-03-31T23:51:52.076124863 23.30425 91.75159 13216 1 5880 85
2019-03-31T23:51:52.490484526 22.95678 91.75609 17543 1 -10449 71
Now use
tt(ismissing(tt.LightningTime),:) = [];
to remove those headre rows, and then do whatever you want with your data. Probably something involving retime.

Community Treasure Hunt

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

Start Hunting!