The date format from my readtable is wrong so I get "NaT"

I really can't believe this was never asked after I googled for hours.

I have a REALLY long csv file with about 1million rows with dates. The format they were written is 'dd/MM/yyyy hh:mm:ss'.

When I try to import those values I keep getting "NaT" on dates like the 31st of october. It's obvious that MATLAB is trying to read on format 'MM/dd/yyyy' instead of 'dd/MM/yyyy'.

I've seen another posts about people that change formats after they imported the data so it would be really easy, but it's not the case.

I've changed the date format on "Preferences->Command Window' but I keep getting frustrated with 'NaT' on half of the data.

I've thought about two options:

• changing the format of readtable (which I tried that already doing something like a=readtable(file,'Range','e5:e931478','DatetimeType','dd/mm/yyyy hh:mm'); but it says

"Error using readtable (line 318)

Expected input to match one of these values:

'datetime', 'text', 'exceldatenum'

The input, 'dd/mm/yyyy hh:mm', did not match any of the valid values."

• The other thing I could try is to import the dates as another format like texst char or strings and THEN try to read them.

I'm really desperate here can anyone help ?

a=readtable(file,'Range','e5:e931478','DatetimeType','dd/mm/yyyy hh:mm');

[dpb]

It always helps to have some of the input file to see for sure what it looks like, but the key thing to try is

opt=detectImportOptions(file);

then set the 'InputFormat' for the variable that is the datetime type to

opt=setvaropts(var,'InputFormat','dd/MM/yyyy hh:mm');

and then call readtable with the import options object, opt

NB: in your call above, the 'DatetimeType' isn't valid; you tried to set the input format there instead of whether you wanted a datetime, datenum, etc., data type for the variable (and besides using lower case 'm' for both months and minutes).

tData=readtable(file,opt);

[Qiurui Zhu]

But I have not import the data yet. How do I let it recognize the var name in the "setvaropts(var,'InputFormat','dd/MM/yyyy hh:mm')" command. In my case, the error is "Unrecognized function or variable 'TIMESTAMP'."

[Walter Roberson]

opt = detectImportOptions(file);
opt = setvaropt(opt, 'TIMESTAMP', 'InputFormat', 'dd/MM/yyyy hh:mm');
tData = readtable(file, opt);