Converting text to datetime stamp in Data Table

6 views (last 30 days)
I am using the import tool to import a CSV file and am able to import it no problem. I need to manipulate the first column of data to a date and time and it won't let me do this automatically because the generated timestamp from my test equipment is different than the formats that matlab automatically detects. Thank you for any help you can give me!
The timestamps in the dataset follow this formatting:
1 (4/5/2022 12:04:05.7)
2 (4/5/2022 12:04:05.8)
3 (4/5/2022 12:04:05.9)
4 (4/5/2022 12:04:06.0)
5 (4/5/2022 12:04:06.1)
6 (4/5/2022 12:04:06.2)
I need to convert this to a timestamp so that MATLAB will recognize and I can calculate time elapsed for my testing.
This is the autogenerated matlab script from importing the csv file: (Currently its importing the time column as "char")
%% Import data from text file
% Auto-generated by MATLAB on 07-Apr-2022 13:36:56
%% Setup the Import Options
opts = delimitedTextImportOptions("NumVariables", 25);
% Specify range and delimiter
opts.DataLines = [15, Inf];
opts.Delimiter = ",";
% Specify column names and types
opts.VariableNames = ["Label", "Channel1", "Channel2", "Channel3", "Channel4", "Channel5", "Channel6", "Channel7", "Channel8", "Channel9", "Channel10", "Channel11", "Channel12", "Channel19", "Channel20", "Channel21", "Channel22", "Channel23", "Channel24", "Channel25", "Channel26", "Channel27", "Channel28", "Channel29", "Channel30"];
opts.VariableTypes = ["char", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double"];
opts = setvaropts(opts, 1, "WhitespaceRule", "preserve");
opts = setvaropts(opts, 1, "EmptyFieldRule", "auto");
opts.ExtraColumnsRule = "ignore";
opts.EmptyLineRule = "read";
% Import the data
TMX2DataFile3TCs25through48 = readtable("C:\Users\\Desktop\Q125A Aggregate Testing\Propagation TMX 2 Data File 3 TCs 25 through 48.csv", opts);
%% Clear temporary variables
clear opts

Answers (1)

Dave B
Dave B on 7 Apr 2022
It's a little difficult to understand exactly what's in TMX2DataFile3TCs25through48 from the code, I think the dates you pasted get copied into the table's first column exactly? If not, uploading a mat file with the table in it would be helpful.
How I understand your your starting point (skipping other values in the file):
Label={'1 (4/5/2022 12:04:05.7)';'2 (4/5/2022 12:04:05.8)';'3 (4/5/2022 12:04:05.9)';'4 (4/5/2022 12:04:06.0)';'5 (4/5/2022 12:04:06.1)';'6 (4/5/2022 12:04:06.2)'};
TMX2DataFile3TCs25through48 = table(Label)
TMX2DataFile3TCs25through48 = 6×1 table
Label ___________________________ {'1 (4/5/2022 12:04:05.7)'} {'2 (4/5/2022 12:04:05.8)'} {'3 (4/5/2022 12:04:05.9)'} {'4 (4/5/2022 12:04:06.0)'} {'5 (4/5/2022 12:04:06.1)'} {'6 (4/5/2022 12:04:06.2)'}
% It's easier to work with this if you extract the bits in between the parentheses:
TMX2DataFile3TCs25through48.Label=extractBetween(TMX2DataFile3TCs25through48.Label,'(',')');
% Now specify the format that your dates have been provided
%
% NOTE: I assumed month/day/year, but your example values were somewhat
% ambiguous. You can flip around the MM and dd as you like!
TMX2DataFile3TCs25through48.Date = datetime(TMX2DataFile3TCs25through48.Label,'InputFormat','MM/dd/uuuu HH:mm:ss.s');
TMX2DataFile3TCs25through48
TMX2DataFile3TCs25through48 = 6×2 table
Label Date _______________________ ____________________ {'4/5/2022 12:04:05.7'} 05-Apr-2022 12:04:07 {'4/5/2022 12:04:05.8'} 05-Apr-2022 12:04:08 {'4/5/2022 12:04:05.9'} 05-Apr-2022 12:04:09 {'4/5/2022 12:04:06.0'} 05-Apr-2022 12:04:00 {'4/5/2022 12:04:06.1'} 05-Apr-2022 12:04:01 {'4/5/2022 12:04:06.2'} 05-Apr-2022 12:04:02

Products


Release

R2019a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by