How to convert timeseries class mat file to excel
42 views (last 30 days)
Show older comments
Hello.
Facing probelm with timeseries class. i want to convert the mat file into excel. I have used the below code,but it's showing only the mat file name.
a=load('simout1.mat');
whos -file simout1.mat
b=struct2table(a);
writetable(b,'justtry6.xls');
Result: simout1
the problem is table b has another double timeseries cell. its difficult to convert into excel file form simout1.mat file.
Would you please help to convert into excel ? i have uploaded the mat file(simout1.mat) in google drive as i can not upload here.
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/812649/image.jpeg)
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/812654/image.jpeg)
4 Comments
KSSV
on 25 Nov 2021
To download the mat file......you need to give permissions. Change the permissions to anyone with link can download the file.
Accepted Answer
dpb
on 26 Nov 2021
Edited: dpb
on 26 Nov 2021
It's not bad at all...I let the file download overnight last night and got a few minutes to poke around just now...I was almost right yesterday. I renamed the timeseries to tsS locally for a shorter variable name...
>> ttS=array2timetable(tsS.Data(1:10,:),'RowTimes',seconds(tsS.Time(1:10)))
ttS =
10×4 timetable
Time Var1 Var2 Var3 Var4
________ ___________ ____ ____ __________
0 sec -6.0029e-06 0 0 6.4033e-06
0.05 sec -82.029 0 0 82.711
0.1 sec -81.195 0 0 81.864
0.15 sec -33.807 0 0 33.92
0.2 sec -34.5 0 0 34.618
0.25 sec -34.068 0 0 34.183
0.3 sec -33.664 0 0 33.777
0.35 sec -33.263 0 0 33.374
0.4 sec -32.865 0 0 32.974
0.45 sec -32.471 0 0 32.577
>>
created the timetable ttS from the first 10 rows that match the image you posted.
>> ttS=array2timetable(tsS.Data,'RowTimes',seconds(tsS.Time));
>> whos ttS
Name Size Bytes Class Attributes
ttS 12096001x4 483841676 timetable
>>
built the whole timetable in a pretty short amount of time...only a second or two here.
Of course, now you have a problem in that the maximum row limit in Excel is 1,048,576. This applies to Excel from Excel 2007 thru Microsoft 365 . Earlier versions were only 16K rows.
It'll take
>> tsS.Length/1048576
ans =
11.5356
>>
sheets if you were to try to go the absolute limit.
All in all, you're probably better off just leaving it in MATLAB in the timetable; it is simpler to deal with there although there is about a 25% memory penalty.
>> whos ttS tsS
Name Size Bytes Class Attributes
tsS 1x1 387072368 timeseries
ttS 12096001x4 483841676 timetable
>>
3 Comments
dpb
on 27 Nov 2021
Edited: dpb
on 27 Nov 2021
One last note -- I intended to add above
>> tsInfo=get(tsS)
tsInfo =
struct with fields:
Events: []
Name: ''
UserData: []
Data: [12096001×4 double]
DataInfo: [1×1 tsdata.datametadata]
Time: [12096001×1 double]
TimeInfo: [1×1 tsdata.timemetadata]
Quality: []
QualityInfo: [1×1 tsdata.qualmetadata]
IsTimeFirst: 1
TreatNaNasMissing: 1
Length: 12096001
>>
shows the original timeseries does not include any Events data; the only real reason one could fabricate for utilizing the timeseries object over either a table or the timetable as it is the only feature included in the timeseries without a direct corollary in timetable. Of course, they can be introduced as added variables; they're just not a builtin feature.
Something similar could be a reasonable enhancement request for timeseries, possibly...
More Answers (2)
dpb
on 25 Nov 2021
Edited: dpb
on 25 Nov 2021
You've managed to put a timeseries object into a table as a cell. To do anything with it, you've got to dereference it back to the inherent timeseries.
In other words, "Don't do that!"
Work with the timerseries as it is; the timeseries object is a real oddball thingie -- it is quite cumbersome and not well supported in continuing development in that there are no methods like read/writetable that work with it.
To convert it to a real timetable, try some variation on
ttS=timetable(seconds(simout1.Time),simout1.Data);
where you get the time information and the data from the timetable and put it into something more usable.
The events that may be with the timeseries will have to be dealt with in some other fashion -- I've never had a case of my own nor found one in all the responses to Q? here of the forum that I could not more easily solve with other tools than the timeseries, so I don't know all the ins and outs of it, but it always was limiting in some way for any problem I was ever faced with.
The file is too big to download practically here in available time...but I'm sure some variant of the above will get you where you need.
3 Comments
dpb
on 26 Nov 2021
It is/will be impossible to convert the TS object directly into an Excel file unless you delve into the guts of the TS implementation and add customized functionality into the class. I've not poked around to see how much of the implementation is still in m files. Either way, that undoubtedly would turn into more work than the problem would justify.
What I showed above should work...if you want some further assistance on going further, save a small portion of the original TS as a .mat file and attach it -- we don't need a 100MB+ file to illustrate a concept.
dpb
on 26 Nov 2021
" add customized functionality into the class"
Which functionality will be, of course, some specific implementation of the process outlined above...
Peter Perkins
on 28 Nov 2021
In MATLAB R2021b, you can use timeseries2timetable, and then write the timetable to excel.
This timeseries has nothing complicated in it, so in earlier versions you can do something like what dpb suggests, but I'd recommend this slight variation that uses the SampleRate parameter:
tt = array2timetable(ts.Data,'StartTime',seconds(0),'SampleRate',20)
4 Comments
dpb
on 29 Nov 2021
..."To some extent timetable is forgiving, it doesn't iteratively add up the time step and accumulate round-off, but still, best to pick the one for which there's an exact value. 44.1kHz, not 0.00002267573... sec, right?"
That answers the Q? I was asking, yes, Peter, thanks.
I agree as for using wthich is the round/integer/exact value, but for a data set picked up at random without prior knowledge, unless one does tests for which is the round value, there's no way to know which might be that one. It's possible it's more likely to be frequency and as you say if the application is engineering or acoustics or the like, possibly quite highly probable, but not a foregone conclusion, so I took the value that was actually given in the data and used it.
Does timeseries2timetable make a test of that sort or just always use frequency on the assumption?
I did not recall (and obviously didn't go look first) that timeseries actually went back quite that far...I just never found a use for it "in anger" so never got on my radar.
Thanks for the feedback, Peter, it's always useful to know something of the development mindset -- I hope I don't come off as incessantly carping for no purpose, but remain focused on product improvement and future considerations... :)
See Also
Categories
Find more on Data Type Conversion in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!