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:
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.
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')
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')
DateTime = Datev + timeofday(Timev)
DateTime.Format = 'MM/dd/yyyy HH:mm:ss.SSSSSS'
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)
More Answers (1)
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)