If you want to import data from a spreadsheet, it must either contain an index sheet describing the layout of your data, or it must meet certain criteria regarding the format in which the data is provided.
Workbook without index sheet
If you do not want to create an index sheet, your Excel file must meet strict requirements:
- Only one GAMS symbol per worksheet
- The name of a sheet must be identical to the symbol name
- The first line must contain the column headers. The names of the column headers are ignored.
- All symbols must be in list view (no columns may be pivoted). The only exceptions to this rule are tables, where the last column must be pivoted.
The image below shows a valid spreadsheet for the transport model:
For scalars there are 2 possibilities:
- Either all scalar values are combined in one tab named _scalars or _scalars_out (separate sheets for input and output scalars). These tables must have 3 columns: scalar, description and value.
- Alternatively, each scalar value is stored in a separate sheet as with other symbols (tab name = symbol name). The "table" then consists only of the scalar value in cell A1. There is therefore no header.
- If you want to import command line parameters/GAMS options, the symbol name must be preceded by _gmspar_ or _gmsopt_ (see screenshot above).
- To import scalar output variables and/or equations, the sheet name must be _scalarsve_out. The table has to have 7 columns: scalar, description, level, marginal, lower, upper, scale.
Workbook with index sheet
If you want more flexibility, you must create an index sheet that describes your layout. If you are already familiar with GDXXRW, this will already be familiar to you. You can skip most of this chapter, but be aware of the differences between GDXXRW and MIRO that we list below. We encourage everyone to read the documentation of GDXXRW, especially the chapter about the index option, as it covers the topic in much greater detail than we will do here.
When the layout of your data differs from the default layout described above, you need to tell MIRO where to find what. This can be achieved by a so-called index. Here you list all the GAMS symbols that are included in your workbook as well as the layout they are in.
Note:
Unlike GDXXRW, MIRO already knows a lot about your data from the GAMS/MIRO data contract. This means that information such as the dimensions of your symbols is known to MIRO and does not need to be specified again.
The first three columns of the index range have a fixed interpretation: dataType, symbol identifier and dataRange. Even though MIRO does not need and ignores the dataType information, it is a good practice to specify it because it allows your worksheet to be read by both MIRO and GDXXRW.
The fourth and subsequent columns can be used for additional options. The column heading indicates the option name and the cell contents indicate the option value. Note that although GDXXRW allows options also in a comma-separated option=value format, MIRO does not support this.
Let's look at an example:
The first thing you may notice is the name of the index sheet: _index. Although the index could be on any sheet and in any range, MIRO can automatically recognize it if the sheet is named _index and the index table starts in the upper left corner: A1. The most important information MIRO requires is the symbol name, range (columns B and C) and one of rDim (row dimension) or cDim (column dimension). If neither rDim nor cDim is supplied, MIRO will assume a default of 1 for cDim. This means that the last column is pivoted as is the case for the parameter d:
You can see that the last column (index j) is pivoted. Also note that we can ignore rows and columns via the ignoreRows and ignoreColumns options. In this example, we want to ignore the row 16 because it does not belong to the data for d, but for the set j, which is also read from the same range (sheet1!L15:N16). This brings us to the next option: values. Each tuple in a set can have associated text. To read a set from a spreadsheet, the values option is used to indicate whether there is data, and if so, whether the data should be interpreted as associated text or as an indicator of whether or not the tuple should be included in the set. In our example here, we use the YN option, which specifies that only tuples that have a data cell that is not empty and does not contain 0, N, or No will be included. This means that in our example j only the tuple New-York is read. MIRO supports all options GDXXRW supports: auto, noData, YN, sparse and dense. See the related section of the GDXXRW documentation for more information on the other options. Below you find a list of all options supported by MIRO.
Supported options
Option |
Description |
Allowed values |
range |
Excel range: Open and closed ranges are supported. It must be of the form SheetName!TopLeft:BottomRight. If the sheet name is omitted, the first sheet in the workbook is used by default. If the BottomRight corner is omitted, the data is read until skipEmpty+1 empty rows/columns are found. If range is omitted, an open range with left upper corner A1 is assumed. |
|
rDim |
Row dimension: the number of columns in the data range that will be used to define the labels for the rows. The first rDim columns of the data range will be used for the labels. |
0..dim |
cDim |
Column dimension: the number of rows in the data range that will be used to define the labels for the columns. The first cDim rows of the data range will be used for labels. |
0..dim |
values |
How set data is handled. See the related section of the GDXXRW documentation for more details. |
auto, noData, YN, sparse, dense |
ignoreRows |
Rows to ignore |
comma-separated list of positive integers |
ignoreColumns |
Columns to ignore |
comma-separated list of positive integers or column letters |
skipEmpty |
When reading an open range, controls how many empty rows or columns signal the end of the range |
0, 1 |
squeeze |
Controls whether zeros are squeezed out/removed |
y, 1, n, 0 |
Reading scalars
In addition to reading each scalar individually as supported by GDXXRW, MIRO also supports reading in table form. To read an entire table of scalars, you must do so using the special _scalars/_scalars_out symbols for input and output scalars, respectively. In this way MIRO reads scalars grouped in a table with 3 columns: Scalar, Description, Value. The Description column can also be omitted. Let's look at our example again:
If we look at the corresponding index sheet again, we see that both the output scalar total_cost and the input scalar table _scalars are defined on this sheet. In this example, the table is pivoted (scalar names in the columns) and the Description column has been omitted.
Note that you can mix the declaration of table and individual scalars. Scalars that are missing from the table are appended if they are individually declared. If a scalar is found both in one of the special tables and individually declared, the scalar in the table takes precedence and the individually declared scalar is ignored.
Note:
Command line parameters/GAMS options are treated like normal input scalars with the special prefixes _gmspar_ and _gmsopt_.
Differences between GDXXRW and MIRO
Below we list the important differences you need to be aware of when migrating from GDXXRW to MIRO or vice versa.
- Global options are not supported by MIRO: all options are symbol-specific and must be part of the symbol declaration row
- The only special values supported by MIRO are: +Inf and -Inf. Eps, YES, NO, NA, Undef, etc. are not properly recognized when used in MIRO
- Acronyms are not supported
- The only options supported by MIRO are: rDim, cDim, dim, values, ignoreRows, ignoreColumns, skipEmpty, squeeze
- Option=value columns are not supported and will be skipped. Please specify the option names as column headers and the values as cell contents.
- skipEmpty must be specified for each symbol individually. The state of this option is not transferred to the next symbols as with GDXXRW.
- Named ranges are not supported.
- Ranges with .. instead of : as separator between TopLeft and BottomRight corner are not supported.
- skipEmpty > 1 is not supported i.e. only values 0 and 1 can be used
- It is possible to read variables/equations with MIRO. They are treated like normal parameters, with an additional dimension that must contain at least one of the following elements: level, marginal, upper, lower and scale. These do not all have to be assigned, i.e. you can skip some.
- When reading scalars, you can either declare them individually as in GDXXRW, or you can use the special symbol names _scalars/_scalars_out to read input/output scalars grouped in a table with 3 columns: Scalar, Description, Value. The Description column can be omitted. You can also mix declaration of individual scalars and scalar tables. Scalars that are missing in the scalar table are appended if they are declared individually. If an individually declared scalar is also in a scalar table, the individually declared scalar is ignored.
- In tables/variables/equations, if squeeze=1, empty cells and NA are indistinguishable
- Output scalar variables and equations must be in a single table named _scalarsve_out with dimensions: scalar, description, type, where type must not contain any elements other than: level, marginal, upper, lower, scale. Please note that the Description dimension must be present (unlike the _scalars/_scalars_out tables), but is ignored when MIRO imports it.