MATLAB Answers

How to import an excel file that contains an absolute time column?

23 views (last 30 days)
Diego Dranuta
Diego Dranuta on 15 Jan 2020 at 16:27
Commented: stozaki on 21 Jan 2020 at 8:23
I have a file that contains some data and one of the colums is in absolute time format. I am using the xlsread function but the issue is that it won't show the date but only NaN values on that column (2nd column)
Any hints on how to do so?
here is my code so far
xlsread ECUSIMTEST0000-000-01.csv
for k=1:size(ans,2)
ans(:,k) = fillmissing(ans(:,k),"previous")
end
test.PNG
This is the actual data I recorded.
matlabview.PNG
and this is how it shows in matlab. Also, is there a way to show the values without the scale?
Thanks!!!

  0 Comments

Sign in to comment.

Answers (3)

stozaki
stozaki on 15 Jan 2020 at 17:05
Hello
Please define a return value of xlsread. for example..
[~,~,raw] = xlsread('ECUSIMTEST0000-000-01.csv')
raw =
13×3 cell array
{'Time ' } {'Absolute Time'} {'Engine Speed'}
{'s' } {'DateTime' } {'RPM' }
{[ 300]} {'2019/12/20' } {[ 1000]}
{[300.1000]} {'2019/12/21' } {[ 1000]}
{[300.2000]} {'2019/12/22' } {[ 1000]}
{[300.3000]} {'2019/12/23' } {[ 1000]}
{[300.4000]} {'2019/12/24' } {[ 1000]}
{[300.5000]} {'2019/12/25' } {[ 1000]}
{[300.6000]} {'2019/12/26' } {[ 1000]}
{[300.7000]} {'2019/12/27' } {[ 1000]}
{[300.8000]} {'2019/12/28' } {[ 1000]}
{[300.9000]} {'2019/12/29' } {[ 1000]}
{[ 301]} {'2019/12/30' } {[ 1000]}
please see xlsread documentation.
Regards,
stozaki

  2 Comments

Diego Dranuta
Diego Dranuta on 15 Jan 2020 at 17:17
Hi stozaki,
Thanks for your reply. The issue with that is that I wanna use the fillmissing function on the double afterwise and if I imported as a raw file I won't be able to play around with it.
stozaki
stozaki on 15 Jan 2020 at 17:39
Hi,
In Excel, dates are strings. So I think you can only get dates as strings.
dateTime.png

Sign in to comment.


Diego Dranuta
Diego Dranuta on 15 Jan 2020 at 17:58
doing it that way the date data is not representative anymore. There has to be a more efficient way to conserve the date format and be able to play around with the data

  1 Comment

stozaki
stozaki on 15 Jan 2020 at 22:12
Hello, Diego
Can you remove '/' slash and ':' colon from date data? Sslash and colon aren't numeric. They are string.
Numeric characters can be converted to double type with str2double, but simple characters cannot be converted to double type.
If you convert a simple character to double, it will be Nan.
Regards,
stozaki

Sign in to comment.


Diego Dranuta
Diego Dranuta on 16 Jan 2020 at 17:26
I can't edit the format since these files are auto generated by a software I can't edit. And I get thousands of files a month.

  1 Comment

stozaki
stozaki on 21 Jan 2020 at 8:23
Why don't you edit with the MATLAB program?
date1 = '2020/01/01';
tmpData1 = replace(date1,'/','');
date1 = str2double(tmpData1)
date =
20200101

Sign in to comment.

Sign in to answer this question.

Tags