Reading a text file using readtable, Matlab stubbornly refuses to accept dates in anything but US-format

Here's a programme I wrote to read in a data file. Rather than using the trusty, old-fashioned method I've always used (fopen, fgetl etc), I thought I'd use this fancy 'readtable' method. Half a day later, I wish I'd not bothered. The online help on this subject is very confusing, with changes in each version of Matlab from 'Parameter','Value' to 'Parameter=Value' to Parameter.value = ... ways of doing things and so many parameters and sub-parameters in the readtable function that I got very confused. As you can see, I've tried 3 or 4 times to set the date format to read data from 10th March, but it still comes out as 3rd October.

Any help would be greatly appreciated.

%Script to read in data files 
clear all
datetime.setDefaultFormats('default','dd/MM/yyyy');
fid=fopen('Myfile','r');
opts = detectImportOptions('Myfile');
opts.VariableTypes(2)={'datetime'};
opts.VariableOptions(1).DatetimeFormat='dd/MM/yy'
opts.VariableOptions(1).InputFormat='dd/MM/yy'
% setvaropts(opts,VariableOptions(1).InputFormat,'dd/MM/yyyy');
A=readtable('Myfile','NumHeaderLines',1);
A.Date.Format = 'dd/MM/yyyy'
fclose(fid)
A{:,1}=datetime(A{:,1},'InputFormat','dd/MM/yyyy', 'Format','dd/MM/yyyy')
d=datevec(A{:,1})+datevec(A{:,2});
d(:,1)=d(:,1)+2000;
t0=datenum(d);

Here's the data-file I'm trying to read:

Patches found at BAKE00CAN between 10-Mar-2024 and 16-Mar-2024:
Date      Time      Latitude   Longitude  sTEC_inc Duratn./s   
10/03/24  00:08:00     71.70    -88.73       3.2      1350
10/03/24  00:14:30     69.60   -110.59       4.9       840
10/03/24  00:16:00     62.46    -94.23       3.8      1620
10/03/24  00:18:00     64.35    -83.21       8.2      1470
10/03/24  00:23:30     72.70   -110.84      17.9      5370
10/03/24  00:25:30     63.86    -91.88       2.4       450
10/03/24  00:28:30     67.25    -85.28       4.1      1710
10/03/24  00:29:30     73.89    -90.16       2.7       570
10/03/24  00:31:00     62.88    -93.91       3.7       870

...but it comes out as:

A =

9×6 table

Date Time Latitude Longitude sTEC_inc Duratn__s

__________ ________ ________ _________ ________ _________

03/10/0024 00:08:00 71.7 -88.73 3.2 1350

03/10/0024 00:14:30 69.6 -110.59 4.9 840

03/10/0024 00:16:00 62.46 -94.23 3.8 1620

03/10/0024 00:18:00 64.35 -83.21 8.2 1470

03/10/0024 00:23:30 72.7 -110.84 17.9 5370

03/10/0024 00:25:30 63.86 -91.88 2.4 450

03/10/0024 00:28:30 67.25 -85.28 4.1 1710

03/10/0024 00:29:30 73.89 -90.16 2.7 570

03/10/0024 00:31:00 62.88 -93.91 3.7 870

Answer 1

[Star Strider]

You need to pass the options structure to readtable.

Perhaps something like this —

type('Dave_2024_07_26.txt')

Date      Time      Latitude   Longitude  sTEC_inc Duratn./s   
10/03/24  00:08:00     71.70    -88.73       3.2      1350
10/03/24  00:14:30     69.60   -110.59       4.9       840
10/03/24  00:16:00     62.46    -94.23       3.8      1620
10/03/24  00:18:00     64.35    -83.21       8.2      1470
10/03/24  00:23:30     72.70   -110.84      17.9      5370
10/03/24  00:25:30     63.86    -91.88       2.4       450
10/03/24  00:28:30     67.25    -85.28       4.1      1710
10/03/24  00:29:30     73.89    -90.16       2.7       570
10/03/24  00:31:00     62.88    -93.91       3.7       870

opts = detectImportOptions('Dave_2024_07_26.txt', 'VariableNamingRule','preserve');
opts = setvaropts(opts,'Date','InputFormat','dd/MM/uuuu');
A = readtable('Dave_2024_07_26.txt', opts)

A = 9x6 table
       Date         Time      Latitude    Longitude    sTEC_inc    Duratn./s
    __________    ________    ________    _________    ________    _________

    10/03/0024    00:08:00      71.7        -88.73        3.2        1350   
    10/03/0024    00:14:30      69.6       -110.59        4.9         840   
    10/03/0024    00:16:00     62.46        -94.23        3.8        1620   
    10/03/0024    00:18:00     64.35        -83.21        8.2        1470   
    10/03/0024    00:23:30      72.7       -110.84       17.9        5370   
    10/03/0024    00:25:30     63.86        -91.88        2.4         450   
    10/03/0024    00:28:30     67.25        -85.28        4.1        1710   
    10/03/0024    00:29:30     73.89        -90.16        2.7         570   
    10/03/0024    00:31:00     62.88        -93.91        3.7         870   

Mth = month(A.Date)

Mth = 9x1

3 3 3 3 3 3 3 3 3

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

A.Date.Format = 'dd/MM/20yy'

A = 9x6 table
       Date         Time      Latitude    Longitude    sTEC_inc    Duratn./s
    __________    ________    ________    _________    ________    _________

    10/03/2024    00:08:00      71.7        -88.73        3.2        1350   
    10/03/2024    00:14:30      69.6       -110.59        4.9         840   
    10/03/2024    00:16:00     62.46        -94.23        3.8        1620   
    10/03/2024    00:18:00     64.35        -83.21        8.2        1470   
    10/03/2024    00:23:30      72.7       -110.84       17.9        5370   
    10/03/2024    00:25:30     63.86        -91.88        2.4         450   
    10/03/2024    00:28:30     67.25        -85.28        4.1        1710   
    10/03/2024    00:29:30     73.89        -90.16        2.7         570   
    10/03/2024    00:31:00     62.88        -93.91        3.7         870   

Mth = month(A.Date)

Mth = 9x1

3 3 3 3 3 3 3 3 3

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

.

[Dave]

Thanks so much for that, Star Strider. Yes, the vital missing link was including the opts variable in the readtable command. Seems obvious now, but I just figured that, as opts is defined using the text filename, the readtable command would know about the existence of opts.

Anyway, thanks for your prompt reply.

Dave

[Star Strider]

As always, my pleasure!

[Peter Perkins]

'dd/MM/20yy' assumes that two-digit years are always 21st century, which might be correct for you. 'dd/MM/yy' might be preferable if things like 10/03/95 are possible.

Also: if all your dates are 10/03/24, readtable should interpret that as Oct 3rd or Mar 10th depending on what locale your system is set to. If there are some 10/25/24's mixed in, that's unambiguously MM/dd, or there are some 25/03/24's mixed in, that's unambigously dd/MM. And readtable should figure those out.

Answer 2

[Steven Lord]

The readtable function is very flexible, and that can sometimes make it a little more difficult to use than the lower-level file I/O functions you're used to.

But one thing you can do to help you import your data is to use the Import Tool. This lets you interactively control how MATLAB imports your data. Set the options in the app, try importing the data, and make sure it imports as you wanted. Repeat until you're satisfied with how the data gets imported. Then you could generate a script from the app that you could use if you need to import data from multiple files in the same format or that you could use to learn the commands and options that MATLAB used to import the data.

[Dave]

Thanks Steven,

This is another thing I didn't realise you could do in Matlab. I tend to favourthe command line over graphical methods, but this would be the missing link between the two methods, I guess.

Thanks for your prompt reply.

Dave