Can I parse a text file with dates and data using readmatrix or readtable?
18 views (last 30 days)
Show older comments
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.
0 Comments
Accepted Answer
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)
.
0 Comments
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)
3 Comments
Stephen23
on 15 Aug 2022
Edited: Stephen23
on 15 Aug 2022
@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.
See Also
Categories
Find more on Timetables in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!