readtable with multiple date formats
36 views (last 30 days)
Show older comments
Hi,
I have to read and concatenate multiple tables which all have the same number of variables. One of these variables is a datetime value which for each table comes in a different format ('dd:MM:yy hh:mm','d/MM/yyyy hh:mm', etc.) All of the formats have in common that the day precedes the month (non-US system).
The problem I have is that if I don't specify the datetime format, matlab defaults to US format in the tables that would accept it; if I specify the format, the datetime entry in the tables which don't match that format read NaT. The problem would be solved if I didn't have to specify the datetime format but could specify the default format to be in non-US style. A nice alternative would be to provide more than one datetime format and let the program pic the one that fits.
I would very much appreciate your help.
Accepted Answer
Jeremy Hughes
on 6 Aug 2020
Without seeing the file, I can only guess, but I'd try the following first:
T = readtable(filename,'DateLocale',locale,...)
Not all of the formats will work in every locale though, so you might want to just import the dates as text,
T = readtable(filename,'DatetimeType','text',...)
Then you can deal with the different formats from in MATLAB, and have the benefit of seeing the data first. I'll open an enhancement request for this.
Hope this helps.
0 Comments
More Answers (1)
Peng Li
on 5 Aug 2020
A workaround for me I think is to just load that column in string format. After loading you can do some cleaning before you convert it back to datetime. For example, you can replace : with / first to consolidate these strings.
0 Comments
See Also
Categories
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!