Table element access problem

I am reading a table from an excel file

T_Log_Pf = readtable(Log_Pf_path_fn);

But the compiler generates mxArray instead of a table, and when I try to change the element

T_Log_Pf{1, {'sts_e'}} = 1;

or get value of element from the table

x = T_Log_Pf.sts_e(1);

the error "Cell contents reference from a non-cell array object" occurs.

What and how should be corrected in order to work with the table?

Ore the problem is in kompilator setup?

[Matt J]

What do you mean by "the compiler"? Which compilation app have you used?

[the cyclist]

It might be helpful for you to upload the data, or at least a small sample that reproduces the issue. You can use the paper clip icon in the INSERT section of the toolbar.

[dpb]

Precisely the comment I was going to make -- it would be even better to have a full sample that illustrates the problem; as is it's not possible to conjecture just what was done nor what toolsets were used.

[Alex K]

My example:

T_test = readtable('test_table.xlsx');

data = T_test{1,1};

And error "Cell contents reference from a non-cell array object.",

which related to expression "T_test{1,1}".

And diagnostic report:

"T_test Local 1x1 mxArray"

PS

I need to read a table from a function.

Commands for reading from a table from WS are executed correctly.

[Alex K]

I added my example

[Matt J]

Attach a .mat file containing the table as it is successfully imported into the Matlab workspace. Also attach test_table.xlsx.

[Alex K]

ok

[Matt J]

I see two .xlsx files but not .mat file.

[Matt J]

I see no .mat file.

[Alex K]

Here...

PS

I can save my simulink model only as .slx or as .mdl

I don't know how can I save simulink model as .mat file

[dpb]

Remove the coder.extrinsic statement from the s-file function...

[Alex K]

If I delete "coder.extrinsic('readtable');",

I get an error: "Function 'readtable' not supported for code generation."

[dpb]

What are you trying to do here? I don't have Simulink but AFAIK you can write s-functions that call/use MATLAB features and return results to the model.

Where does the coder come into what you're trying to do?

[Alex K]

I am making a simulation model in Simulink.

There was a problem with the implementation of the functionality in Simulink, that I was trying to solve.

[dpb]

I did a search and it appears that readtable isn't supported by the coder(*) -- I wasn't aware until I found <this link> that that's how Simulink function block was implemented; that person had the same problem of trying to use readtable.

The suggestion there was to use <FromSpreadsheet> block. It would seem that would be the way Simulink intends for users to go, having builtin that block.

I don't suppose the venerable xlsread would be supported by coder, either; it would return the numeric data from a spreadsheet as a double array if your spreadsheet were constructed as such without multiple data types. If it is mixed data, then you probaby have an issue in handling mixed data types in a composite object under Simulink.

(*)ADDENDUM:

I guess that's not quite right on reflection; it appears that the coder did actually return something from the spreadsheet after the call using the extrinsic; but the returned object isn't a table in that environment, but an mxArray. Thus, you can't expect to be able to address it as if it were a table; it isn't one. OTOMH, I'm not sure how one would go about dereferencing an mxArray object with m-code; that's the province of mex files that use the encapsulated array with its header info as well as just the array data to pass back and forth.

While got that far, unless you can find some reference or somebody else comes along that knows how to get at the data inside, ooks to me like the shorter route would still be to revert to using straight text files and the builtin tools/blocks in Simulink.

Sorry, without ever having seen a Simulink installation, that's the best I can offer; maybe somebody else will come along or submit an official support request to TMW.

[Alex K]

It's strange that problems arise in terms of extracting data from and writing data to a table.

Functions of reading-writing from-to the table work correctly.

I need to process large streams of structured data sequentially. I used tables for this.

So far I've solved the problem like this:

- read a spreadsheet from excel;

- converted the table into an array (table2array);

- worked with some col of this array;

- rebuilt the table;

- saved the updated table.

Now the task arose of making small adjustments (change some elements) to the tables already created and stored in Excel.

Tried to make it simpler by using direct access to table elements. So far it hasn't worked.

[dpb]

See https://www.mathworks.com/matlabcentral/answers/13297-subscripting-into-an-mxarray#answer_18242 that indicates you have to preallocate the array to be assigned the output by the coder in order to then index into it. The above answer would work for a known size; a comment there asked about dynamically-sized arrays but that one wasn't addressed there.

I don't know if something like (btw, it would be MUCH simpler if you would attach your code snippets as text inline and formatted with the {} Code button instead of images -- nothing can be done with an image but look at it).

function(data,flag)=read_table(fn)
  coder.extrinsic('readtable');
  tT=table[];
  data=tT{:,:};
  flag=~isempty(data);
end

or not -- it would create an empty table object; whether that's enough for the coder to stuff the returned table into correctly or not, I've no klew...

I'd still suggest trying the builtin block and/or using whatever toolset there is to read a double array into Simulink directly--that functionality surely must be supported natively without needing such gyrations.

[Alex K]

Here is the code:

function [extract_data, Ready]= Read_table_from_file(Row_Nmbr, Col_Nmbr)

coder.extrinsic('readtable');

coder.extrinsic('table2array');

T = readtable('test_table.xlsx');

[row col] = size(T);

A = zeros(row,col);

A = table2array(T);

d = A(Row_Nmbr,Col_Nmbr);

%A(Col_Nmbr,Col_Nmbr) = 10;

%%

extract_data = d;

Ready = true;

end

Yes, I know that it is recommended to predefine variables.

But with the table, I have not yet done this to the end.

If you create an empty table without variables, then there is no effect.

And the full table has many variables + can change during the development of the simulation model. So I left it for later.

While I would like to understand the proposed code snippet ...

I predefine an array, but I can only access its elements when the array is on the right side of equation

d = A(Row_Nmbr,Col_Nmbr);

but when I try to change the elements of the array and move the array to the left side of the equation

A(Col_Nmbr,Col_Nmbr) = 10;

then I get an error: "Subscripted assignment into an mxArray is not supported for code generation",

regardless of the array predefinition

[dpb]

"Yes, I know that it is recommended to predefine variables."

The link provided indicates for coder to create the variable from an extrinsic it isn't "recommended" but required in order to be able to address the array later; apparently the coder needs that memory location predefined in the workspace context in order to place the object in the MATLAB variable location to then subsequently be referenced by MATLAB m-file syntax.

You haven't done that above; as noted, the issue of other than a fixed-size array wasn't addressed in the specific forum Q/A; but it's pretty clear to me that SOMEHOW such a memory address has got to be created before the call the the extrinsic function in order that that location is available to the coder in which to place the returned object.

As noted, the point of allocating the null table is NOT that it is a useful entity in and of itself yet, but to have that memory location for the coder to return the table object into in the hope that the coder has enough power to place the actual table into that variable and that you can the reference it normally.

Clearly, what you've tried without doesn't work; this is a wild stab in the dark and probably won't work, either, given that it appeared from the TMW Staff response one needed to allocate the actual space. But, if it does happen to be enough, then you're in luck; if it doesn't, you're no worse off than already are.

I don't follow and you've not shown the code that created the table object -- if that was somehow done in Simulink, then can you make it GLOBAL or structure the code to simply pass it once it was created instead of trying to reread it from the file (which would seem to be quite inefficient way to go about it if this isn't just an initialization routine but one that is going to be called every timestep).

ADDENDUM: Why don't you back off from the table for testing and see if you can mange to read an ordinary double array following the protocol outlined. Until you can get at least the simple case working, there's not much chance of anything more complex...

[Alex K]

I propose to solve the problem in parts.

The first problem is the table.

The second problem is the pre-definition of variables.

The problem with the table turned out to be rather complicated. I tried declaring a predefined table but that didn't help. Perhaps you can experiment further in this direction, but to save time and effort, I tried to find a "workaround". It consists in converting a table into an array and working with it.

And then I was overtaken by the second problem related to the impossibility of changing the elements of the array when writing the array on the left side of the expression.

I tried to predefine the size of the array. This array has a fixed dimension. And in the code example it is. But it still throws an error. And in the error report it is written that the array has a dimension of 1x1 and this is mxArray.

And here it is not clear to me why, despite the array predefinition, the simulink does not perceive this array correctly.

THAT. I propose not to consider the problem of the table yet, but to consider the problem associated with the inability to change the element of the array when it is completely predetermined.

Perhaps there is a way to work with mxArray, which will also solve the problem with access to the elements of the read table.

[Alex K]

The problem with working with an array has been partially fixed.

Indeed, if an array is declared in advance and with a fixed dimension, then the access problem to the table element can be solved. This is very inconvenient, because the dimension (number of variables/columns) can change during model development. But for now, I can put up with it, if every time I change the array dimension, I correct the dimension error that occurred when writing to the array.

I will try to more thoroughly test the possibility of predefining a table with the required columns and the maximum number of rows.

If it works, I'll let you know.

function [old_d, new_d, Ready]= Read_table_from_file(Row_Nmbr, Col_Nmbr)

coder.extrinsic('readtable');

coder.extrinsic('table2array');

coder.extrinsic('array2table');

coder.extrinsic('writetable');

T = readtable('test_table.xlsx');

[row col] = size(T);

A = zeros(511,37);

A = table2array(T);

d = A(Row_Nmbr,Col_Nmbr);

A(Row_Nmbr,Col_Nmbr) = d+1;

n_d = A(Row_Nmbr,Col_Nmbr);

TT = array2table(A);

writetable(TT,'test_table_new.xlsx');

%%

old_d = d;

new_d = n_d;

Ready = true;

end

[dpb]

"T_test    Local    1x1    mxArray"

shows that

T = readtable('test_table.xlsx');
[row col] = size(T);

will return [1,1] as I presumed it would; what is in the variable is the reference address of the object handle/container wrapping the underlying table data, not the derferenced content that the normal table variable in the MATLAB workspace would show. This obviously has to do with how Coder actually implements the call/return of the extrinsic function instead of acessing it directly from MATLAB code in an m-file.

It would take a lot of digging to figure out just how that all works behind the scenes; without Simulink I can't even think about trying to poke any deeper.

BUT, my poking has gotten to the help topic on <executing extrinsics> that outlines the rules given in the Answer before linked to -- namely the predefinition of the output variable, specifically.

Think you need to start here and read...it may lead you through the weeds to the promised alnd--then again, it wouldn't surprise me to learn tha the higher-abstraction objects like table simply aren't fully supported. The earlier link discussed the code generation process and the analysis going into it in defining the generated code. It is a static analysis which goes a long ways towards why changing sizes doesn't work. Makes me glad don't have to deal with Simulink; this would be a mess to have to deal with...

[Alex K]

The paradox is that the function size for the read table works correctly, see below.

Simulink is not a CAD-system.

Instead of designing algorithms, it need to think about the specifics of programming

[dpb]

See <Working with mxArrays>. It answers a lot once know from above that an extrinsic returns an mxArray and only an mxArray. Ergo, while you're calling readtable and expecting to get a MATLAB table; that simply ain't agonna' happen, no matter what. The content of the is contained as an mxArray, but getting at the content through MATLAB engine syntax isn't possible in this context, it seems. See <The MATLAB Array> for the description of how higher-level objects are stored.

In theory, you could write a mex file and decode the table, but if it were my problem, I'd look at far less complex storage mechanisms like straight double arrays if the need is to use the data in Simulink. You're just adding way too much overhead and complexity here.

It seems simple when you can call MATLAB code in a function block until one begins to fully understand how that functionality is implemented. It's nothing at all like just running the code in MATLAB, unfortunately,.

[Alex K]

I need a structured table for further processing of an Excel file. And here it is important to understand what data is contained in which column, becose the composition of the columns may change during the development of the model, and it will be even more difficult to administer an Excel file without automatic binding to variables.

So far, the most "simple" scheme is the following:

1. Read an Excel spreadsheet;

2. Predefine a fixed-size array equal to the maximum number of rows and the actual number of columns known at the time of compilation;

3. when defining an array, set NaN;

4. Initialize the array with values, taking into account the actual number of rows in the table, using the transformation of the table into an array;

5. Change the necessary values in the array;

6. Convert the adjusted array back into a table;

7. Write the corrected table to an Excel file.

It remains only to solve the issue with the naming of the columns for the adjusted table.

If it is possible to extract the variable names from the read table in step 1, it will be very good!

An alternative scheme can be associated with the predefinition of a temporary table with the maximum possible number of rows and number of columns, corresponding to the readed table on step 1.

[dpb]

Can you read a .mat file with Simulink? If so, the simpler way might be to save the table to a .mat file and read it instead; creating the .mat file when making the spreadsheet initially.

Alternatively, can you read/dereference a cell array or string array with the external keyword or are only doubles recognized by the preallocation step and everything else is still the raw mxArray? If could do that, then could save the Excel file as text/csv, read the first row only to get the names and then use readmatrix or another similar with the 'NumHeaderlLines',1 and then build the table internally instead of trying to read a table.

Thirdly, can you use low-level i/o functions like fgetl and/or fscanf? In that case, you can definitely read the file saved as text file and build the internal table.

Most of those if they would work would seem more direct than the above...

[Alex K]

@dpb, thanks for the suggested options!

And information about <executing extrinsics> was very useful.

I decided not to experiment with the table anymore, because any creation of a predefined table will still go through an external function table and therefore again will give mxArray and new problems.

I was quite satisfied with the solution with converting the table into an array with a fixed size, which is predefined.

With the variable names for the table, I decided to declare a global array with variable names. Because there are only several types of tables, I do not expect large expenses for their administration during the development of the model. Additionally, it is possible to guarantee the unity of table formats formed in different modules.

[Alex K]

@dpb, colleagues, hello!

In continuation of the question with the table, a few more appeared:

1. How to initialize an array with names of table column using m-file?

(e.g. { ' var1' ' var2' ' var3'} )

I initiate the model variables through an m-file which I run before running the model in Simulink. I would like to initiate this array in the same file.

To initiate variables, I use the sequence:

test_const = Simulink.Signal;

test_const.DataType = 'double';

test_const.Dimensions = 1;

test_const.Complexity = 'real';

test_const.SamplingMode = 'Sample based';

test_const.InitialValue = '0';

I would like to use something like this.

2. How to make a constant out of this variable?

It is recommended to set constants through functions.

But there can be many loads of the tables, and the simulation time must be reduced. Therefore, I don’t really want to waste time on the output of an external function, + an external function can cause problems with mxArray.

I found an option to frose the constant by using comand

test_const.Clocked = 'on';

but this command did not work for me.

Answer 1

[Sulaymon Eshkabilov]

Use this standard syntax to read the data from MS Excel into a table array:

% Way 1: Simple one
MY_file = 'DATA_Exp.xlsx';    % Note the file extension
D = readtable(MY_file);       % Reads all data from sheet1 if sheet1 contains any data

% Way 2: A bit more specific
% Reads a data from sheet called "DATA" and all data in cells A1 to D232
D = readtable(MY_file, sheet='DATA', Range = 'A1:D232'); 
 
% Way 3: Even more specific
% Reads a data from sheet called "DATA" and all data in cells A1 to D232,
% and preserves variable names from data table headers
D = readtable(MY_file, sheet='DATA', Range = 'A1:D232', VariableNamingRule='preserve'); 

%% Access Table array elements:
CELL1 = D{1,1}    % Cell 1

CELL1 = 1×1 cell array
    {'NSW1'}

COL2 = D{:,2}     % Column 2 

COL2 = 231×1 datetime array
   01-Jan-2019 00:30:00
   01-Jan-2019 01:00:00
   01-Jan-2019 01:30:00
   01-Jan-2019 02:00:00
   01-Jan-2019 02:30:00
   01-Jan-2019 03:00:00
   01-Jan-2019 03:30:00
   01-Jan-2019 04:00:00
   01-Jan-2019 04:30:00
   01-Jan-2019 05:00:00
   01-Jan-2019 05:30:00
   01-Jan-2019 06:00:00
   01-Jan-2019 06:30:00
   01-Jan-2019 07:00:00
   01-Jan-2019 07:30:00
   01-Jan-2019 08:00:00
   01-Jan-2019 08:30:00
   01-Jan-2019 09:00:00
   01-Jan-2019 09:30:00
   01-Jan-2019 10:00:00
   01-Jan-2019 10:30:00
   01-Jan-2019 11:00:00
   01-Jan-2019 11:30:00
   01-Jan-2019 12:00:00
   01-Jan-2019 12:30:00
   01-Jan-2019 13:00:00
   01-Jan-2019 13:30:00
   01-Jan-2019 14:00:00
   01-Jan-2019 14:30:00
   01-Jan-2019 15:00:00

COL34 = D{:,3:4}  % Column 3 and 4

COL34 = 231×2

1.0e+04 * 0.7458 0.0067 0.7243 0.0069 0.6919 0.0073 0.6677 0.0070 0.6513 0.0067 0.6400 0.0064 0.6317 0.0062 0.6242 0.0050 0.6229 0.0054 0.6198 0.0050

% Alt Ways:
Cell01 = D.Reg(1) % Cell 1

Cell01 = 1×1 cell array
    {'NSW1'}

Col02 = D.Time_Day(:) % Column 2 

Col02 = 231×1 datetime array
   01-Jan-2019 00:30:00
   01-Jan-2019 01:00:00
   01-Jan-2019 01:30:00
   01-Jan-2019 02:00:00
   01-Jan-2019 02:30:00
   01-Jan-2019 03:00:00
   01-Jan-2019 03:30:00
   01-Jan-2019 04:00:00
   01-Jan-2019 04:30:00
   01-Jan-2019 05:00:00
   01-Jan-2019 05:30:00
   01-Jan-2019 06:00:00
   01-Jan-2019 06:30:00
   01-Jan-2019 07:00:00
   01-Jan-2019 07:30:00
   01-Jan-2019 08:00:00
   01-Jan-2019 08:30:00
   01-Jan-2019 09:00:00
   01-Jan-2019 09:30:00
   01-Jan-2019 10:00:00
   01-Jan-2019 10:30:00
   01-Jan-2019 11:00:00
   01-Jan-2019 11:30:00
   01-Jan-2019 12:00:00
   01-Jan-2019 12:30:00
   01-Jan-2019 13:00:00
   01-Jan-2019 13:30:00
   01-Jan-2019 14:00:00
   01-Jan-2019 14:30:00
   01-Jan-2019 15:00:00

Col034 = [D.Total, D.RRP]  % Column3 and 4

Col034 = 231×2

1.0e+04 * 0.7458 0.0067 0.7243 0.0069 0.6919 0.0073 0.6677 0.0070 0.6513 0.0067 0.6400 0.0064 0.6317 0.0062 0.6242 0.0050 0.6229 0.0054 0.6198 0.0050

[Alex K]

Thanks for the detailed answer.

But the standard syntax when working from a function does not work for me.

See my example...