Missing 2000 years on date from csv file.

Hi,

When I read date column from a csv file, Matlab lacks 2000 years on every single date row. For example on the csv file it is 01/01/2018, Matlab reads it 01/01/0018. I use the function 'readtable'. Do you have any explanation to this, or any idea on how to fix this error? It is important for me to store the loaded data in a table format as I use functions like 'timetable' and 'retime' afterwards. Thank you

[madhan ravi]

upload your data

[Mohamed Larabi]

Thank you for your comment. It let me pay more attention to the date format. On the csv file it is 01/01/18 not as I mentioned on my original post. However, I still would like matlab to read it as 01/01/2018 and not 01/01/0018, like Excel does. I have added the data to my post.

Answer 1

[madhan ravi]

Perhaps?

T=readtable('Treasury.csv');
a=strrep(string(T.Date),'0018','2018');
T.Date=a

[madhan ravi]

To keep them as datetime instead of strings then:

T.Date=datetime(a,'Format','Default') % replace the last line with this

[Mohamed Larabi]

Your solution works perfectly. But it will be redundant if I had a date column compounded of different years like from 2002 to 2018 for instance. I will have to run the 'strrep' function for each year.

[madhan ravi]

If it was explicitly stated in the csv file then you wouldn't be facing the issue in the first place see doc.

[Mohamed Larabi]

If I had dates from 2002 until 2018,

I am doing:

Table = readtable('Treasury2002.csv');
for i=2:9
a=strrep(string(Table.Date),['000', num2str(i)],['200', num2str(i)]);
Table.Date=datetime(a,'Format','MM/dd/yyyy');
end
for i=10:18
a=strrep(string(Table.Date),['00', num2str(i)],['20', num2str(i)]);
Table.Date=datetime(a,'Format','MM/dd/yyyy');
end

Let me know if you have a more efficient method. This one works, but I am sure there is better.

[Stephen23]

"Let me know if you have a more efficient method. This one works, but I am sure there is better."

The better and more efficient method is to store the complete date in the CSV file, preferably in an ISO 8601 date format.

[Mohamed Larabi]

Ok, Thank you.

Answer 2

[Corinne Beier]

I was able to fix my 0018 and 0019 datetime issue using this:

opts = detectImportOptions('/path/to/file.csv');
opts = setvaropts(opts,'myDateVariableName','InputFormat','MM/dd/y HH:mm');
data = readtimetable('/path/to/file.csv', opts);

The key for me was changing the date time format to include only 1 'y':

'MM/dd/y HH:mm'

Because I originally tried

'MM/dd/yyyy HH:mm' and 'MM/dd/uuuu HH:mm'

and they both did not work.

Also, I did have some trouble with my variable name. See below.

'myDateVariableName'

is the name of the column that has datetime values. Please note, if Matlab is changing your variable for you, use Matlab's version of the variable name. e.g. my data was actually labeled as "Channel Name:" and Matlab always changed the name to "ChannelName_" For my fix to work I had to use 'ChannelName_' as 'myDateVariableName'

Hope this helps.

Answer 3

[Peter Perkins]

If all you have is a missing "20", the quick and dirty solution is to add calyears(2000) to each datetime in your table. But the right thing to do is to use a format when reading the data, preferably using detectimportoptions.

And of course Stephen is correct: the real solution is to try to get the data in the file to be in a less ambiguous form.