Convert a single column in cell array to datetime format

Question

jcledfo2 2017-12-10

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/372164-convert-a-single-column-in-cell-array-to-datetime-format

评论： Douglas Anderson 2022-4-4

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)) = [];

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

per isakson 2017-12-10

在 MATLAB Online 中打开

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 2017-12-10

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/372164-convert-a-single-column-in-cell-array-to-datetime-format#answer_295557

在 MATLAB Online 中打开

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');

10 个评论
显示 8更早的评论隐藏 8更早的评论

jcledfo2 2017-12-10

在 MATLAB Online 中打开

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)) = [];

Star Strider 2017-12-10

在 MATLAB Online 中打开

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 2022-4-4

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

请先登录，再进行评论。

Convert a single column in cell array to datetime format

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

采纳的回答

10 个评论
显示 8更早的评论隐藏 8更早的评论

更多回答（0 个）

另请参阅

类别

标签

产品

Community Treasure Hunt

Convert a single column in cell array to datetime format

1 个评论 显示 -1更早的评论隐藏 -1更早的评论

采纳的回答

10 个评论 显示 8更早的评论隐藏 8更早的评论

更多回答（0 个）

另请参阅

类别

标签

产品

Community Treasure Hunt

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

10 个评论
显示 8更早的评论隐藏 8更早的评论