importing excel file to matlab without losing date format?
11 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
Rosanna Fish
le 20 Nov 2019
Commenté : SHIJO ZACHARIA
le 21 Sep 2021
Hi, I'm wondering if anyone can help me, I have spent all day trying to load some data into matlab. I finally managed to do it by convering it to a .txt file but my date variable comes out as a NaT or as a number that doesn't equate to any of my data.
My excel file has three columns without headers in this format:
10.12.19 10.37.00 5
Does this make sense to anyone? I would really really appreciate it if someone has any ideas please. Thank you
0 commentaires
Réponse acceptée
Vijay Sagar
le 21 Nov 2019
Modifié(e) : Vijay Sagar
le 21 Nov 2019
Hi Rosanna, If your column data is in the following format ''Years.Months.Days Hours.Minutes.Seconds Data'', then the following code will work. I have created a xlsx file with data as you descrived above. At the end you can save date-time with your data column.
clear all
% Reading data from excel file
[a b]=xlsread('yourdata_according_to_question.xlsx')
% Reading Year, Month, Day
[y, m, d] = datevec(b(:,1),'dd.mm.yy')
% Reading only HOURS, Minutes and Seconds
[yyyy, mm, dd, HH, MM, SS] = datevec(b(:,2),'HH.MM.SS')
% Converting to Year, Month, Day, HOURS, Minutes and Seconds to charectors
y=num2str(y);
m=num2str(m);
d=num2str(d);
HH=num2str(HH);
MM=num2str(MM);
SS=num2str(SS);
% Adding all charector into single line
ymd=strcat(y,'-',m,'-',d);
hms=strcat(HH,':',MM,':',SS);
alltime=strcat(ymd,{' '},hms);
% Converting to datevec or datenum
alltime_dnum=datevec(alltime)
alltime_dvec=datenum(alltime_dnum)
8 commentaires
Vijay Sagar
le 23 Nov 2019
Hi Rosie, Your uploaded file in Stephen Cobeldick answer shows that your data is in 'dd/mm/yy' format not in 'dd.mm.yy'. It is appearing in 'dd.mm.yy' due to your local computer's excel settings.
>> [a b]=xlsread('rosiedata.xlsx')
b =
8×2 cell array
'2/7/2017' '14.45.00'
'4/8/2017' '11.35.00'
'4/8/2017' '11.35.00'
'4/8/2017' '12.25.00'
'4/8/2017' '12.25.00'
'4/8/2017' '13.40.00'
'4/11/2017' '12.55.00'
'4/11/2017' '13.00.00'
So you need to change the format
[y, m, d] = datevec(b(:,1),'dd.mm.yy')
to
[y, m, d] = datevec(b(:,1),'dd/mm/yy')
then you will not get errors.
Plus de réponses (1)
Stephen23
le 21 Nov 2019
Modifié(e) : Stephen23
le 21 Nov 2019
Simpler:
>> T = readtable('test.xlsx','ReadVariableNames',false);
>> T.Properties.VariableNames = {'ymd','HMS','data'};
>> D = datetime(strcat(T.ymd,'_',T.HMS),'InputFormat','yy.MM.dd_HH.mm.ss')
D =
19-Dec-2010 10:37:00
19-Dec-2010 10:38:00
19-Dec-2010 10:39:00
19-Dec-2010 10:39:00
18-Jan-2001 01:01:01
Or allocate back into the same table:
>> T.timestamp = D
T =
ymd HMS data timestamp
__________ __________ ____ ____________________
'10.12.19' '10.37.00' 5 19-Dec-2010 10:37:00
'10.12.19' '10.38.00' 5 19-Dec-2010 10:38:00
'10.12.19' '10.39.00' 5 19-Dec-2010 10:39:00
'10.12.19' '10.39.00' 5 19-Dec-2010 10:39:00
'01.01.18' '01.01.01' 5 18-Jan-2001 01:01:01
3 commentaires
Stephen23
le 22 Nov 2019
@Rosanna Fish: it worked for me using my supplied test file, so your data file must be different, Please upload a sample file by clicking the paperclip button.
Voir également
Catégories
En savoir plus sur Spreadsheets dans Help Center et File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!