readtable is not reading the time 12:00:00 AM?

I am using 'readtable' to read an excel file. The first column of the file contains date and time in 'mm/dd/yyyy HH:MM:SS PM' format. The probelm is the following:

The readtable skips the time if the entry is '5/7/2018 12:00:00 AM'. I want to read entire date-time string. is it possibel to do it using the readtable?

I am using the following code to invoke the readtable:

  filename=fullfile(direc,station_name);
  T=readtable(filename);

Note: the readtable is able to read other time-entries perfectly; the above time-entry is 1427th row in the file, including header.

[Joel Bay]

Anything else specific to this table entry? Is it the first line of the table and being read as a header line?

[Star Strider]

See if using detectImportOptions and then defining the way the dates and times are read can do what you want.

[Abhinav]

Joel Bay, the above time-entry is 1427th row in the file, including header. Header is the first row.

[Abhinav]

Star Strider, I am using MATLAB 2014a. teh 'detectImportOptions' is not contained my MATLAB version.

[Star Strider]

When I looked at the file (in Excel), I did not notice any ‘AM’ or ‘PM’ designations, and the times appeared to be in 24-hour format. They also imported as such in MATLAB using readtable. I also looked at the specific rows 1425:1435 and saw no problems. (I am using R2019b.)

[Abhinav]

That is strange. In my system, I can see AM/PM designations (in the dropdown tab), as in the atatched picture. It might be a bug in 2014a then?

[Star Strider]

I am not certain where the ‘AM’ and ‘PM’ designations come from. They are not in the Excel file, and they are not in the readtable output. I also do not recognise the application you are using to examine that file (largely because I likely do not use it), although it appears to be a MATLAB application.

I would just use the datetime values that readtable creates, remember to use HH to designate the hours in the datetime Format options if necessary, and you should have no problems.

[Walter Roberson]

Yes, when I click on individual elements I see the AM/PM

[Abhinav]

Star Strider, I am using excel to visualize the data. I have no idea what is the issue with AM/PM designation. I will just go with ussual textscan, I think.

[Abhinav]

Star Strider: I just wanted to point out that when I convert the excel into a tab-separated text-file; the AM/PM designations go away and I can write my code.

[Star Strider]

Noted. Apparently the MATLAB application was adding the AM/PM designations, since they do not appear in the Excel file, at least that I can see.

Answer 1

[Walter Roberson]

Your input format specification should be 'MM/dd/yyyy hh:mm:ss a' to read that.

m is minutes not month

H is only for 24 hour clock

M is for month not minutes

S is for fractions of a second

PM is the wrong specifier for AM/PM

[Abhinav]

Walter Robinson, the same problem still persists after changing the date format as per your sugggestion.

[Walter Roberson]

Please attach a sample file and your lines that invoke readtable()

[Abhinav]

I have attached the sample file now, along with the code lines.

Answer 2

[Motasem Mustafa]

I have the same issue.

I have a data from a datalogger and when I am using a code to separate the year,month,day, and time from the data, it gives me an error. When I skip the row of the time 00:00:00, the code works.

When openeing the workspapce it shows that 00:00:00 is empty as shown in the screenshot below.

I am still struggling with the code and did not solve it.

[Mohd Khairi Mohd Zambri]

I have the same problem with the first row, but if the first row is not 12:00:00 AM then it does not have any problem. Can anyone help?

Answer 3

[Yutaka Yamada]

Hi,

Recentely I've tried similar thing.

Please try below code.

opts = detectImportOptions('readTime.xlsx');
opts.VariableTypes = 'datetime'; 
opts.VariableOptions.DatetimeFormat =　'mm/dd/yyyy HH:mm:ss a';

T = readtable('readTime.xlsx', opts);

I've attached the Excel file that I've used for the test.

Answer 4

[Motasem Mustafa]

Assalm Alykom Mohammad Khairi,

Kindly find the below code that worked for me last year and kindly find the linke to the answer.

https://uk.mathworks.com/matlabcentral/answers/623053-date-parsing-error-for-time-00-00-00-from-ms-excel#answer_522683

Bel tawfeeq

All the best,

Motasem

The new code that worked .
Thanks @ Walter Roberson
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');