readtable with multiple date formats

조회 수: 15 (최근 30일)
Ramon Sabin
Ramon Sabin 2020년 8월 5일
답변: Jeremy Hughes 2020년 8월 6일
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.
  댓글 수: 2
dpb
dpb 2020년 8월 5일
For right now the "one size fits all" limitation for a given file is there -- only workaround I know would be that hopefully you have some way to identify which files are in which format and to have a lookup table that uses that information to set the correct format for the file programmatically.
You can try calling the |detectImportOptions function before trying to read the file -- it does some more in-depth probing of the file than does readtable on its own -- it may be able to divine the correct format automagically. If that works, then just add that step before reading and pass the specific import object for the file.
Ramon Sabin
Ramon Sabin 2020년 8월 5일
Thank you. I will try the function and see if I can work something out.

댓글을 달려면 로그인하십시오.

채택된 답변

Jeremy Hughes
Jeremy Hughes 2020년 8월 6일
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.

추가 답변 (1개)

Peng Li
Peng Li 2020년 8월 5일
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.

카테고리

Help CenterFile Exchange에서 Text Files에 대해 자세히 알아보기

제품


릴리스

R2019b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by