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

15 次查看（过去 30 天）
Abhinav
Abhinav 2020-2-17
回答： Motasem Mustafa 2021-9-1
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.
  11 个评论
显示 9更早的评论
Abhinav
Abhinav 2020-2-18
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
Star Strider 2020-2-18
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.

请先登录，再进行评论。

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

回答（4 个）

Walter Roberson
Walter Roberson 2020-2-17
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
  3 个评论
显示 1更早的评论
Walter Roberson
Walter Roberson 2020-2-17
Please attach a sample file and your lines that invoke readtable()
Abhinav
Abhinav 2020-2-17
编辑：Abhinav 2020-2-17
I have attached the sample file now, along with the code lines.

请先登录，再进行评论。

Motasem Mustafa
Motasem Mustafa 2020-10-19
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.
  1 个评论
Mohd Khairi Mohd Zambri
Mohd Khairi Mohd Zambri 2021-9-1
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?

请先登录，再进行评论。

Yutaka Yamada
Yutaka Yamada 2021-5-24
编辑：Yutaka Yamada 2021-5-24
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.
Motasem Mustafa
Motasem Mustafa 2021-9-1
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');

类别

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

标签

Community Treasure Hunt

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

Start Hunting!

Translated by