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

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.

Answer 1

[Star Strider]

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

.

[Forrest Ward]

Thank you for your reply! For some reason, when I use 'fopen' I get this error message

"fl:filesystem:SystemError "

[Forrest Ward]

Also, would you mind walking me through your while and nested if statements? I don't recognize a few of those functions that you're calling inside of them.

[Star Strider]

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.

.

[Forrest Ward]

Thank you very much for the well described response!

[Star Strider]

As always, my pleasure!

This is an interesting problem!