Readtable 00:00:00 is missing

9 次查看（过去 30 天）
chris_andreas
chris_andreas 2016-7-25
回答： Motasem Mustafa 2020-10-23
Hi. I have an Excelfile with a.o. a DateTime column. When I import the data in MATLAB with readtable the DateTimes like "15.06.2016 00:00:00" are always imported as just "15.06.2016". This problem just occures with 00:00:00 at the end.
Can anyone help, that the DateTime is imported correctly (with the 00:00:00)? Thanks a lot!

请先登录，再回答此问题。

回答（2 个）

Titus Edelhofer
Titus Edelhofer 2016-7-25
Hi,
I'm not sure if there is really a problem. I think it's just a question of display. Take a look at this code fragment:
s1 = datetime(2016, 6, 5)
s1 =
05-Jun-2016
s2 = datetime(2016, 6, 5, 0, 0, 0)
s2 =
05-Jun-2016 00:00:00
They are displayed differently but are indeed equal:
isequal(s1, s2)
ans =
1
You might change the display format later on:
s1.Format = 'dd-MMM-uuuu HH:mm:ss'
s1 =
05-Jun-2016 00:00:00
Hope this helps,
Titus
  1 个评论
Mee Youu
Mee Youu 2020-8-22
编辑：Mee Youu 2020-8-22
Of course there is a problem. I am reading datetimes from a CSV file and am trying to convert them to a date number using
[~, ~, myTable] = xlsread('MYfile.csv');
r=cell2dataset(myTable);
mdates=datenum(r.DATETIME,'dd/mm/yyyy HH:MM:SS');
So if the hours are missing in some rows of r.DATETIME then this obviously throws an error. If I don't specify a date format in datenum it does not throw an error but then Matalb is too stupid to consitently recognise European date formats on its own (i.e., it interprets 30/09/2018 as 30 September but then the next one 01/10/2018 as 10 January - how stupid is that?!). I can't believe that a software costing as much as yours is incapable of handling dates correctly.
For anyone who has the same issue and is actually looking for a solution (as Matlab does not even recognise this as a problem) you can fix it by not using xlsread but readtable instead. I.e., the first two lines of code from above should be replaced by:
r=readtable('MYfiles.csv');

请先登录，再进行评论。

Motasem Mustafa
Motasem Mustafa 2020-10-23
I used to have the same issue abd I have posted my question yesterday :
'' Dears,
I am using the code below to do parsing for date-time cells in an MS Excel sheet with date-time form of ( 01/05/2019 00:00) as in the screenshot below.
clc,clear,close all;
[num1,data] = xlsread('Book_new.xlsx','sheet1','A1:A30');
a=datevec(data,'dd/mm/yyyy HH:MM:SS');
date=datestr(datenum(a),'dd/mm/yyyy');
time=datestr(datenum(a),'HH:MM:SS');
Year=datestr(datenum(a),'yyyy');
mm=datestr(datenum(a),'mm');
dd=datestr(datenum(a),'dd');
yy=datestr(datenum(a),'yyyy');
[status,message] =xlswrite('motasem.xlsx',str2num(yy),'sheet1','A1:A30');
[status,message] =xlswrite('motasem.xlsx',str2num(mm),'sheet1','B1:B30');
[status,message] =xlswrite('motasem.xlsx',str2num(dd),'sheet1','C1:C30');
[status,message] =xlswrite('motasem.xlsx',string(time),'sheet1','D1:D30');
When I run the code for example for the 1st 30 readings (half hourly readings) it gives me the following error :
"Error using dtstr2dtvecmx
Failed to convert from text to date number.
Error in datevec (line 123)
y = dtstr2dtvecmx(t,icu_dtformat);
Error in motasem (line 4)
a=datevec(data,'dd/mm/yyyy HH:MM:SS');"
But when I change the range of data to avoid the first reading which contains the time 00:00:00 it works and gives the below output :
Any suggestions please ?
"
The new code that works is using readtable function as follows :
clc,clear,close all;
data = readtable('Book_new.xlsx','Range','A1:A60','ReadVariableNames',false);
A = table2array(data);
yy=datestr(datenum(A),'yyyy');
mm=datestr(datenum(A),'mm');
dd=datestr(datenum(A),'dd');
time=datestr(datenum(A),'HH:MM:SS');
[status,message] =xlswrite('motasem.xlsx',str2num(yy),'sheet1','A1:A30');
[status,message] =xlswrite('motasem.xlsx',str2num(mm),'sheet1','B1:B30');
[status,message] =xlswrite('motasem.xlsx',str2num(dd),'sheet1','C1:C30');
[status,message] =xlswrite('motasem.xlsx',string(time),'sheet1','D1:D30');
Hope this will help you
All the best

类别

在 Help Center 和 File Exchange 中查找有关 Dates and Time 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by