MATLAB Answers

S.M
0

time formate changes while exporting table as excel

Asked by S.M
on 18 Jun 2019 at 8:14
Latest activity Commented on by S.M
on 24 Jun 2019 at 20:27
Hi guys,
I'm a bit desperate, could not solve this problem by myself, perhaps you can help me.
I got several Excel-tables with times and variables with different time-steps. I imported and synchronized them in matlab. Now I would like to save the synchronized table as Excel table again.
But here comes my problem:
first i convert timetable to table, everything works, nothing changes. When I try to save the table as Excel, it changes the time-formate.
In the timetable and converted table the time-formate is 'dd.MM.yyyy HH:mm:ss.SSS'.
first row: '18.08.2018 10:02:12.497'
second row: '18.08.2018 10:02:12.528'
The formate in Exel is
first row: '18.08.2018 10:02:12,12'
second row: '18.08.2018 10:02:13.13'
So the information about milliseconds get lost somehow. It's writing 12 milliseconds until the second changes to 13 and then all the milliseconds are again 13.
I hope I could explain it more or less good and you can help me.
Thanks in advance.

  2 Comments

Can you show us the code you're using to save the table or timetable to excel? Can you also attach a mat file with one of the timetable you're saving?
Note that in R2019a, there is no longer a need to convert to a table. You can use writetimetable directly.
Hi, sorry, didn't think about that. I'll upload the whole script and the excel files.

Sign in to comment.

2 Answers

Answer by Guillaume
on 18 Jun 2019 at 14:30
Edited by Guillaume
on 18 Jun 2019 at 15:46
 Accepted Answer

The date/time format specifiers in Excel are slightly different from the ones use by matlab. In particular, to display milliseconds in Excel, you use .000, not the .SSS used by Matlab. .SSS in excel displays the seconds, hence why you're seeing 12.12, 13.13, etc.
However, If you use .000 with your output file, you'll see that all milliseconds are 0. I'm not clear at which point the milliseconds are lost but they're clearly not written by writetable.Unfortunately, there's no workaround if you use writetable.
edit: see comment, this is not true:On the other, if you're on R2019a, writetimetable doesn't loose the milliseconds. I will ask for clarification from Mathworks on the reason for the difference between the two. So, if you are on R2019a, the fix is:
writetimetable(Tdata, filename); %and no need to create Tdata_sync
Also note that:
width = width(Tdata);
% remove NaN by overwriting with previous values
for i = 1:width;
Tdata{:,i} = fillmissing(Tdata{:,i}, 'previous');
end
is simply:
Tdata = fillmising(Tdata, 'previous'); %loop completely unnecessary

  2 Comments

I've investigated a bit further the difference between writetable and writetimetable and there can't be any difference since all writetimetable does is call writetable(timetabletotable(...)). So I don't know how I got an excel file with the milliseconds in it. I will have to investigate further tomorrow when I'm back on the same computer where I did my initial testing.
Ok, thank you so much for your help. Let's see, if you can find it out. In the meantime I will try to organize R2019a. I'm running the R2018b.

Sign in to comment.


Answer by Peter Perkins
on 19 Jun 2019 at 13:18

S.M., are you quite sure this is not simply a formatting issue in the spreadsheet? When I run this code in a recent version of MATLAB, the only problem I see is that the cells in the output spreadsheet need to have their display format set to something that display the fractional seconds.
Here's a screenshot of the spreadsheet I get, after I set the format for Time to include .000 to show milliseconds.
Screen Shot 2019-06-19 at 9.15.25 AM.png

  3 Comments

Hi Peter,
so today I installed the R2019a, hoping, that the problem will be solved. Unfortunately it didn't.
So I made pictures to show how it looks.
This is one of the original excel tables, which I load in Matlab.
Time formate OnOff Table before loading in Matlab.jpg
This is the timetable after I run the script: the time formate is still ok here
Tdata as timetable in matlab.jpg
This is the excel sheet which is saved after the execution:
Time formate Tdata Table after saving as excel.jpg
The only thing I can think is that I'm using the german Excel version and there its not allowed to work with dots in the time formate, instead of a "." its an "," to separate the seconds from the milliseconds. Maybe there is the problem? On the other hand, Matlab recognizes it also, when I load the original excel files.
I also tried to change 'datetime.setDefaultFormats('default','dd.MM.yyyy HH:mm:ss.SSS')' in 'datetime.setDefaultFormats('default','dd.MM.yyyy HH:mm:ss,SSS')', also didn't help.
I think this is an excel issue, and that you need to solve it in Excel. I'm going to suggest that in the output spreadsheet, you set the format of those cells to "Number", with some large value for "Decimal Places", like 12, and look at what's in them. For dates in june 2019, you should see numbers vaguely like this
Screen Shot 2019-06-19 at 4.43.05 PM.png
Those correspond to
>> tt = timetable([1;2;3],'RowTimes',datetime(2019,6,19,0,0,0:.001:.002,'Format','dd-MMM-yyyy HH:mm:ss.SSS'))
tt =
3×1 timetable
Time Var1
________________________ ____
19-Jun-2019 00:00:00.000 1
19-Jun-2019 00:00:00.001 2
19-Jun-2019 00:00:00.002 3
1ms is about 1e-8 days, so you should see values out to 8 decimal places.
Looks really like an excel issue, but still thanks for your help, I will see, if I can use it.

Sign in to comment.