readtable - Reading dates and times from text file

I am struggling to use readtable to read data from a text file. The person supplying this file has changed it from a comma-delimited format to a tab-delimted format. I was using setvaropts and readtable to read the csv file, but I can't work out how to use them with this text file.

A typical file is attached. This is what the top of the file looks like:

Date Time	Rain Rain (mm)
2022/07/07 11:17:01.0	0.00
2022/07/07 11:18:01.0	0.00
2022/07/07 11:19:01.0	0.00
2022/07/07 11:20:01.0	0.00
2022/07/07 11:21:01.0	0.00

And this is how readtable sees it:

         Var1           Var2      Var3
    ______________    ________    ____

    {'2022/07/07'}    11:17:01     0  
    {'2022/07/07'}    11:18:01     0  
    {'2022/07/07'}    11:19:01     0  
    {'2022/07/07'}    11:20:01     0  
    {'2022/07/07'}    11:21:01     0  

How can I get these three variables into two, a datetime and a number?

Answer 1

[Stephen23]

tbl = readtable('data.txt', 'Delimiter','\t', 'Format','%{y/M/d H:m:s.S}D%f')

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

tbl = 99×2 table
         DateTime         RainRain_mm_
    __________________    ____________

    2022/7/7 11:17:1.0         0      
    2022/7/7 11:18:1.0         0      
    2022/7/7 11:19:1.0         0      
    2022/7/7 11:20:1.0         0      
    2022/7/7 11:21:1.0         0      
    2022/7/7 11:22:1.0         0      
    2022/7/7 11:23:1.0         0      
    2022/7/7 11:24:1.0         0      
    2022/7/7 11:25:1.0         0      
    2022/7/7 11:26:1.0         0      
    2022/7/7 11:27:1.0         0      
    2022/7/7 11:28:1.0         0      
    2022/7/7 11:29:1.0         0      
    2022/7/7 11:30:1.0         0      
    2022/7/7 11:31:1.0         0      
    2022/7/7 11:32:1.0         0      

tbl.DateTime

ans = 99×1 datetime array
   2022/7/7 11:17:1.0
   2022/7/7 11:18:1.0
   2022/7/7 11:19:1.0
   2022/7/7 11:20:1.0
   2022/7/7 11:21:1.0
   2022/7/7 11:22:1.0
   2022/7/7 11:23:1.0
   2022/7/7 11:24:1.0
   2022/7/7 11:25:1.0
   2022/7/7 11:26:1.0
   2022/7/7 11:27:1.0
   2022/7/7 11:28:1.0
   2022/7/7 11:29:1.0
   2022/7/7 11:30:1.0
   2022/7/7 11:31:1.0
   2022/7/7 11:32:1.0
   2022/7/7 11:33:1.0
   2022/7/7 11:34:1.0
   2022/7/7 11:35:1.0
   2022/7/7 11:36:1.0
   2022/7/7 11:37:1.0
   2022/7/7 11:38:1.0
   2022/7/7 11:39:1.0
   2022/7/7 11:40:1.0
   2022/7/7 11:41:1.0
   2022/7/7 11:42:1.0
   2022/7/7 11:43:1.0
   2022/7/7 11:44:1.0
   2022/7/7 11:45:1.0
   2022/7/7 11:46:1.0

[dormant]

Many thanks.

Answer 2

[dpb]

%type data.txt
opt=detectImportOptions('data.txt','ReadVariableNames',1,'VariableNamesLine',1)

opt = 
  DelimitedTextImportOptions with properties:

   Format Properties:
                    Delimiter: {'\t'  ' '}
                   Whitespace: '\b'
                   LineEnding: {'\n'  '\r'  '\r\n'}
                 CommentStyle: {}
    ConsecutiveDelimitersRule: 'join'
        LeadingDelimitersRule: 'ignore'
       TrailingDelimitersRule: 'ignore'
                EmptyLineRule: 'skip'
                     Encoding: 'UTF-8'

   Replacement Properties:
                  MissingRule: 'fill'
              ImportErrorRule: 'fill'
             ExtraColumnsRule: 'addvars'

   Variable Import Properties: Set types by name using setvartype
                VariableNames: {'Date', 'Time', 'Rain'}
                VariableTypes: {'char', 'duration', 'double'}
        SelectedVariableNames: {'Date', 'Time', 'Rain'}
              VariableOptions: [1-by-3 matlab.io.VariableImportOptions] 
	Access VariableOptions sub-properties using setvaropts/getvaropts
           VariableNamingRule: 'modify'

   Location Properties:
                    DataLines: [3 Inf]
            VariableNamesLine: 1
               RowNamesColumn: 0
            VariableUnitsLine: 0
     VariableDescriptionsLine: 0 
	To display a preview of the table, use preview

tData=readtable('data.txt',opt);
opt.VariableTypes(1)={'datetime'};
opt=setvaropts(opt,'Date','InputFormat','yyyy/MM/dd');
tData=readtable('data.txt',opt);
tData.Date=tData.Date+tData.Time;
tData.Date.Format='default';
tData=removevars(tData,'Time');
head(tData)

            Date            Rain
    ____________________    ____

    07-Jul-2022 11:18:01     0  
    07-Jul-2022 11:19:01     0  
    07-Jul-2022 11:20:01     0  
    07-Jul-2022 11:21:01     0  
    07-Jul-2022 11:22:01     0  
    07-Jul-2022 11:23:01     0  
    07-Jul-2022 11:24:01     0  
    07-Jul-2022 11:25:01     0  

They didn't do you any favors by having four variable labels for three variables and the extra space with the units will be a pain to use if keep that way.

The problem with the sample dataset is that 07/07 is ambiguous as to which is month and which is day; I made a choice above; you'll have to confirm if it is that or the other way 'round.

If you build the import options struct to match once the format is finalized, then you can save it and use it for all files of that type; you do not have to build one for every file.

It would be better if they would build the full date/time string as one instead of two and also pick an unambiguous output format for the date -- like the three-letter month, say.