readtable defaulting to wrong date format

I am reading data from a CSV file that contains a combination of date, numeric, and text fields. I then convert the table data to an array for calculations. The problem is the readtable is assuming the date format MM/dd/uuuu when it is recorded in the CSV file as dd/MM/uuuu.

I have attempted to convert it to a string and back but the conversion to a string is reading the date in American format and then outputing the wrong date in rest-of-the-world format. eg. 01/07/2017 in the CSV is converted to a string 07-JAN-2017 which is then converted to 07/01/2017.

Data = readtable(CSVFilePath)
TimeDate = table2array(Data(:,1))

I have also tried

TimeDate  = datetime(TimeDate ,'InputFormat', 'dd/MM/uuuu')

but that also interprets a change in month as a change in day.

[Andrei Bobrov]

Please attaching your data: CSV - file

[Andrew Hart]

Here you go. Thanks.

Answer 1

[Steven Lord]

Look at the "Read Foreign-Language Dates from Text File" example in the documentation for the readtable function. When you don't tell readtable the format of your data, it tries to determine the format on its own. If it doesn't quite get it right, you can use the 'Format' option to tell it exactly how to read the data.

That particular example uses the "%{fmt}D" specifier as shown in the "Nonnumeric fields" section of the documentation of the formatSpec input argument to the textscan function.

[Andrew Hart]

Thanks for your time! Very helpful.