Readtable 00:00:00 is missing
2 views (last 30 days)
Show older comments
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!
0 Comments
Answers (2)
Titus Edelhofer
on 25 Jul 2016
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 Comment
Mee Youu
on 22 Aug 2020
Edited: Mee Youu
on 22 Aug 2020
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
on 23 Oct 2020
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
0 Comments
See Also
Categories
Find more on Logical in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!