Convert a single column in cell array to datetime format

I have a cell array which I imported with textscan and the first column is a date/time stamp. I want to convert this date/time stamp to a true datetime.

The cell array (data_temp) looks like this:

"2017-01-15 13:50:46.500" "OPC.Temperature.BottomTemperature" "23"

"2017-01-15 13:50:50.203" "OPC.Temperature.BottomTemperature" "24"

"2017-01-15 13:52:06.937" "OPC.Temperature.BottomTemperature" "22"

"2017-01-15 13:52:22.578" "OPC.Temperature.BottomTemperature" "24"

I have tried:

 % data_temp = datetime(data_temp(:,1),'InputFormat','yyyy-MM-dd HH:mm:ss.SSS');

This converts the whole cell array to datetime and deletes the 2nd and 3rd columns. Using

data_temp{:,1}

gives me these errors:

Error using datetime (line 510)
Invalid parameter name: 2017-01-15 13:50:50.203.

Error in parser (line 39)
data_temp = datetime(data_temp{:,1},'InputFormat','yyyy-MM-dd HH:mm:ss.SSS');

I am not sure what I am doing wrong. I am doing this so I can export the data to a sql database for archiving of a manufacturing process.

Edit:

This my full code so far

%parses .plg file from Log file
fileID = fopen('test.txt');
scan = textscan(fileID,'%s %s %s %s %s %s %s %s %s %s','collectoutput', true,'Delimiter','|');
fclose(fileID);

% combines textscan cell arrays to one cell array 
scan = scan{:};

%determines number of paramater rows and counts all rows in textscan cell
%array
num_nonblank = sum(~strcmp(scan(:,10),''));
num_nonblank_plusone = num_nonblank + 1;
row_count = size(scan,1);

%makes 2 copies of the textscan cell array and convert to strings
parameters = scan;
parameters = string(parameters);
data = scan;
data = string(data);

%deletes extra columns and parameter rows from data cell array
data(:,(6:10)) = [];
data((1:num_nonblank),:) = [];

%deletes data rows from parameter cell array
parameters((num_nonblank_plusone:row_count),:) = [];

%create cell arrays for individual data types
data_temp = data;

%remove all rows not containing 'OPC.Temperature.BottomTemperature' in
%column 2
temp_string = 'OPC.Temperature.BottomTemperature';
temp_comp = data_temp(:,2) ~= temp_string;
data_temp(temp_comp,:) = [];

%remove extra information from columns 3 & 4 from data_temp
data_temp(:,(2:4)) = [];

[per isakson]

Neither am I. This works here on R2016a

>> dt=datetime( '2017-01-15 13:50:50.203','InputFormat','yyyy-MM-dd HH:mm:ss.SSS')
dt = 
   2017-01-15 13:50:50

Comments:

• Strange that you get an error for the second row, rather than the first
• Have you looked for non-printing characters?

Answer 1

[Star Strider]

I would create a table from ‘data_temp’, then convert the first variable to a datetime array:

data_temp = {"2017-01-15 13:50:46.500" "OPC.Temperature.BottomTemperature" "23"
"2017-01-15 13:50:50.203" "OPC.Temperature.BottomTemperature" "24"
"2017-01-15 13:52:06.937" "OPC.Temperature.BottomTemperature" "22"
"2017-01-15 13:52:22.578" "OPC.Temperature.BottomTemperature" "24"};

data_table = cell2table(data_temp);

data_table.data_temp1 = datetime(data_table.data_temp1,'InputFormat','yyyy-MM-dd HH:mm:ss.SSS');

[jcledfo2]

I am getting the error

Error using cell2table (line 24)
C must be a 2-D cell array.

FYI data_temp is listed with a value of 1331x3 string

This is the code I used:

%convert data_temp cell array to table
data_table = cell2table(data_temp,'VariableNames',{'TimeStamp' 'OPCName' 'Temperature(C)'});

%convert timestamp string to datetime
data_table.data_temp1 = datetime(data_table.data_temp1,'InputFormat','yyyy-MM-dd HH:mm:ss.SSS');

[Star Strider]

First, ‘data_temp’ should be a (1331x3) cell variable, not a string. You said it was a cell array. Creating it as a cell array is as simple as putting curly braces around it, as I did.

Second, you have to use the variable names you assigned when you created the table.

Using the (4x3) cell array I created using your posted data in my original Answer, these work for me in R2017b:

data_table = cell2table(data_temp, 'VariableNames',{'TimeStamp' 'OPCName' 'Temperature_C'});

data_table.TimeStamp = datetime(data_table.TimeStamp,'InputFormat','yyyy-MM-dd HH:mm:ss.SSS');

I cannot reproduce the error you are getting.

[jcledfo2]

I am attaching my entire code below. Essentially I have an enormous data file which is split into parameters and data. Parameters has 10 columns and data only has 5 columns. I split them into 2 different arrays. Then I search for the temperature and then remove the excess rows and then remove the excess info in the columns so I am left with a time stamp and temperature.

I have no idea if I have done this correctly.

%parses .plg file from Log file
fileID = fopen('test.txt');
scan = textscan(fileID,'%s %s %s %s %s %s %s %s %s %s','collectoutput', true,'Delimiter','|');
fclose(fileID);

% combines textscan cell arrays to one cell array 
scan = scan{:};

%determines number of paramater rows and counts all rows in textscan cell
%array
num_nonblank = sum(~strcmp(scan(:,10),''));
num_nonblank_plusone = num_nonblank + 1;
row_count = size(scan,1);

%makes 2 copies of the textscan cell array and convert to strings
parameters = scan;
parameters = string(parameters);
data = scan;
data = string(data);

%deletes extra columns and parameter rows from data cell array
data(:,(6:10)) = [];
data((1:num_nonblank),:) = [];

%deletes data rows from parameter cell array
parameters((num_nonblank_plusone:row_count),:) = [];

%create cell arrays for individual data types
data_temp = data;

%remove all rows not containing 'OPC.Temperature.BottomTemperature' in
%column 2
temp_string = 'OPC.Temperature.BottomTemperature';
temp_comp = data_temp(:,2) ~= temp_string;
data_temp(temp_comp,:) = [];

%remove extra information from columns 3 & 4 from data_temp
data_temp(:,(2:4)) = [];

[jcledfo2]

Now my final array looks like this:

"2017-01-15 13:50:46.500"  "23"
"2017-01-15 13:50:50.203"  "24"
"2017-01-15 13:52:06.937"  "22"
"2017-01-15 13:52:22.578"  "24"
"2017-01-15 13:54:59.046"  "22"
"2017-01-15 13:55:17.578"  "24"

[jcledfo2]

Here are my workspace values

[Star Strider]

The only problem I can see is this assignment:

data = string(data);

I would eliminate that. As I see it, the rest of your code should work with ‘data’ as a cell array. (I obviously have not run your code, however that is my impression.) The cell2table call should then work.

[jcledfo2]

The problem I run into by removing that is it doesn't like this line:

temp_comp = data_temp(:,2) ~= temp_string;

Says that ~= is an undefined operator for type 'cell'

[jcledfo2]

I figured it out. I needed to use strcmp and just invert the logic to tell me where the string was not located.

temp_string = 'OPC.Temperature.BottomTemperature';
temp_comp = strcmp(data_temp(:,2),temp_string);
temp_comp = ~temp_comp;
data_temp(temp_comp,:) = [];

Then I used your datetime code from earlier and it worked!!

Thanks

[Star Strider]

Try this:

data_temp = {'2017-01-15 13:50:46.500' 'OPC.Temperature.BottomTemperature' '23'
'2017-01-15 13:50:50.203' 'OPC.Temperature.BottomTemperature' '24'
'2017-01-15 13:52:06.937' 'OPC.Temperature.BottomTemperature' '22'
'2017-01-15 13:52:22.578' 'OPC.Temperature.BottomTemperature' '24'};

temp_string = 'OPC.Temperature.BottomTemperature';
temp_comp = ~cellfun(@strcmp, data_temp(:,2), repmat({temp_string},size(data_temp,1),1));

It runs, and appears to produce the correct result. I tested it on the cell array I created from the data you posted, that I assume to be similar to the original cell array (before the string call).

[Douglas Anderson]

Wow! Amazing! Cells are always a mystery to me. Thank you!!!