MATLAB Answers

0

Importing Time data from excel.

Asked by anooja thomas on 8 Apr 2019
Latest activity Commented on by anooja thomas on 9 Apr 2019
I am trying to read an excel file containg the date and time in the format "dd/mm/yyyy HH:MM:SS". (The excel contains five column [date, data1, data2, data3, data4])
I read the excel file with following code
[num,text,both]=(xlsread('file_name'));
and extracted date from it using
data_only = both(:,1);
s=datenum(data_only ,'dd-mm-yyyy HH:MM');
date=datestr(s,'dd-mm-yyyy HH:MM:SS');
My date starts with 25-03-2019 00:00:00 and increase with time step of 15 minute (ie 25-03-2019 00:00:00, 25-03-2019 00:15:00, 25-03-2019 00:30:00.....
The cell with date "25-03-2019 00:00" in excel ie at 12 am (00:00:00) is reading as "25-03-2019" only in matlab and gave error in line 3 of the code. When i ignored the first value by modifing the line 2 as
data_only = both(2:end,1);
ie date start from "25-03-2019 00:15" i am getting the required result.
Error message was
Error using datenum (line 181)
DATENUM failed.
Error in file_name (line 3)
s=datenum(data_only ,'dd-mm-yyyy HH:MM');
Caused by:
Error using dtstr2dtnummx
Failed to convert from text to date number.
I have tried with format as "date" and custom format of dd-mm-yyyy HH:MM while saving the excel file.

  2 Comments

Does datetime work? In place of datenum.
Yes it worked. Thank You

Sign in to comment.

1 Answer

Answer by Cris LaPierre on 8 Apr 2019

I find readtable works best with Excel files.
opts = detectImportOptions('file_name.xlsx');
data = readtable("file_name.xlsx",opts)
Where your first column is time data, consider converting your table to a timetable.
data = table2timetable(data)
The problem with your code is the format changes from row 1 to row 2. When trying to manually convert, you have to handle both cases. Better to use readtable if you can.

  2 Comments

If you are using the latest MATLAB (R2019a), you can use newly introduced readtimetable function to do it, like:
TT = readtimetable('file_name.xlsx');
Thankyou.. It worked.

Sign in to comment.