For a project, I want to load sets from an Excel sheet, but I am struggling with this. I know that I can only import the set using the unprotected table, but I’m wondering if it’s possible, as it was with gdxxrw, to load the set from an Excel sheet without an additional table.
Set city;
$onEmbeddedCode Connect:
- ExcelReader:
file: sets_city.xlsx
symbols:
- name: city
range: city!A2:A5
type:set
- GAMSWriter:
writeAll: True
$offEmbeddedCode
It is associated with the following Excel sheet.
Do you have an idea that could be used to solve this kind of problem?
Mathieu,
I don’t understand what you mean with “unprotected table” and loading “the set from an Excel sheet without an additional table”. Perhaps you could provide more details are even better - a complete example for what you want to do.
For the actual code at hand, there are a few problems. First of all type:set
needs to get an extra space in order to become valid YAML syntax: type: set
.
The ExcelReader default for both rowDimension
and columnDimension
is 1
. In order to read a 1-dimensional set with all set elements in the rows, you have to specify columnDimension: 0
. The complete (GAMS 48 compatible) example looks like this:
Set city;
$onEmbeddedCode Connect:
- ExcelReader:
file: sets_city.xlsx
symbols:
- name: city
range: city!A2:A5
columnDimension: 0
type: set
- GAMSWriter:
symbols: all # GAMS 48
# writeAll: True # GAMS 47
$offEmbeddedCode
display city;
Note that with GAMS 47, you need to use the writeAll
option, but this has been replaced by symbols: all
with GAMS 48. The used Excel file - identical to your screenshot - is attached: sets_city.xlsx (8.5 KB)
Best,
Clemens
Thanks for the answer, the code works for what I want to do.
Regarding “unprotected table”, I thought it was when you use “<” in the definition of the parameter, as in this example:
Mathieu,
Great that this is now working for you.
What you are referring to is what is called Implicit Set Definition. You can do this with Connect as well. The attached example loads a 3-dimensional parameter p
from Excel and makes it available to GAMS by using the GAMSWriter. And since p
uses the implicit set definition syntax for i
, j
, and k
, those get the corresponding set elements automatically.
Best,
Clemens
implicit_set_definition.gms (224 Bytes)
p_data.xlsx (8.5 KB)