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.

  0 Comments

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');
end
k = k+1;
end
fclose(fidi);
datemtx = fliplr(cell2mat(datnum).');
T1 = readtable('20190725_20190729_.txt', 'HeaderLines',2);
T1.Var1 = datetime(datemtx, 'InputFormat','yyyy MM dd');
with:
First10Rows_First7Columns = T1(1:10,1:7)
producing:
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
.

  5 Comments

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');
end
k = k+1;
end
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!