extract data for a specified date and time period

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

[Luna]

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]

"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?

Answer 1

[Guillaume]

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), :)

[Abhishek]

Thanks for you answer, i am attaching the file with this, might be helpful

[Guillaume]

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]

I am very sorry for that, it was by mistake I attached different file. I am attaching the original file with this

Answer 2

[Peter Perkins]

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.