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

3 views (last 30 days)
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.

Accepted Answer

Star Strider
Star Strider on 15 Aug 2022
Edited: Star Strider on 15 Aug 2022
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
.

More Answers (1)

Stephen23
Stephen23 on 15 Aug 2022
Edited: Stephen23 on 15 Aug 2022
"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
  3 Comments
John Petersen
John Petersen on 15 Aug 2022
I was just trying to simplify the problem by only focusing on the part of the data file that was giving me trouble.

Sign in to comment.

Products


Release

R2021a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!