Can I parse a text file with dates and data using readmatrix or readtable?

I have used textscan for most of my parsing, but I want to try using readmatrix or readtable with hopes that it will be simpler.

I have some data in files like this:

08/12/22 18:07:13.390583296,,,0.1857243627309799,1
08/12/22 18:07:13.395899904,0.3213851451873779,1,,
08/12/22 18:07:13.400586752,,,0.1857263445854187,1
08/12/22 18:07:13.405899520,0.3213972449302673,1,,

The columns are

Date, Time, value1, validity1, value2, validity2

The two values are not synched so they alternate.

I tried using readtable and readmatrix but I don't know how to specify the datetime in the call. I don't understand the matlab instructions for these.

I tried various versions of

data = readtable(filepath,'FileType','text','TreatAsMissing','NaN','Datetime','dd/mm/yy hh:mm:ss.SSS','ExpectedNumVariables',5);

Similar with readmatrix.

I get an error

Expected input to match one of these values:
'datetime', 'text', 'exceldatenum'
The input, 'dd/mm/yy hh:mm:ss.SSS', did not match any of the valid values.

So I know it's something to do with the date, but looks like I need some help.

Answer 1

[Star Strider]

It would help to have the file to experiment with.

I would just use readtable with no other name-value pairs, and see the result.

The available examples of ‘Date’ are ambiguous, so it wlll be necessary to define the various fields, for example:

Date(1,:) = '08/12/22';
Datev = datetime(Date,'InputFormat','MM/dd/yy')

Datev = datetime
   12-Aug-2022

Change the 'InputFormat' string as appropriate if I guessed incorrectly.

The second column, if read as a character array, would be:

Time = '18:07:13.390583296';
Timev = datetime(Time,'InputFormat','HH:mm:ss.SSSSSSSSS')

Timev = datetime
   15-Aug-2022 18:07:13

DateTime = Datev + timeofday(Timev)

DateTime = datetime
   12-Aug-2022 18:07:13

DateTime.Format = 'MM/dd/yyyy HH:mm:ss.SSSSSS'

DateTime = datetime
   08/12/2022 18:07:13.390583

The rest should be straightforward, although I am not certain how you want to deal with the missing values in the other columns.

EDIT — (15 Aug 2022 at 21:06)

opts = detectImportOptions('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1098935/TestData.csv');
opts = setvaropts(opts, 'inertial_6286_148080_estYaw', 'InputFormat','MM/dd/uuuu HH:mm:ss.SSS');
T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1098935/TestData.csv', opts)

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.

T1 = 25×5 table
    inertial_6286_148080_estYaw    inertial_6286_148080_estYaw_valid    inertial_6257_145026_estYaw    inertial_6257_145026_estYaw_valid    Var5
    ___________________________    _________________________________    ___________________________    _________________________________    ____

      08/12/0022 18:07:13.390                       NaN                             NaN                             0.18572                   1 
      08/12/0022 18:07:13.395                   0.32139                               1                                 NaN                 NaN 
      08/12/0022 18:07:13.400                       NaN                             NaN                             0.18573                   1 
      08/12/0022 18:07:13.405                    0.3214                               1                                 NaN                 NaN 
      08/12/0022 18:07:13.410                       NaN                             NaN                             0.18575                   1 
      08/12/0022 18:07:13.415                   0.32141                               1                                 NaN                 NaN 
      08/12/0022 18:07:13.420                       NaN                             NaN                             0.19181                   1 
      08/12/0022 18:07:13.425                   0.32142                               1                                 NaN                 NaN 
      08/12/0022 18:07:13.430                       NaN                             NaN                             0.19182                   1 
      08/12/0022 18:07:13.435                   0.32143                               1                                 NaN                 NaN 
      08/12/0022 18:07:13.440                       NaN                             NaN                             0.19182                   1 
      08/12/0022 18:07:13.445                   0.32144                               1                                 NaN                 NaN 
      08/12/0022 18:07:13.450                       NaN                             NaN                             0.19184                   1 
      08/12/0022 18:07:13.455                   0.32145                               1                                 NaN                 NaN 
      08/12/0022 18:07:13.460                       NaN                             NaN                             0.19184                   1 
      08/12/0022 18:07:13.465                   0.32145                               1                                 NaN                 NaN 

.

Answer 2

[Stephen23]

"but I don't know how to specify the datetime in the call. I don't understand the matlab instructions for these."

READTABLE, READMATRIX, et al attempt to automagically decode the file format by reading the file and trying to make sense of the file content. For "basic" file formats this can work well, however for more complex files format the more help you will have to give them (e.g. specifying a precise datetime format). There are several ways how this can be achieved, but the versatile approach is to call DETECTIMPORTOPTIONS to scan the file and do its best to automagically determine the file format, and then use SETVAROPTS and SETVARTYPE to modify the details of any specific characteristic that it needs help with. Note that DETECTIMPORTOPTIONS also accepts input arguments!

Note that there are many many options here, and a bit of experimentation is often required. In particular take a look at the OPT structure, you will find many interesting things there to consider when you are importing file data.

fnm = 'test.txt';
opt = detectImportOptions(fnm, 'Delimiter',',');
opt = setvaropts(opt,'DateTime', 'InputFormat','d/M/u H:m:s.SSS');
tbl = readtable(fnm, opt)

tbl = 4×5 table
         DateTime          value1     validity1    value2     validity2
    ___________________    _______    _________    _______    _________

    8/12/22 18:7:13.390        NaN       NaN       0.18572         1   
    8/12/22 18:7:13.395    0.32139         1           NaN       NaN   
    8/12/22 18:7:13.400        NaN       NaN       0.18573         1   
    8/12/22 18:7:13.405     0.3214         1           NaN       NaN   

[John Petersen]

I get an error from the datetime input.

>>opt = detectImportOptions(filepath, 'Delimiter',',');
>>opt = setvaropts(opt,'DateTime','InputFormat','d/M/u H:m:s.SSS');

Error using matlab.io.ImportOptions/setvaropts (line 445)
Ambiguous parameter name: DateTime.

[Stephen23]

@John Petersen: of course you need to use the variable/column names of your data file (which you did not provide), not the random names I invented for my fake data file (which i attached to my answer).

It really would be much better if you uploaded a sample data file by clicking the paperclip button, because then a) you would not get confused by answers using fake data that only bears a passing resemblance to your data b) you get a working solution much faster.

[John Petersen]

I was just trying to simplify the problem by only focusing on the part of the data file that was giving me trouble.