simulink spreadsheet, datetime as duration

Hi, I have like 5 spreadsheets sampled with different sampling time over a year.

I want to use them in simulink, how can i use a datetime (dd/mm/yyyy hh:mm:ss) as sampling time?

Answer 1

[Nehemiae]

Hello @Ultrareidar,

I will assume an Excel file "Sample Data.xlsx", which has a column named DateTime with time logs for each data sample, in the format (dd/mm/yyyy hh:mm:ss). MATLAB has the datetime datatype that is automatically associated with the table that is read in. Then the duration in seconds between sample points can be calculated as below.

dataTable = readtable("Sample Data.xlsx"); 
reqDur = dataTable.DateTime(2) - dataTable.DateTime(1); % Replace DateTime with the column name in the Excel file with the time log
reqSec = seconds(reqDur);

Then, "reqSec" can be used in the "Sample Time" property of the "powergui" of Simulink that controls the sample time for discrete simulations in Simulink.

The documentation on datetime (https://www.mathworks.com/help/matlab/ref/datetime.html#d124e321916) can help in understanding the above code.

[Ultrareidar]

Thanks, but i think it is not what im looking for.

I think the best is to have a sample time of 1 sec (). Further, I want to get data from 5 different spreadsheet based on their timestamp. All five spreadsheets has different timestamps.

[Nehemiae]

Hello,

You could take the sample time as the least sampling time among the five spreadsheets. At any rate, the "Sample Time" property of each From Workspace block (for the five files) can be set with the sampling time of that respective file.

[Ultrareidar]

You mean setting the sample time (here: 15) to a variable? The thing is that within each speadsheet the sampling time changes. How can i modify som that the variable changes based on changing sampling time?

[Nehemiae]

Since you trying to extract the values from the spreadsheet, based on the logged time, essentially in Simulink what is required is a scaled version of the same. This can be done by transforming the time data into a character array and feeding it along with the data, into a timeseries object. Now this timeseries object can be called in the From Workspace block. The "Sample Time" property for each From Workspace block can be set to 0. If it is required that the data value from the spreadsheet is held till the next the timestamp, then uncheck "Interpolate data". Based on your requirement, under "Form output after final data value by" select "Holding final value". In the Model Settings > Solver, set the "Max step size" to 0.001 and "Min step size" to 0.0001, in the case of a variable-step solver. The code below can then be extended to the 5 files as required.

dataTable1 = readtable("Sample Data.xlsx"); 
dataFile1 = timeseries(dataTable1.Values, char(dataTable1.DateTime));

This is the output for interpolated data.

You could use the From Spreadsheet block, if you had time data in terms of simulation time, which is what is created above.

The documentation on the "timeseries" function (https://www.mathworks.com/help/matlab/ref/timeseries.html#d124e1251402) can be helpful.

[Ultrareidar]

Thanks, one more thing.

Now my simulation result is based on the step size.

how can i ensure that my stepsize represent "1 sec" in my timestamp. From my spreadsheets I get a timestamp (date and time), and i want to sum the values each second to find the total. So between each timestamp the value should be held(as you explained how to).

[Nehemiae]

The step size for the simulation can be set in the Model Settings > Solver. In this case, a fixed-step solver could be chosen. However, the sample time too needs to be scaled. From my previous example, this would mean a step size of 0.0007 (for 1 second). To confirm the same, take the timeseries object created (in the previous example, dataFile1) and check the array of simulation time values (dataFile1.Time). Then the difference between the first two elements would be the simulation time between the two data points, and since the actual time (in dd/mm/yyyy hh:mm:ss) between the points are known, it is possible to get the value of 1 second in simulation time. This value can then be used in the fixed-step solver step size. Also note, based on the largest value in the array of simulation times, the Stop Time in Simulink would need to be changed.

[Eric Sofen]

It's not clear to me whether you want to:

A) bring the data into Simulink at its original logged rate and do aligning (interpolation or sampling) as part of the data ingestion and solving process, or

B) Align the data from the 5 spreadsheets first in MATLAB and then pass it to Simulink.

I think Nehemiae's answers address (A). For (B), you can read the data into timetables and then use the synchronize function to get all the data into a timetable with a 1s timestep.

[Ultrareidar]

Yes, its has been solved. I added a column to my excel sheet that stored the duration between each sample point, often 15 sec. Then used this line to use it as duration in a timetable : cumsum(duration(0, 0, seconds(dataTable.Duration - dateshift(dataTable.Duration, 'start', 'day'))))

And now i can run with a steptime of 1s.

Thanks