MATLAB Answers

The file I need to import has the dates separated by colons and 'readable' will not recognize them as dates.

2 views (last 30 days)
Hello, so I am trying to import a file using 'readtable'. The file uses dates and times, so I will use 'table2timetable' at some point. However, the dates that are being imported uses ':' to seperated the months, days, years. For example the format is dd:MM:yyyy HH:mm:ss . The Dates and Times are seperarted by a delimiter, so I would like to combine the dates and times into one column. But like I said earlier, MATLAB does not recognize the date format. I know I could just change the format in the file, but I am having to import a lot of files and would not like to do that everytime. I am attaching a short version of one file I am trying to import.


Sign in to comment.

Accepted Answer

Star Strider
Star Strider on 15 Jun 2020
Unfortunately, datetime has problems with unconventional delimiters.
Try this:
fidi = fopen('20190725_20190729_.txt');
k = 1;
while ~feof(fidi)
if k > 4
txtline = fgets(fidi);
txtsave = strrep(txtline, ':', ' ');
datnum{:,k} = sscanf(txtsave,'%f');
k = k+1;
datemtx = fliplr(cell2mat(datnum).');
T1 = readtable('20190725_20190729_.txt', 'HeaderLines',2);
T1.Var1 = datetime(datemtx, 'InputFormat','yyyy MM dd');
First10Rows_First7Columns = T1(1:10,1:7)
First10Rows_First7Columns =
10×7 table
Var1 Var2 Var3 Var4 Var5 Var6 Var7
___________ ________ ____ ______ ________ ________ ________
25-Jul-2019 11:16:27 206 206.47 0.011794 0.030183 0.039634
25-Jul-2019 11:18:55 206 206.47 0.011859 0.030229 0.039624
25-Jul-2019 11:20:56 206 206.47 0.011923 0.030303 0.039689
25-Jul-2019 11:23:36 206 206.47 0.011957 0.030381 0.039506
25-Jul-2019 11:27:58 206 206.48 0.011718 0.030215 0.039613
25-Jul-2019 11:30:01 206 206.48 0.012062 0.030735 0.04041
25-Jul-2019 11:33:53 206 206.48 0.012556 0.031577 0.041267
25-Jul-2019 11:36:54 206 206.48 0.012473 0.031313 0.040949
25-Jul-2019 11:40:07 206 206.49 0.013014 0.032393 0.042618
25-Jul-2019 11:42:04 206 206.49 0.01333 0.033174 0.043354


Show 2 older comments
Star Strider
Star Strider on 15 Jun 2020
My pleasure!
I have no idea what the problem is with your using fopen. I had no problems with it. (I am using R2020a, Update 3, and Windows 10.)
The data in that file does not start until the fifth line, so I just have the while loop iterate through the first four lines without doing anything. I refer you to the documentation for a detailed description of fgets. (I could also have used fgetl.) Here, I use it to read the file line-by-line, then with each line read the first field (up to the first comma delimiter), and return the numbers in 'dd MM yyyy' format in ‘datnum’. I then create ‘datemtx’ from it by converting the cell array into a numeric array, and flipping it column-wise (because datetime will not convert the original format correctly), read the original file into a table, and then replace the first table variable (all NaN originally) with the datetime array.
A more efficient version of the while loop:
while ~feof(fidi)
if k > 4
txtline = fgets(fidi);
datnum{:,k-4} = sscanf(txtline,'%f:%f:%f');
k = k+1;
The datetime call can be replaced by this more efficient version as well:
T1.Var1 = datetime(datemtx);
I didn’t see those improvements earlier because it took a while to find an approach that worked, and I was just happy to have found one.
The rest of the code is unchanged.

Sign in to comment.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!