Reading in csv data with separate columns of dates and times into MATLAB R2017b.

I found a way to read in csv dates and times in the command window using:

>> opts = detectImportOptions('Verif10_31_2018.csv');

>> opts.VariableOptions(1,1).DatetimeFormat = 'dd-MMM-yyyy';

>> opts.VariableOptions(1,2).DatetimeFormat = 'H:m:s';

>> dt = readtable('Verif10_31_2018.csv', opts);

When I try the same commands in a Live Script they don't read in cleanly. The dates come in with 00:00:00 appended to the end of each date, and the times come in with today's date added to the front. I found ways to combine the two pieces of data and plot them, but I am struggling to compare this data set with other data with the standard datetime stamps. This is what I have tried:

opts = detectImportOptions('Verif10_31_2018.csv');

% setvaropts(opts,VariableOptions.Format='dd-MMM-yyyy'); %Column 1 has date in this format.

% setvaropts(opts,VariableOptions.Format='H:mm:s'); %Column 2 has the times.

% opts.VariableOptions(1,1).InputFormat = 'dd-MMM-yyyy';

% opts.VariableOptions(1,2).InputFormat = 'H:m:s';

% setvaropts(opts,VariableOptions(1,1),'InputFormat','dd-MMM-yyyy');

% setvaropts(opts,VariableOptions(1,2),'InputFormat','H:m:s');

% setvaropts(opts,VariableOptions(1,1),'DatetimeFormat','dd-MMM-yyyy');

% setvaropts(opts,VariableOptions(1,2),'DatetimeFormat','H:m:s');

setvaropts(opts,'InputFormat','dd-MMM-yyyy');

setvaropts(opts,'InputFormat','H:m:s');

dt = readtable('Verif10_31_2018.csv', opts);

The commented out lines are the previous attemps. Most of the attempts give a warning saying to use setvaropts to set the format because the software is not sure of the correct format for the dates.

Sample of data:

Date,Time,Chm_SP,Chm_PV,Hum_SP,Hum_PV,AS_SP,AS_PV,Comment,Alarms,

10/31/2018,4:34:24 PM,25,22.1,0000,0045,48,33,Start Logging,

10/31/2018,4:34:24 PM,25,22.1,0000,0045,48,33,Oscillating Vertical Spray Down,

10/31/2018,4:35:24 PM,22.2,21.4,0000,0075,48,39.4,,

10/31/2018,4:36:24 PM,22.5,22.2,0000,0095,48,38,,

10/31/2018,4:37:24 PM,22.8,22.9,0000,0100,48,37.6,,

10/31/2018,4:38:24 PM,23.1,23.3,0000,0100,48,38,,

10/31/2018,4:39:24 PM,23.4,23.5,0000,0100,48,41,,

10/31/2018,4:40:24 PM,23.7,23.5,0000,0100,48,40.9,,

10/31/2018,4:41:24 PM,24,23.6,0000,0100,48,41.1,,

10/31/2018,4:42:24 PM,24.3,23.7,0000,0100,48,42.3,,

How can I read in this data to get it into standard timestamp format?

[madhan ravi]

attach your file

[Timothy Potter]

Here is the file with more than one day's data.

Answer 1

[Peter Perkins]

Timothy, I'm on a newer version of MATLAB and readtable has "moved ahead", but I think this is close enough to what you want to get you going:

>> t.Timestamp = t.Var1 + timeofday(t.Var2);
>> t.Timestamp.Format = 'dd-MMM-yyyy HH:mm:ss';
>> t = t(:,[end 3:end-1]);
>> head(t)
ans =
  8×9 table
         Timestamp          Var3    Var4    Var5    Var6    Var7    Var8          Var9             Var10   
    ____________________    ____    ____    ____    ____    ____    ____    _________________    __________
    01-Nov-2018 08:18:25     50       50     70      69      48       48    {'Start Logging'}    {0×0 char}
    01-Nov-2018 08:18:25     50       50     70      69      48       48    {'ATCU (ACC29)' }    {0×0 char}
    01-Nov-2018 08:19:25     50       50     70      70      48     48.3    {0×0 char       }    {0×0 char}
    01-Nov-2018 08:20:25     50       50     70      70      48     48.1    {0×0 char       }    {0×0 char}
    01-Nov-2018 08:21:25     50     50.1     70      68      48     48.1    {0×0 char       }    {0×0 char}
    01-Nov-2018 08:22:25     50     50.1     70      68      48       48    {0×0 char       }    {0×0 char}
    01-Nov-2018 08:23:25     50     50.1     70      70      48       48    {0×0 char       }    {0×0 char}
    01-Nov-2018 08:24:25     50       50     70      70      48     47.9    {0×0 char       }    {0×0 char}
    

Newer versions will read durations as durations, but your Var2 isn't actually a duration, due to the AM/PM. So read it as a datetime (date set to today), strip off tyhe date, and add to the real date.

[Timothy Potter]

I was able to get a series of commands to work in the Command window, but my problem is that the same commands would not work in a live edited script. The biggest problem seemed to be read the data in. All the methods used to read data in gave a formating error or warning. This data is one of four data sets I want to compare, and I was hoping to use the power of Matlab to align all four sets of data on one timescale for comparing what each instrument recorded. I took a week of vacation last week, which is why it took so long to acknowledge your answer.

[Peter Perkins]

I don't know what to say. You seem to be indicating that code that runs fine from the command line doesn't run from the Live Editor. I think you are going to need to show a short clear example fo that.