MATLAB Answers

Import datetime in custom date format which includes junk characters

23 views (last 30 days)
KAE
KAE on 2 Sep 2020
Answered: Steven Lord on 2 Sep 2020
I would like to import a csv file that contains a column of times in the following format,
2020-09-02T16:31:44.427Z
I am using the File Import Wizard, which can apply a custom date format to strings and read them in as datetime values. However there does not seem to be an option to ignore non-date characters like the T and Z in the string above. Is this possible to do, using the pulldown menu? If not I will read it in as text and extract the date/time components.

Accepted Answer

Steven Lord
Steven Lord on 2 Sep 2020
Let's make a sample timetable.
MeasurementTime = datetime({'2015-12-18 08:03:05'; ...
'2015-12-18 10:03:17';...
'2015-12-18 12:03:13'}, ...
'Format', "yyyy-MM-dd 'at' hh:mm:ss");
Temp = [37.3;39.1;42.3];
Pressure = [30.1;30.03;29.9];
WindSpeed = [13.4;6.5;7.3];
WindDirection = categorical({'NW';'N';'NW'});
TT = timetable(MeasurementTime,Temp,Pressure,WindSpeed,WindDirection);
Write it to a file.
writetimetable(TT, fullfile(tempdir, 'mydata.txt'))
Now try to import the datetime variable from that file.
  1. Open the file in the Import Tool.
  2. Change the delimiter to "comma" and the MeasurementTime variable should contain just the representation of the date and time.
  3. Click the dropdown showing "text" below the MeasurementTime identifier.
  4. Click on the "more date formats" and scroll to the bottom of the list where you can enter a custom format.
  5. Enter the option I specified as the Format when I created the datetime MeasurementTime above (without the double quotes but with the single quotes.) This uses the capability to "escape" literal character [a-zA-Z] in a format that is described in the description of the Format property on the documentation page for datetime.
  6. Import that variable and you should see it contains the appropriate date and time with the word 'at' between the date and time.

More Answers (0)

Tags

Community Treasure Hunt

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

Start Hunting!