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

조회 수: 33 (최근 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
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
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.
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 CenterFile Exchange에서 Spreadsheets에 대해 자세히 알아보기

Community Treasure Hunt

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

Start Hunting!

Translated by