time formate changes while exporting table as excel

23 visualizzazioni (ultimi 30 giorni)
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.
  3 Commenti
S.M
S.M il 18 Giu 2019
Hi, sorry, didn't think about that. I'll upload the whole script and the excel files.
João Campos
João Campos il 13 Apr 2021
Hi in excel you should use: 'dd.MM.yyyy hh:mm:ss.000' instead of dd.MM.yyyy hh:mm:ss.SSS

Accedi per commentare.

Risposta accettata

Guillaume
Guillaume il 18 Giu 2019
Modificato: Guillaume il 18 Giu 2019
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 Commenti
Guillaume
Guillaume il 18 Giu 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
S.M il 18 Giu 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.

Accedi per commentare.

Più risposte (2)

Eric Heubel
Eric Heubel il 10 Ago 2019
Modificato: Eric Heubel il 11 Ago 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 Commenti
Guillaume
Guillaume il 12 Ago 2019
Thanks for investigating. Have you raised this as a bug with Mathworks. This is certainly something that should change.
Eric Heubel
Eric Heubel il 6 Set 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!

Accedi per commentare.


Peter Perkins
Peter Perkins il 19 Giu 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 Commenti
Peter Perkins
Peter Perkins il 19 Giu 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
S.M il 24 Giu 2019
Looks really like an excel issue, but still thanks for your help, I will see, if I can use it.

Accedi per commentare.

Prodotti

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by