importing excel file to matlab without losing date format?

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

Answer 1

[Vijay Sagar]

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)

[Rosanna Fish]

Hi Vijay, thank you so much for your help.

That sort of works, it gives me the date in a cell array and the time and data in a struct but the time is still in decimal yearday(I think).

I get this error message when I run it:

Error using dtstr2dtvecmx

Failed to convert from text to date number.

Error in datevec (line 123)

y = dtstr2dtvecmx(t,icu_dtformat);

Thank you again, Rosie

[Vijay Sagar]

Hi Rosie, This code is working without any error in my Matlab. Once you check date-time rows of both columns manually. It is possible that somewhere the date-time format may be changed. If it is still not work then upload a sample file.

For an example If I change the format 01.01.18 to 01/01/18 at any random raw, I will get the same error.

Error using dtstr2dtvecmx
Failed to convert from text to date number.
Error in datevec (line 117)
            y = dtstr2dtvecmx(t,icu_dtformat);

[Rosanna Fish]

The only difference I can see is that my data column is formatted 01.1.12 as opposed to 01.01.12. Would that be causing the issue? I can't work out how to change it in excel.

Thank you, Rosie

[Vijay Sagar]

No, This will not cause the issue, Only the signs such as '/', ' - ' in between the number may produce the error.

[Rosanna Fish]

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]

Please check these link about formated dates appearence in different excel in diffenent system and their solutions. It may be related to your question.

https://excel.tips.net/T003218_Formatted_Dates_Appear_Differently_on_Different_Systems

https://superuser.com/questions/629014/why-does-excel-treat-some-dates-differently-than-others

Thank you.

[Vijay Sagar]

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.

[SHIJO ZACHARIA]

Very good.It works

Answer 2

[Stephen23]

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

[Rosanna Fish]

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

[Stephen23]

@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]

Hi, I've uploaded a few rows of my data here.

Thank you very much, Rosie