Date/Time Processing and Formatting Issues

I'm very new to MATLAB and have been reading tutorials and posts on plotting data from an external file (csv) and am having trouble processing date/time data. The CSV file has the date/time field as yyyy:ddd:hh:mm:ss.sssssssss (inculded sample below).

2022:246:10:00:02.593994140,4.44E+01

2022:246:10:00:03.592987060,-7.60E+01

2022:246:10:00:04.092987060,2.25E+02

I have tried several ways to plot the data but always run into an error when trying to convert the date/time string into date/time (I'm sure I'm missing a very basic/fundamental item), inculding different variations of u:DDD:HH:mm:ss.ms.

[data,txt,raw] = xlsread('test.csv');

x = txt{2:end,1};

y = raw(:,1) ;

dt = datetime(x,'InputFormat', 'u:DDD:HH:mm:ss.ms');

plot(dt,y)

Appreciate any help!

[F_M]

Thanks again. The table is attached.

Answer 1

[Star Strider]

The 'yyyy' needs to be 'uuuu' (ISO year) to avoid problems with the conversion —

C = {'2022:246:10:00:02.593994140',4.44E+01
     '2022:246:10:00:03.592987060',-7.60E+01
     '2022:246:10:00:04.092987060',2.25E+02};

T = cell2table(C)

T = 3×2 table
                  C1                    C2 
    _______________________________    ____

    {'2022:246:10:00:02.593994140'}    44.4
    {'2022:246:10:00:03.592987060'}     -76
    {'2022:246:10:00:04.092987060'}     225

T.C1 = datetime(C(:,1),'InputFormat','uuuu:DDD:HH:mm:ss.SSSSSSSS')

T = 3×2 table
             C1              C2 
    ____________________    ____

    03-Sep-2022 10:00:02    44.4
    03-Sep-2022 10:00:03     -76
    03-Sep-2022 10:00:04     225

.

[F_M]

Thanks for the help. I tried it by adding to the code but think I'm not quite reading the date/time field right or am still doing something wrong after that. I tried two different ways of reading the date/time and end up with errors on both ways. Below is what I tried and the errors I get.

[data,txt,raw] = xlsread('test.csv');

x = txt{2:end,1};

y = raw(:,1) ;

dt = datetime(x,'InputFormat', 'uuuu:DDD:HH:mm:ss.SSSSSSSSS');

plot(dt,y)

With x=txt{2:end,1};

Unable to perform assignment with 0 elements on the right-hand side.

Error in test_plot_a (line 2)

x = txt{2:end,1};

With x=txt(:,1);

Error using datetime (line 651)

Unable to convert 'pixel0' to datetime using the format 'uuuu:DDD:HH:mm:ss.SSSSSSSSS'.

Error in test_plot_a (line 4)

dt = datetime(x,'InputFormat', 'uuuu:DDD:HH:mm:ss.SSSSSSSSS');

[Star Strider]

Please provide ‘test.csv’.

Also, depending on what you want, the readtable or readmatrix functions are likely better than xlsread. (I generally use readtable first for Answers problems, then switch to readmatrix if it appears to be more appropriate.) You have both of them in R2021b.

EDIT — (5 Oct 2022 at 12:54)

I do not see any strings that could be considered usablle as datetime inputs in the format originally presented. However a work-around is possible —

T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1146090/table_a.csv')

T1 = 2047×5 table
    Var1    Var2    Var3    Var4               Var5           
    ____    ____    ____    ____    __________________________

    2022    246      10      0      {'00.093994140,4.16E+01' }
    2022    246      10      0      {'01.092987060,-1.19E+00'}
    2022    246      10      0      {'01.592987060,-1.19E+00'}
    2022    246      10      0      {'02.092987060,4.44E+01' }
    2022    246      10      0      {'02.593994140,4.44E+01' }
    2022    246      10      0      {'03.592987060,-7.60E+01'}
    2022    246      10      0      {'04.092987060,2.25E+02' }
    2022    246      10      0      {'04.592987060,2.25E+02' }
    2022    246      10      0      {'05.092987060,2.92E+02' }
    2022    246      10      0      {'05.592987060,2.92E+02' }
    2022    246      10      0      {'06.094985961,1.80E+02' }
    2022    246      10      0      {'07.092987060,6.64E+01' }
    2022    246      10      0      {'07.593994140,6.64E+01' }
    2022    246      10      0      {'08.592987060,5.84E+01' }
    2022    246      10      0      {'09.092987060,-1.10E+02'}
    2022    246      10      0      {'09.592987060,-1.10E+02'}

V5c = cellfun(@(x)sscanf(x,'%f,%f'),T1{:,5}, 'Unif',0);
V5 = cell2mat(V5c').';

DT = compose('%4d:%03d:%02d:%02d:%012.9f\n',[T1{:,1:4} V5(:,1)]);
DateTime = datetime(DT,'InputFormat','uuuu:DDD:HH:mm:ss.SSSSSSSS');

Something = V5(:,2);
T2 = table(DateTime,Something)

T2 = 2047×2 table
          DateTime          Something
    ____________________    _________

    03-Sep-2022 10:00:00       41.6  
    03-Sep-2022 10:00:01      -1.19  
    03-Sep-2022 10:00:01      -1.19  
    03-Sep-2022 10:00:02       44.4  
    03-Sep-2022 10:00:02       44.4  
    03-Sep-2022 10:00:03        -76  
    03-Sep-2022 10:00:04        225  
    03-Sep-2022 10:00:04        225  
    03-Sep-2022 10:00:05        292  
    03-Sep-2022 10:00:05        292  
    03-Sep-2022 10:00:06        180  
    03-Sep-2022 10:00:07       66.4  
    03-Sep-2022 10:00:07       66.4  
    03-Sep-2022 10:00:08       58.4  
    03-Sep-2022 10:00:09       -110  
    03-Sep-2022 10:00:09       -110  

Getting that into a form I could work with was something of an adventure!

.

[F_M]

Thanks again for digging through it.

I was able to take pieces of the previous suggestions and was able to make it work without having to compose the string. Here's the code:

[data,txt,raw] = xlsread('table_a.csv');

x = txt(:,1);

y = data(:,1);

dt = datetime(x,'InputFormat', 'uuuu:DDD:HH:mm:ss.SSSSSSSSS');

plot(dt,y)

I was able to verify that the date/time was being read correctly as a string (maybe it has to do with xslread as opposed to readtable) by displaying x and then dt:

disp(x) output (truncated):

{'2022:246:10:00:00.093994140'}

{'2022:246:10:00:01.092987060'}

{'2022:246:10:00:01.592987060'}

{'2022:246:10:00:02.092987060'}

disp(dt) output (truncated):

03-Sep-2022 10:00:00

03-Sep-2022 10:00:01

03-Sep-2022 10:00:01

03-Sep-2022 10:00:02

Here's the plot:

[Star Strider]

My pleasure!

The xlsread function has been superceded by others and is now ‘Not recommended’ (and may be deprecated in future releases), so I have stopped using it in MATLAB Answers for compatibility reasons. (Also, it doesn’t work with the online Run feature.) I now use readtable or readmatrix instead.

I was able to make my datetime code work after creating the appropriate vectors using compose. Given the format of your file, I doubt if it would be possible to use detectImportOptions to get the same result.

Anyway, my datetime code worked with it, so I learned something by figuring out how to create the appropriate character arrays to use with datetime in files with unusual formats.

.

Answer 2

[millercommamatt]

dt = datetime(x,'InputFormat', 'yyyy:DDD:HH:MM:ss.SSSSSSSSS');

[F_M]

Thanks for your help.

I tried it but now get this error:

Unable to perform assignment with 0 elements on the right-hand side.

Error in test_plot_a (line 2)

x = txt{2:end,1};

I tried changing how the date/time is read as follows but get the error below:

[data,txt,raw] = xlsread('test.csv');

x = txt(:,1);

y = raw(:,1) ;

dt = datetime(x,'InputFormat', 'yyyy:DDD:HH:MM:ss.SSSSSSSSS');

plot(dt,y)

Warning: The format 'yyyy:DDD:HH:MM:ss.SSSSSSSSS' contains a field for month (M) in what appears to be a time portion. You

might have intended to use the symbol for minute (m) rather than for month (M). See the datetime.Format property for a

complete description of the identifiers used in datetime formats.

> In datetime (line 636)

In test_plot_a (line 4)

Warning: The format 'yyyy:DDD:HH:MM:ss.SSSSSSSSS' contains a field for month (M) in what appears to be a time portion. You

might have intended to use the symbol for minute (m) rather than for month (M). See the datetime.Format property for a

complete description of the identifiers used in datetime formats.

> In verifyFormat (line 34)

In datetime (line 642)

In test_plot_a (line 4)

Error using datetime (line 651)

Unable to convert 'pixel0' to datetime using the format 'yyyy:DDD:HH:MM:ss.SSSSSSSSS'.

Error in test_plot_a (line 4)

dt = datetime(x,'InputFormat', 'yyyy:DDD:HH:MM:ss.SSSSSSSSS');

[millercommamatt]

That's what I get for not testing this before posting. This has the correct minute part of the format string

dt = datetime(x,'InputFormat', 'yyyy:DDD:HH:mm:ss.SSSSSSSSS');

[F_M]

I tried it with the same errors:

With x=txt{2:end,1};

Unable to perform assignment with 0 elements on the right-hand side.

Error in test_plot_a (line 2)

x = txt{2:end,1};

With x=txt(:,1);

Warning: The format 'yyyy:DDD:HH:mm:ss.SSSSSSSSS' contains fields for day of year (D) and Gregorian year (y). This will cause

unexpected results when converting from text. Use ISO year (u) in place of Gregorian year (y). See the datetime.Format

property for a complete description of the identifiers used in datetime formats.

> In datetime (line 636)

In test_plot_a (line 4)

Error using datetime (line 651)

Unable to convert 'pixel0' to datetime using the format 'yyyy:DDD:HH:mm:ss.SSSSSSSSS'.

Error in test_plot_a (line 4)

dt = datetime(x,'InputFormat', 'yyyy:DDD:HH:mm:ss.SSSSSSSSS');