MATLAB Answers

importing excel file to matlab without losing date format?

조회 수: 5(최근 30일)
Rosanna Fish
Rosanna Fish 20 Nov 2019
댓글: Vijay Sagar 23 Nov 2019
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

로그인 to comment.

채택된 답변

Vijay Sagar
Vijay Sagar 21 Nov 2019
편집: Vijay Sagar 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)

  댓글 수: 7

표시 이전 댓글 수: 4
Rosanna Fish
Rosanna Fish 22 Nov 2019
Ok Thank you, I will try to manually check the date time columns. I have around 55,000 rows so it might take a while!
Thank you for your help
Rosie
Vijay Sagar
Vijay Sagar 22 Nov 2019
Please check these link about formated dates appearence in different excel in diffenent system and their solutions. It may be related to your question.
Thank you.
Vijay Sagar
Vijay Sagar 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.

로그인 to comment.

추가 답변(1개)

Stephen Cobeldick
Stephen Cobeldick 21 Nov 2019
편집: Stephen Cobeldick 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

Rosanna Fish
Rosanna Fish 22 Nov 2019
Hi Stephen,
Thank you for this, I have given it a go and I got this error:
Error using strcat (line 46)
Inputs must be character vectors, cell arrays of character vectors, or string arrays.
Thank you, Rosie
Stephen Cobeldick
Stephen Cobeldick 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.
Rosanna Fish
Rosanna Fish 22 Nov 2019
Hi, I've uploaded a few rows of my data here.
Thank you very much, Rosie

로그인 to comment.

이 질문에 답변하려면 로그인을(를) 수행하십시오.

태그


Translated by