time formate changes while exporting table as excel

Asked by S.M on 18 Jun 2019

Latest activity Commented on by Eric Heubel on 6 Sep 2019

17 views (last 30 days)

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

Guillaume on 18 Jun 2019

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.

S.M on 18 Jun 2019

Hi, sorry, didn't think about that. I'll upload the whole script and the excel files.

Products

MATLAB

3 Answers

Link

Answer by Guillaume on 18 Jun 2019

Edited by Guillaume on 18 Jun 2019

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

Guillaume on 18 Jun 2019

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.

S.M on 18 Jun 2019

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.

Link

Answer by Peter Perkins on 19 Jun 2019

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

S.M on 19 Jun 2019

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

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.

Peter Perkins on 19 Jun 2019

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.

S.M on 24 Jun 2019

Looks really like an excel issue, but still thanks for your help, I will see, if I can use it.

Link

Answer by Eric Heubel on 10 Aug 2019

Edited by Eric Heubel on 11 Aug 2019

Hi everyone!

I found this post as I have had a similar issue trying to write data with timestamps of millisecond precision to Excel. After about a day or two of digging, I believe the issue might actually be in the libmwspreadsheet.dll, but I have not found a way of digging any further. [Note: I am running MATLAB R2019a on Windows 10]

I traced back the issue to the writeXLSFile function, interestingly enough, the 'duration' class data type is written without any rounding issue. Although the exceltime function properly converts from MATLAB 'datetime' class to an Excel date value down to the millisecond precision, writeXLSFile converts this to a complex number to flag that it should be formatted as a datetime cell in Excel (lines 157 to 164 in my case, but similar code exists in lines 116 to 123 of writeXLSFile):

elseif isa(varj,'datetime')
    if any(exceltime(varj, dateOrigin) < 0)
        varj = cellstr(matricize(varj),[],locale);
    else
        % datetimes are represented as complex numbers in C++. The
        % signals libmwspreadsheet that the data is a datetime and
        % should be treated as such.
        varj = arrayfun(@(x){complex(x,0)},exceltime(varj));

Simply writing out the datetime in the same manner as a duration carries the proper value and precision. The DLL file must understand string in the following modification as datetime.

        % varj = arrayfun(@(x){complex(x,0)},exceltime(varj));
        % Replacing the above with the string formatted datetime
        varj = cellstr(matricize(varj),[],locale);

Though the default formatting in the output Excel file only displays the time, the date information is still there, just need to convert the cell formatting from mm:ss.0 to mm/dd/yyyy hh:mm:ss.000 or any desired display pattern.

From the above example using the original file yields

tt = timetable([1;2;3],'RowTimes',datetime(2019,6,19,0,0,0:.001:.002,'Fromat','dd-MMM-yyyy HH:mm:ss.SSS'));
writetimetable(tt,'timetable.xlsx')

And with the modified writeXLSFile line

Cheers,

-Eric

2 Comments

Guillaume on 12 Aug 2019

Thanks for investigating. Have you raised this as a bug with Mathworks. This is certainly something that should change.

Eric Heubel on 6 Sep 2019

I put in a bug report with Mathworks last month. I was told the issue was forwarded to the developers, but we will have to see if a fix comes with the next release.

Thanks!

Products

MATLAB

You are now following this question

S.M (view profile)