Read datetime with format "dd-MMM-uuuu HH:mm:ss.sssssss" from datastore

I have a datastore with csv files that I created with the writetimetable function.

The dates are written in "dd-MMM-uuuu HH:mm:ss.sssssss" format. For example, a sample date is "14-Feb-2023 13:12:58.9699163".

I need to read this datastore keeping the same datetime precision. In creating the datastore, MATLAB identified it as a TabularTextDatastore object, and I saw that the default value for the TextScanFormats property for dates is "%{dd-MMM-uuuu HH:mm:ss.SSS}D".

I tried changing it explicitly to "%{dd-MMM-uuuu HH:mm:ss.SSSSSSSS}D" but when I try to read the data I get the following error message:

Unable to read the DATETIME data with the format "dd-MMM-uuuu HH:mm:ss.sssssss". If the data is not a time, use %q to get text data.

I read this post: How do I use 'textscan' to correctly read a formatted date? and tried reading the data as text using "[%{MM-dd-yyyy HH:mm:ss.SSSSSSS}D%q]" but I got "Each variable name must correspond to a single format specifier."

I've read textscan documentation and the Format property of datetime values but I did not find mention of using any precision higher than microseconds.

I do not know how to proceed. Should I change strategy and parse individually each file? Or is there any way to change the TextScanFormats properties in the datastore to read datetimes with up to nanosecond precision?

Regards

[Stephen23]

It works here:

ft = 'dd-MMM-uuuu HH:mm:ss.SSSSSSS';
dt = datetime('14-Feb-2023 13:12:58.9699163','inputFormat',ft,'Format',ft) + minutes([0;1;5]);
xx = [0;1;5];
tt = timetable(dt,xx)

tt = 3x1 timetable
                 dt                 xx
    ____________________________    __

    14-Feb-2023 13:12:58.9699163    0 
    14-Feb-2023 13:13:58.9699163    1 
    14-Feb-2023 13:17:58.9699163    5 

writetimetable(tt,'test.csv')
type test.csv

dt,xx
14-Feb-2023 13:12:58.9699163,0
14-Feb-2023 13:13:58.9699163,1
14-Feb-2023 13:17:58.9699163,5

ds = datastore('test.csv');
t = read(ds)

t = 3x2 table
               dt               xx
    ________________________    __

    14-Feb-2023 13:12:58.969    0 
    14-Feb-2023 13:13:58.969    1 
    14-Feb-2023 13:17:58.969    5 

t.dt.Format = ft

t = 3x2 table
                 dt                 xx
    ____________________________    __

    14-Feb-2023 13:12:58.9699163    0 
    14-Feb-2023 13:13:58.9699163    1 
    14-Feb-2023 13:17:58.9699163    5 

[Siddharth Bhutiya]

As Stephen23 showed above, it should just work. Looking at the error message text that you pasted above, it might just be a typo while setting the format in your code. The fractional seconds need to be upper case S whereas the error message is showing lower case s's. Can you try changing the fractional seconds to be all upper case S and see if that works ?

Unable to read the DATETIME data with the format "dd-MMM-uuuu HH:mm:ss.sssssss". If the data is not a time, use %q to get text data.

Answer 1

[Shivam]

hi @Pedro,

To handle datetime values with nanosecond precision in a TabularTextDatastore, you need to read the datetime column as text and then convert it to a datetime format.

Here is the sample workaround you can follow:

% Create the datastore
ds = tabularTextDatastore('your_folder_path', 'FileExtensions', '.csv');

datetimeColumnIndex = 1; % You can addjust this to the correct index of your datetime column

% Set the TextScanFormats to read the datetime column as text
formats = ds.TextScanFormats;
formats{datetimeColumnIndex} = '%q'; % Read the datetime as text
ds.TextScanFormats = formats;

% Read the data
data = readall(ds);

% Convert the datetime text to datetime with nanosecond precision
datetimeFormat = 'dd-MMM-uuuu HH:mm:ss.SSSSSSS';
data.DateTime = datetime(data{:, datetimeColumnIndex}, 'InputFormat', datetimeFormat);

% Display the result
disp(data);

Hope it helps.