Import set from an excel sheet with Gams Connect

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.

image

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:
image

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)