Dates and Times Refuse to Concatenate Into One Column

1 view (last 30 days)
Gregory Lang
Gregory Lang on 17 Feb 2023
Commented: Cris LaPierre on 17 Feb 2023
Hello, I have a csv file with dates and times in the following format:
dd-mm-yyyy hh-mm-ss-SSS
20-09-2022 22:16:39.163
I've tried date string and datenum, and have had a friend try as well. No matter what we try, the dates and times refuse to combine into one large date - time value. Any help would be greatly appreciated
Additional Data:
running MATLAB version R2022b - academic use
Example CSV is attached
  3 Comments
Show 1 older comment
Les Beckham
Les Beckham on 17 Feb 2023
What version of Matlab are you using? Please provide a sample csv file -- edit your question and attach it using the paperclip icon.
Gregory Lang
Gregory Lang on 17 Feb 2023
Hello, as requested I have attached a small csv file with the example data. The data was imported into MATLAB using the import tool, resulting in a vector 844,740 x 75. Looking at the data there do not seem to be any issues with the imported data after having imported it.
During importing I needed to change the date format to dd-mm-yyyy and the time format to hh-mm-ss-SSS

Sign in to comment.

Sign in to answer this question.

Accepted Answer

Walter Roberson
Walter Roberson on 17 Feb 2023
Ran in:
filename = 'https://www.mathworks.com/matlabcentral/answers/uploaded_files/1299205/example%20csv%20data.txt';
datecols = [1 9 14 19 27 32 37 45 50 55 63 68];
opts = detectImportOptions(filename, 'VariableNamingRule', 'preserve', 'HeaderLines', 1);
opts = setvartype(opts, datecols, 'datetime');
opts = setvaropts(opts, datecols, 'InputFormat', 'dd-MM-uuuu', 'DateTimeFormat', 'dd-MM-uuuu HH:mm:ss.sss');
T = readtable(filename, opts);
T{:,datecols} = T{:,datecols} + T{:,datecols+1};
T = removevars(T, datecols+1); %time merged into date
T(1:5,:)
ans = 5×63 table
Date [A Ch.1 Main] dt (s) [A Ch.1 Main] Oxygen (mg/L) [A Ch.1 Main] dphi (°) [A Ch.1 Main] Signal Intensity (mV) [A Ch.1 Main] Ambient Light (mV) [A Ch.1 Main] Status [A Ch.1 Main] Date [A Ch.1 CompT] dt (s) [A Ch.1 CompT] Fixed Temp (°C) [A Ch.1 CompT] Status [A Ch.1 CompT] Date [A Ch.1 CompP] dt (s) [A Ch.1 CompP] Pressure (mbar) [A Ch.1 CompP] Status [A Ch.1 CompP] Date [B Ch.1 Main] dt (s) [B Ch.1 Main] Oxygen (mg/L) [B Ch.1 Main] dphi (°) [B Ch.1 Main] Signal Intensity (mV) [B Ch.1 Main] Ambient Light (mV) [B Ch.1 Main] Status [B Ch.1 Main] Date [B Ch.1 CompT] dt (s) [B Ch.1 CompT] Fixed Temp (°C) [B Ch.1 CompT] Status [B Ch.1 CompT] Date [B Ch.1 CompP] dt (s) [B Ch.1 CompP] Pressure (mbar) [B Ch.1 CompP] Status [B Ch.1 CompP] Date [C Ch.1 Main] dt (s) [C Ch.1 Main] Oxygen (mg/L) [C Ch.1 Main] dphi (°) [C Ch.1 Main] Signal Intensity (mV) [C Ch.1 Main] Ambient Light (mV) [C Ch.1 Main] Status [C Ch.1 Main] Date [C Ch.1 CompT] dt (s) [C Ch.1 CompT] Fixed Temp (°C) [C Ch.1 CompT] Status [C Ch.1 CompT] Date [C Ch.1 CompP] dt (s) [C Ch.1 CompP] Pressure (mbar) [C Ch.1 CompP] Status [C Ch.1 CompP] Date [D Ch.1 Main] dt (s) [D Ch.1 Main] Oxygen (mg/L) [D Ch.1 Main] dphi (°) [D Ch.1 Main] Signal Intensity (mV) [D Ch.1 Main] Ambient Light (mV) [D Ch.1 Main] Status [D Ch.1 Main] Date [D Ch.1 CompT] dt (s) [D Ch.1 CompT] Fixed Temp (°C) [D Ch.1 CompT] Status [D Ch.1 CompT] Date [D Ch.1 CompP] dt (s) [D Ch.1 CompP] Pressure (mbar) [D Ch.1 CompP] Status [D Ch.1 CompP] Date [Comment] Time [Comment] Comment _______________________ ____________________ ___________________________ ______________________ ___________________________________ ________________________________ ____________________ _______________________ _____________________ ______________________________ _____________________ _______________________ _____________________ ______________________________ _____________________ _______________________ ____________________ ___________________________ ______________________ ___________________________________ ________________________________ ____________________ _______________________ _____________________ ______________________________ _____________________ _______________________ _____________________ ______________________________ _____________________ _______________________ ____________________ ___________________________ ______________________ ___________________________________ ________________________________ ____________________ _______________________ _____________________ ______________________________ _____________________ _______________________ _____________________ ______________________________ _____________________ _______________________ ____________________ ___________________________ ______________________ ___________________________________ ________________________________ ____________________ _______________________ _____________________ ______________________________ _____________________ _______________________ _____________________ ______________________________ _____________________ ______________ ______________ __________ 20-09-2022 22:16:39.039 9.17 7.203 24.892 141 3 {'OK'} 20-09-2022 22:16:39.039 9.17 12 {'OK'} 20-09-2022 22:16:39.039 9.17 1014 {'OK' } 20-09-2022 22:16:31.031 1.323 8.943 24.74 128 2 {'OK' } 20-09-2022 22:16:31.031 1.323 12 {'OK' } 20-09-2022 22:16:31.031 1.323 1014 {'OK' } 20-09-2022 22:16:30.030 0.299 7.409 24.62 154 4 {'OK' } 20-09-2022 22:16:30.030 0.299 12 {'OK' } 20-09-2022 22:16:30.030 0.299 1013 {'OK' } 20-09-2022 22:16:35.035 5.319 7.822 25.055 121 2 {'OK' } 20-09-2022 22:16:35.035 5.319 12 {'OK' } 20-09-2022 22:16:35.035 5.319 1014 {'OK' } {0×0 char} {0×0 char} {0×0 char} 20-09-2022 22:16:49.049 19.171 7.206 24.887 141 2 {'OK'} 20-09-2022 22:16:49.049 19.171 12 {'OK'} 20-09-2022 22:16:49.049 19.171 1014 {'OK' } 20-09-2022 22:16:41.041 11.325 8.938 24.747 128 2 {'OK' } 20-09-2022 22:16:41.041 11.325 12 {'OK' } 20-09-2022 22:16:41.041 11.325 1014 {'OK' } 20-09-2022 22:16:31.031 1.299 7.407 24.623 154 2 {'OK' } 20-09-2022 22:16:31.031 1.299 12 {'OK' } 20-09-2022 22:16:31.031 1.299 1013 {'OK' } 20-09-2022 22:16:45.045 15.317 7.816 25.063 121 2 {'OK' } 20-09-2022 22:16:45.045 15.317 12 {'OK' } 20-09-2022 22:16:45.045 15.317 1014 {'OK' } {0×0 char} {0×0 char} {0×0 char} 20-09-2022 22:16:59.059 29.172 7.211 24.879 141 2 {'OK'} 20-09-2022 22:16:59.059 29.172 12 {'OK'} 20-09-2022 22:16:59.059 29.172 1014 {'OK' } 20-09-2022 22:16:51.051 21.324 8.942 24.742 128 0 {'OK' } 20-09-2022 22:16:51.051 21.324 12 {'OK' } 20-09-2022 22:16:51.051 21.324 1014 {'OK' } 20-09-2022 22:16:32.032 2.301 7.418 24.605 154 1 {'OK' } 20-09-2022 22:16:32.032 2.301 12 {'OK' } 20-09-2022 22:16:32.032 2.301 1013 {'OK' } 20-09-2022 22:16:55.055 25.313 7.825 25.05 121 2 {'OK' } 20-09-2022 22:16:55.055 25.313 12 {'OK' } 20-09-2022 22:16:55.055 25.313 1014 {'OK' } {0×0 char} {0×0 char} {0×0 char} 20-09-2022 22:17:09.009 39.171 7.196 24.904 141 0 {'OK'} 20-09-2022 22:17:09.009 39.171 12 {'OK'} 20-09-2022 22:17:09.009 39.171 1014 {'OK' } 20-09-2022 22:17:01.001 31.323 8.94 24.744 127 1 {'OK' } 20-09-2022 22:17:01.001 31.323 12 {'OK' } 20-09-2022 22:17:01.001 31.323 1014 {'OK' } 20-09-2022 22:16:33.033 3.303 7.419 24.604 154 2 {'OK' } 20-09-2022 22:16:33.033 3.303 12 {'OK' } 20-09-2022 22:16:33.033 3.303 1013 {'OK' } 20-09-2022 22:17:05.005 35.313 7.824 25.051 121 0 {'OK' } 20-09-2022 22:17:05.005 35.313 12 {'OK' } 20-09-2022 22:17:05.005 35.313 1015 {'OK' } {0×0 char} {0×0 char} {0×0 char} 20-09-2022 22:17:19.019 49.171 7.205 24.889 141 0 {'OK'} 20-09-2022 22:17:19.019 49.171 12 {'OK'} 20-09-2022 22:17:19.019 49.171 NaN {0×0 char} NaT NaN NaN NaN NaN NaN {0×0 char} NaT NaN NaN {0×0 char} NaT NaN NaN {0×0 char} NaT NaN NaN NaN NaN NaN {0×0 char} NaT NaN NaN {0×0 char} NaT NaN NaN {0×0 char} NaT NaN NaN NaN NaN NaN {0×0 char} NaT NaN NaN {0×0 char} NaT NaN NaN {0×0 char} {0×0 char} {0×0 char} {0×0 char}

More Answers (1)

Cris LaPierre
Cris LaPierre on 17 Feb 2023
Ran in:
I found that using the import tool did not correctly autodetect the Date and Time fomats correctly.
The approach I would use is to read in the date as a datetime, and the time as a duration, and then add the two together. I don't see duration as a datetype option in the import tool, so I would opt to use readtable and set the import options manually.
opts = detectImportOptions("example csv data.txt");
% Capture the variable names from the file
opts.VariableNamesLine = 2;
% Identify columns with Dates and Times
dCols = [1,9,14,19,27,32,37,45,50,55,63,68];
tCols = [2,10,15,20,28,33,38,46,51,56,64,69];
% Set date import and display options
opts = setvartype(opts,dCols,'datetime');
opts = setvaropts(opts,dCols,"InputFormat","dd-MM-yyyy","DatetimeFormat","dd/MM/yyyy HH:mm:ss.SSS");
% Set time import optoins
opts = setvartype(opts,tCols,'duration');
opts = setvaropts(opts,tCols,'InputFormat',"hh:mm:ss.SSS");
% Import table
F = readtable("example csv data.txt",opts);
Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.
% Combine date and time data
F{:,dCols} = F{:,dCols}+F{:,tCols};
% Delete time data, as it is redundant
F(:,tCols) = [];
% Change Date variable names to now say 'Datetime'
F.Properties.VariableNames = replace(F.Properties.VariableNames,"Date","Datetime")
F = 5×63 table
Datetime_ACh_1Main_ dt_s__ACh_1Main_ Oxygen_mg_L__ACh_1Main_ dphi____ACh_1Main_ SignalIntensity_mV__ACh_1Main_ AmbientLight_mV__ACh_1Main_ Status_ACh_1Main_ Datetime_ACh_1CompT_ dt_s__ACh_1CompT_ FixedTemp__C__ACh_1CompT_ Status_ACh_1CompT_ Datetime_ACh_1CompP_ dt_s__ACh_1CompP_ Pressure_mbar__ACh_1CompP_ Status_ACh_1CompP_ Datetime_BCh_1Main_ dt_s__BCh_1Main_ Oxygen_mg_L__BCh_1Main_ dphi____BCh_1Main_ SignalIntensity_mV__BCh_1Main_ AmbientLight_mV__BCh_1Main_ Status_BCh_1Main_ Datetime_BCh_1CompT_ dt_s__BCh_1CompT_ FixedTemp__C__BCh_1CompT_ Status_BCh_1CompT_ Datetime_BCh_1CompP_ dt_s__BCh_1CompP_ Pressure_mbar__BCh_1CompP_ Status_BCh_1CompP_ Datetime_CCh_1Main_ dt_s__CCh_1Main_ Oxygen_mg_L__CCh_1Main_ dphi____CCh_1Main_ SignalIntensity_mV__CCh_1Main_ AmbientLight_mV__CCh_1Main_ Status_CCh_1Main_ Datetime_CCh_1CompT_ dt_s__CCh_1CompT_ FixedTemp__C__CCh_1CompT_ Status_CCh_1CompT_ Datetime_CCh_1CompP_ dt_s__CCh_1CompP_ Pressure_mbar__CCh_1CompP_ Status_CCh_1CompP_ Datetime_DCh_1Main_ dt_s__DCh_1Main_ Oxygen_mg_L__DCh_1Main_ dphi____DCh_1Main_ SignalIntensity_mV__DCh_1Main_ AmbientLight_mV__DCh_1Main_ Status_DCh_1Main_ Datetime_DCh_1CompT_ dt_s__DCh_1CompT_ FixedTemp__C__DCh_1CompT_ Status_DCh_1CompT_ Datetime_DCh_1CompP_ dt_s__DCh_1CompP_ Pressure_mbar__DCh_1CompP_ Status_DCh_1CompP_ Datetime_Comment_ Time_Comment_ Comment _______________________ ________________ _______________________ __________________ ______________________________ ___________________________ _________________ _______________________ _________________ _________________________ __________________ _______________________ _________________ __________________________ __________________ _______________________ ________________ _______________________ __________________ ______________________________ ___________________________ _________________ _______________________ _________________ _________________________ __________________ _______________________ _________________ __________________________ __________________ _______________________ ________________ _______________________ __________________ ______________________________ ___________________________ _________________ _______________________ _________________ _________________________ __________________ _______________________ _________________ __________________________ __________________ _______________________ ________________ _______________________ __________________ ______________________________ ___________________________ _________________ _______________________ _________________ _________________________ __________________ _______________________ _________________ __________________________ __________________ _________________ _____________ __________ 20/09/2022 22:16:39.163 9.17 7.203 24.892 141 3 {'OK'} 20/09/2022 22:16:39.163 9.17 12 {'OK'} 20/09/2022 22:16:39.163 9.17 1014 {'OK' } 20/09/2022 22:16:31.316 1.323 8.943 24.74 128 2 {'OK' } 20/09/2022 22:16:31.316 1.323 12 {'OK' } 20/09/2022 22:16:31.316 1.323 1014 {'OK' } 20/09/2022 22:16:30.291 0.299 7.409 24.62 154 4 {'OK' } 20/09/2022 22:16:30.291 0.299 12 {'OK' } 20/09/2022 22:16:30.291 0.299 1013 {'OK' } 20/09/2022 22:16:35.312 5.319 7.822 25.055 121 2 {'OK' } 20/09/2022 22:16:35.312 5.319 12 {'OK' } 20/09/2022 22:16:35.312 5.319 1014 {'OK' } {0×0 char} {0×0 char} {0×0 char} 20/09/2022 22:16:49.164 19.171 7.206 24.887 141 2 {'OK'} 20/09/2022 22:16:49.164 19.171 12 {'OK'} 20/09/2022 22:16:49.164 19.171 1014 {'OK' } 20/09/2022 22:16:41.317 11.325 8.938 24.747 128 2 {'OK' } 20/09/2022 22:16:41.317 11.325 12 {'OK' } 20/09/2022 22:16:41.317 11.325 1014 {'OK' } 20/09/2022 22:16:31.292 1.299 7.407 24.623 154 2 {'OK' } 20/09/2022 22:16:31.292 1.299 12 {'OK' } 20/09/2022 22:16:31.292 1.299 1013 {'OK' } 20/09/2022 22:16:45.310 15.317 7.816 25.063 121 2 {'OK' } 20/09/2022 22:16:45.310 15.317 12 {'OK' } 20/09/2022 22:16:45.310 15.317 1014 {'OK' } {0×0 char} {0×0 char} {0×0 char} 20/09/2022 22:16:59.164 29.172 7.211 24.879 141 2 {'OK'} 20/09/2022 22:16:59.164 29.172 12 {'OK'} 20/09/2022 22:16:59.164 29.172 1014 {'OK' } 20/09/2022 22:16:51.317 21.324 8.942 24.742 128 0 {'OK' } 20/09/2022 22:16:51.317 21.324 12 {'OK' } 20/09/2022 22:16:51.317 21.324 1014 {'OK' } 20/09/2022 22:16:32.293 2.301 7.418 24.605 154 1 {'OK' } 20/09/2022 22:16:32.293 2.301 12 {'OK' } 20/09/2022 22:16:32.293 2.301 1013 {'OK' } 20/09/2022 22:16:55.305 25.313 7.825 25.05 121 2 {'OK' } 20/09/2022 22:16:55.305 25.313 12 {'OK' } 20/09/2022 22:16:55.305 25.313 1014 {'OK' } {0×0 char} {0×0 char} {0×0 char} 20/09/2022 22:17:09.163 39.171 7.196 24.904 141 0 {'OK'} 20/09/2022 22:17:09.163 39.171 12 {'OK'} 20/09/2022 22:17:09.163 39.171 1014 {'OK' } 20/09/2022 22:17:01.316 31.323 8.94 24.744 127 1 {'OK' } 20/09/2022 22:17:01.316 31.323 12 {'OK' } 20/09/2022 22:17:01.316 31.323 1014 {'OK' } 20/09/2022 22:16:33.295 3.303 7.419 24.604 154 2 {'OK' } 20/09/2022 22:16:33.295 3.303 12 {'OK' } 20/09/2022 22:16:33.295 3.303 1013 {'OK' } 20/09/2022 22:17:05.305 35.313 7.824 25.051 121 0 {'OK' } 20/09/2022 22:17:05.305 35.313 12 {'OK' } 20/09/2022 22:17:05.305 35.313 1015 {'OK' } {0×0 char} {0×0 char} {0×0 char} 20/09/2022 22:17:19.164 49.171 7.205 24.889 141 0 {'OK'} 20/09/2022 22:17:19.164 49.171 12 {'OK'} 20/09/2022 22:17:19.164 49.171 NaN {0×0 char} NaT NaN NaN NaN NaN NaN {0×0 char} NaT NaN NaN {0×0 char} NaT NaN NaN {0×0 char} NaT NaN NaN NaN NaN NaN {0×0 char} NaT NaN NaN {0×0 char} NaT NaN NaN {0×0 char} NaT NaN NaN NaN NaN NaN {0×0 char} NaT NaN NaN {0×0 char} NaT NaN NaN {0×0 char} {0×0 char} {0×0 char} {0×0 char}
Just a caution that this is quick and dirty. I have not extensively checked the results to ensure they are correct.
  1 Comment
Cris LaPierre
Cris LaPierre on 17 Feb 2023
Nice to see Walter and I have similar solutions :) This is largely redundant, but I'll leave it for now.

Sign in to comment.

Categories

Find more on Calendar in Help Center and File Exchange

Tags

Community Treasure Hunt

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

Start Hunting!