MATLAB: How to extract certain column of date-time data from a text file

datetimedlmreadsscanftextscanxlsread

I have a .cef text file and it is a mixture of strings and data. A part of the code is as below:

 % more irrelevant description on top
   DEPEND_0 = time_tags__C3_CP_EDI_EGD
end_variable = kine_flag__C3_CP_EDI_EGD
! END_CEFMERGE_INCLUDE = "C3_CH_EDI_EGD_DATASET.ceh"
!
DATA_UNTIL=EOF
!
2014-12-17T20:14:24.220514Z,    109.196,      1.526, 1, 0
2014-12-17T20:14:45.373833Z,    109.315,      0.763, 1, 0
2014-12-17T20:15:21.192066Z,    108.480,      0.763, 1, 0
2014-12-17T20:15:48.907884Z,    107.527,      0.763, 1, 0
2014-12-17T20:16:26.787885Z,    107.646,      0.763, 1, 0
2014-12-17T20:16:45.387492Z,    107.169,      0.763, 1, 0
2014-12-17T20:17:04.818033Z,    106.573,      0.763, 1, 0
2014-12-17T20:17:48.323033Z,    106.454,      0.763, 1, 0
...

I hope to extract the first column of the data, which are dates in ISO 8601 format, and use it to plot a graph. To do this my code have to neglect all the description above, as well as the remaining columns.

I have thought of using dlmread, textscan, xlsread, sscanf, fopen, but since the data type is date-time and it consists of integers and strings, it is quite a challenge to me.

2014-12-17T20:14:24.220514Z    
2014-12-17T20:14:45.373833Z   
2014-12-17T20:15:21.192066Z  
2014-12-17T20:15:48.907884Z
2014-12-17T20:16:26.787885Z

Best Answer

And, w/ all you considered, you neglected probably the easiest... :)

Putting your first sample data into a file I named 'jie.dat',

t=readtable('jie.dat','headerlines',7);
t.DT=datetime(t.Var1,'InputFormat','uuuu-MM-dd''T''HH:mm:ss.SSSSSSZ','TimeZone','UTC');
t.DT.Format=[t.DT.Format '.SSSSSS'];

The result of the above is...you can, of course, remove any variables not needed...

>> t
t =
  8×6 table
                Var1                  Var2     Var3     Var4    Var5                DT             
    _____________________________    ______    _____    ____    ____    ___________________________
    '2014-12-17T20:14:24.220514Z'     109.2    1.526    1       0       17-Dec-2014 20:14:24.220514
    '2014-12-17T20:14:45.373833Z'    109.31    0.763    1       0       17-Dec-2014 20:14:45.373833
    '2014-12-17T20:15:21.192066Z'    108.48    0.763    1       0       17-Dec-2014 20:15:21.192066
    '2014-12-17T20:15:48.907884Z'    107.53    0.763    1       0       17-Dec-2014 20:15:48.907884
    '2014-12-17T20:16:26.787885Z'    107.65    0.763    1       0       17-Dec-2014 20:16:26.787885
    '2014-12-17T20:16:45.387492Z'    107.17    0.763    1       0       17-Dec-2014 20:16:45.387492
    '2014-12-17T20:17:04.818033Z'    106.57    0.763    1       0       17-Dec-2014 20:17:04.818033
    '2014-12-17T20:17:48.323033Z'    106.45    0.763    1       0       17-Dec-2014 20:17:48.323033
>>

ADDENDUM:

function n=readHdrLines(filename)
  fid=fopen(filename,'r');  % open file...ensure filename fully-qualified name or in path
  % do error checking here for valid fid
  n=0;                      % initialize counter
  while ~feof(fid)          % loop until find key phrase
    n=n+1;                  % increment line counter
    if contains(fgetl(fid,'DATA_UNTIL=EOF')), break, end  % found it; quit
  end
  n=n+1;                    % account for the comment line after before first data
  fid=fclose(fid);          % close the file
end

then just fixup the above sample code to something like--

filename=fullfile(yourpath,yourfile);
nHdr=readHdrLines(filename);
t=readtable('jie.dat','headerlines',nHdr);
...

ADDENDUM 2:

The grep utility will do this a whole lot faster; and can also be done with regexp if have file in memory. Don't know how that would compare to fgetl, but the portion of the file header section isn't that long so performance won't be terribly slow however you choose to do it.

Related Solutions

MATLAB: Overwriting Data in Text File with Content from Matlab Table, sample code attached!

With the example you've shown what you're asking is not possible. The only way you could do what you want is if the replacement text has exactly the same number of characters as the one you replace. If it has less, you can add spaces to make it equal, but if it has more, like in your example, it's not physically possible as the rest of the file needs to be moved to make room for the extra characters. Hence you've got to rewrite everything after the replacement.

If you're willing to keep the formatting of the water and air column as it is originally, and assuming the header is always the same number of lines, then:

fid = fopen('somefile.txt', 'rt+')
for headerline = 1:10
   fgetl(fid); %skip header lines
end
for row = 1:size(newdata, 1) %assuming your cell array is newdata
   startline = ftell(fid); %get current position
   fseek(fid, 0, 'cof'); %required because switching from reading to writing
   fprintf(fid, '%s\t% +7.2f\t%+7.2f', newdata{row, 1:3}); %the format string assumes no value is above 999.99
   fseek(fid, startline, 'bof'); %return to beginning of line
   fgetl(fid); %read whole line to position file pointer to beginning of next line
end

Code is untested, there may be bugs in the formatting string, number of header lines, etc.

MATLAB: Using textscan to skip specific rows

Second attempt: this time we will first convert the awful file format into a standard CSV format with one header line, no empty rows, no quoted numbers, and no repeated headers. I used regexprep and simply defined appropriate regular expressions:

fnm = 'events file.csv';
str = fileread(fnm);
rgx = {'\s*\n\s*"type"[a-z",]+','"(\d+)\s*"'};
rep = {'','$1'};
str = regexprep(str,rgx,rep);

You could also save str if you want an improved CSV file. Now we can easily extract the data from this improved CSV string:

fmt = '%f%*q%f%f%f';
opt = {'HeaderLines',1, 'CollectOutput',true, 'Delimiter',','};
C = textscan(str,fmt,opt{:});
M = C{1}

giving:

M =
        92       14287           0           1
        20       16459           0           2
        14       19147           0           3
        14       20646           0           4
        14       22047           0           5
        14       24431           0           6
        14       26844           0           7
        14       28819           0           8
        14       30071           0           9
        14       31843           0          10
        14       34788           0          11
        14       36883           0          12
        14       38439           0          13
        14       39571           0          14
        14       40691           0          15
        14       41368           0          16
        14       42443           0          17
        20       42708           0          18
        14       44955           0          19
        14       46092           0          20
        14       47771           0          21
        14       48632           0          22
        14       52780           0          23
        14       54312           0          24
        14       56616           0          25
        14       58724           0          26
        14       59831           0          27
        14       61911           0          28
        14       62859           0          29
        14       64884           0          30
        14       67534           0          31
        14       68367           0          32
        14       70052           0          33
        20       70311           0          34
        14       72450           0          35
        14       73476           0          36
        14       75143           0          37
        14       76615           0          38
        20       76622           0          39

Tested on this file:

Best Answer

Related Solutions

MATLAB: Overwriting Data in Text File with Content from Matlab Table, sample code attached!

MATLAB: Using textscan to skip specific rows

Related Question