Convert spreadsheet that contains variable names and numbers into simple variables

Hello everyone,

I've been trying to convert a Excel Spreadsheet to variables. The Spreadsheet only has two columns and looks somewhat like this:

Cw     0.35
A        2.7
ig1      4.171  
ig2      2.34  
ig3      1.521
...

as you can see the variable names are written in the first coulmn and the the variable values in the second coulmn.

All i want to do is to extract those variables with the given name and value to my workspace (as a 1x1 table) since I need them that way for my Simulink model. I've tried xlsread and readtable as well as the import data button but i cant get it to output anything different than a table.

There must be a very simple solution to this problem?

Any help would be greatly appreciated. Thank you.

[madhan ravi]

instead of variable names use rownames in readtable

[TimonD]

Thank you for your answer.

Sadly that won't do it.

As you can see in the picture i want to be able to get the variable Cw (for example) to have the value that is asigned to it in the spreadsheet...

(This is what the imported spreasheet looks like if i use rownames instead of variable names. its not the original spreadseet)

[dpb]

What do you mean, precisely by " (as a 1x1 table) "? It makes no sense.

You do NOT want to "poof" (create) variables into the workspace dynamically as a general practice; what, again, specifically are you trying to do with this file and how?

[TimonD]

well i'm guessing i want to do exactly just that....

i want to have the different variables to turn up like this

because i need them to be like this for usage in a simulink modell. I cant just have a (e.g.) 21x2 table because simulink will tell me that the variables are not defined....

or to put it in other words i need the variables to be defined with the proper values as i defined them in the excel spreadsheet so that they are usable in simulink as input parameters (constants from workspace)...

[Stephen23]

You could easily use that .xlsx file to define a structure with those fields. Then within Simulink refer to that structure and its fields.

Magically creating/accessing variable names is not recommended:

https://www.mathworks.com/matlabcentral/answers/304528-tutorial-why-variables-should-not-be-named-dynamically-eval

[TimonD]

Ok Thank you. That really does make sense. I'm still struggeling to define a structure which sets the variable names to the field names tho... I've been browsing the the Matlab help and the forums but i'm still not sure how to dynamicly create the field names with my given spreadsheet. This is all i get:

i guess what i want is a 1x1 structure with 23 fields... i just don't know how to creat it. This is my code so far:

filename=FahrzeugDaten.xlsx
%% Setup the Import Options
opts = spreadsheetImportOptions("NumVariables", 2);

% Specify sheet and range
opts.Sheet = "Daten";
opts.DataRange = "B3:C100";

% Specify column names and types
%opts.VariableNames = ["Variable", "Werte"];
%opts.VariableTypes = ["string", "double"];
opts = setvaropts(opts, 1, "WhitespaceRule", "preserve");
opts = setvaropts(opts, 1, "EmptyFieldRule", "auto");

% Setup rules for import
opts.ImportErrorRule = "omitrow";
opts.MissingRule = "omitrow";
opts = setvaropts(opts, 2, "TreatAsMissing", '');

FahrzeugDaten = table2struct(readtable(filename, opts, "UseExcel", false));

Do you have any ideas on how to do this? Could you point me in the right direction?

P.S. I'm using readtable instead of XLS read becaus i need the missing row feature....

Thank you so much

Answer 1

[dpb]

OK, given the example data from the original question in an .xls file -- to build a struct with those variables as fields with the associated values:

>> [v,n]=xlsread('timon.xls');       % values double array, text names cellstr
>> s=cell2struct(num2cell(v),n,1)    % convert to struct w/ field names with values
s = 
  struct with fields:

     Cw: 0.3500
      A: 2.7000
    ig1: 4.1710
    ig2: 2.3400
    ig3: 1.5210
>> 

Or, you can use the raw data returned as cell array--

[~,~,r]=xlsread('timon.xls');
s=cell2struct(r(:,2),r(:,1),1);

will return the same struct

[TimonD]

Thank you so much!!! That is indeed quite easy... you just need to know what you're doing haha

[dpb]

:) I agree! The syntax for building a structure isn't always greatly explained from struct documentation; you have to know the helper functions exist (which means must follow the links of "See Also" to learn about them.

[TimonD]

ok, thanks for the tip!