GDXXRW

GDXXRW is a utility to read and write Excel spreadsheet data. GDXXRW can read multiple ranges in a spreadsheet and write the data to a 'GDX' file, or read from a 'GDX' file, and write the data to different ranges in a spreadsheet.

Usage

gdxxrw Inputfile {Outputfile} {Options} [Symbols]

Parameters can also be read from a text file; the use of a file for parameters is indicated by preceding the file name with a @ (At sign.). When reading parameters from a text file, lines starting with an asterisk (*) will be ignored and act as a comment.

Parameters can also be read from an area in a spreadsheet; see Index below.

Files without a full path name are assumed to be in the current directory when using a DOS command prompt. When using the GAMS IDE, these files are assumed to be in the current project directory. The use of file names with embedded blanks is allowed as long as the file name is enclosed in double-quotes (").

Note: A small utility program is available to see if Excel is installed, to close an Excel file etc. See XLSTALK for more details. To read data from an Excel file without Excel installed see XLSDUMP.

Parameters and options

Describing the actions to be taken by gdxxrw requires passing a number parameters and options to the program. The ability of gdxxrw to process multiple actions in a single call makes the parameter passing and interpretation more complex.

There are four kind of parameters:

  1. Immediate Immediate parameters are recognized and processed before any other actions are taken and can only be specified once. Examples are:

    input= output= trace=

  2. Global Global parameters are interpreted from left to right and affect every action that follows. The same parameter can be used multiple times to affect the actions that follow. Examples are:

    SkipEmpty= EpsOut=

  3. Symbol A symbol definition introduces a new action for reading or writing a symbol. Examples are:

    par= set= dset=

  4. Symbol attributes Attributes specify additional information for the last symbol defined

    dim= cdim= merge clear etc

Options (Immediate)

Immediate options are command line options recognized independent of their position on the command line. These options are global and they can only be specified once.

Inputfile (Required parameter)

FileName: Is the first parameter on the command line
Or
Input = FileName
or I = FileName

The file extension of the input file is required and determines the action taken by the program.

The extension '.gdx' for the input file will read data from a 'GDX' file and write data to a spreadsheet. The extension '.xls' or '.xlsx' for the input file will read a spreadsheet and write the data to a '.gdx' file. In addition to the '.xls' input file extension, the following file extensions are also valid for spreadsheet input files: '.wk1', .'wk2', '.wk3' and '.dbf'.

A file sharing conflict will arise when writing to a spreadsheet with the target file open in Excel. Either close the file in Excel before executing GDXXRW, or mark the spreadsheet as a shared workbook in Excel. To change the shared status of a workbook, use the Excel commands available under: Tools|Share Workbook.

Writing to a shared workbook can be painfully slow; simply closing the file and reopen the file after GDXXRW is finished is often a better option.

Outputfile (Optional parameter)

Output = FileName
or
O = FileName

When an output file is not specified, the output file will be derived from the input file by changing the file extension of the input file and removing any path information. The file type, file extension, depends on the installed version of Excel. Versions prior to Excel 2007 use the .xls file extension, later version use .xlsx. Excel 2007 can write .xls files, but in that case the output file has to be specified with an .xls file extension.

Logfile (Optional parameter)

Log = FileName
or
LogAppend = FileName

Specifies the filename of the log file. When omitted, log information will be written to standard output. When using GDXXRW in a GAMS model that is started from the GAMSIDE, the output will be written to the IDE process window. Using LogAppend will add the log information to the end of the file.

Password

Password = password

Specifies a password for a protected spreadsheet file

Delay after opening a spreadsheet

RWait = integer (default = 0)

Introduce a delay after opening a spreadsheet before accessing the data. This parameter can be used to work around an issue we encountered that Excel indicated it was not ready.

Check if any works needs to be done

CheckDate

CheckDate

When specified, no data will be written if the output file exists and the file date for the output file is more recent than the file date for the input file. Provides a simple check to update the output file only if the input file has changed.

Use RC notation to specify cells and ranges

UseRC

Specify that all cell and range references use RC notation. So instead of specifying the range Sheet1!A1:D6 one specifies Sheet1!R1C1:R6C4 When tracing is enabled ranges will be reported in RC notation. This is a global option and applies to all cell references.

Trace and debug output

Trace = integer (default = 1)

Sets the amount of information written to the log. Higher values will generate more output. Valid range is 0..3.

0   Minimal information is included in the output
1   Message appears telling about each GDXXRW call indicating input file, output file and execution time
2   Message appears giving the level 1 output plus workbook name, and ranges worked with.
3   Message appears giving the level 2 output plus cell addresses, and numerical or string values for every item worked with

Maximum number of duplicate records allowed for a symbol

MaxDupeErrors = integer (default = 0)

Sets the maximum number of duplicate records that is allowed when reading a spreadsheet and writing to a gdx file. The duplicate records will be reported in the logfile, and if their number does not exceed the maximum specified using this option, the gdx file will not be deleted. This is a global option and applies to each symbol read from the spreadsheet.

Updating of cells that refer to other spreadsheets

UpdLinks = integer (default = 0)

Specifies how links in a spreadsheet should be updated. The valid range is 0..3.

0   Doesn't update any references
1   Updates external references but not remote references
2   Updates remote references but not external references
3   Updates both remote and external references

Execution of Excel Auto macros

RunMacros = integer (default = 0)

This option controls the execution of the 'Auto_open' and the 'Auto_close' macros when opening or closing a spreadsheet. Valid values are 0..3.

0   Doesn't execute any macros
1   Executes Auto_open macro
2   Executes Auto_close macro
3   Executes Auto_open and Auto_close macro

Symbols

To write data to a spreadsheet or to a GDX file, one or more symbols and their associated Excel range need to be specified. See also Excel Ranges.

The general syntax for a Symbol specification is:

DataType=SymbolName {DataRange} {Dimensions} {SymbolOptions}

(Also see the Text directive below to write text to a spreadsheet)

DataType

Par = GAMS_Parameter

Specify a GAMS parameter to be read from a GDX file and written to spreadsheet, or to be read from a spreadsheet and written to a GDX file.

When writing to a spreadsheet, special values such as Eps, NA and Inf will be written in ASCII. When reading data from a spreadsheet, the ASCII strings will be used to write the corresponding special values to the GDX file.

Equ = GAMS_Equation
Var = GAMS_Variable

A sub-field of a variable or equation can be written to a spreadsheet and should be specified as part of the SymbolName. The fields recognized are .L (level), .M (marginal), .Lo (lower bound), .Up (upper bound), .Prior (priority), and .Scale (scale). The sub-field names are not case sensitive.

A sub-field of a variable or equation cannot be read from a spreadsheet and written to a GDX file.

Set = GAMS_Set [Values = ValueType]

In GAMS we can define a set by specifying all its elements. In addition, each tuple can have an associated text. To read a set from a spreadsheet, the values option is used to indicate if there is any data, and if there is, if the data should be interpreted as associated text or as an indicator whether the tuple should be included in the set or not.

ValueType Interpretation
Auto Based on the range, row and column dimensions for the set, the program decides on the value type to be used. This is the default for Values
NoData There is no data range for the set; all tuples will be included
YN Only those tuples will be included that have a data cell that is not empty and does not contain '0', 'N' or 'No'
Sparse Only those tuples will be included that have a data cell that is not empty. The string in the data cell will be used as the associated text for the tuple
Dense All tuples will be included. A string in the data cell will be used as the associated text for the tuple

Due to backward compatibility ValueType=String or All are also recognized and are synonyms for ValueType=Dense. The following table summarizes which ValueType will be used when reading a Set if a value type was not specified:

Range specification Rdim = 0 Or Cdim = 0 Rdim > 0 And Cdim > 0
Top left corner only Dense YN
A block, but the data range is empty Dense YN
A block, and there is a data range Dense YN

When writing to a spreadsheet, the entire set is written to the spreadsheet and the writing of the accociated text is governed by the values option:

ValueType Interpretation
Auto If Rdim = 0 or Cdim = 0, Auto means String, otherwise Auto means YN
NoData Neither associated text nor 'Y' is written for a set element
YN A 'Y' is written for a set element
String The associated text is written for a set element. If no text is stored with set element the cell will be empty

Due to backward compatibility ValueType=Dense, Sparse or All are also recognized and are synonyms for ValueType=String.

DSet = GAMS_Set

A domain set is used to read the domain of a set from a spreadsheet row or column. Either the row or the column dimension (Rdim or Cdim) should be set to '1' to specify a row or column for the set, resulting in a one-dimensional set. Duplicate labels in the range specified do not generate an error message.

Text = "String of characters" {DataRange}
TextID = Identifier {DataRange}

Write the text to the cell specified in the DataRange. In addition, TextID will write the explanatory text of the Identifier in the cell to the right of the DataRange.

A Text directive can be followed by a Link=Address or LinkID=identifier directive. Using Link will create a hyperlink to an external page or to a cell in the spreadsheet. LinkID will create a hyperlink to the top left corner of the symbol specified. See Writing Spreadsheet using Text and Hyperlinks Example.

HText = "String of characters" {DataRange}
VText = "String of characters" {DataRange}

Write a string of characters in the horizontal direction for HText or vertical direction for VText. Text for the next cell is indicated by a comma. In order to write a comma as part of the text, the comma needs to be preceeded by a backslash.

The following options apply to the symbol preceding the option, and only affect that symbol:

DataRange (Optional)

Rng = Excel Range

The Excel Range for the data for the symbol. Note that an empty range is equivalent to the first cell of the first sheet.

Dimensions (Optional)

Dim = integer

The total dimension for the symbol

Cdim = Integer

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.

Rdim = Integer

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.

More about dimensions:

When reading data from a GDX file and writing to a spreadsheet, the dimension of the symbol is known. When reading a spreadsheet and writing to a GDX file, the dimension is not known.

The sum of Cdim and Rdim determine the dimension of the symbol. This dimension is used when writing data to a GDX file, and is used to verify the dimension of a symbol when reading from a GDX file.

When reading a GDX file, the dimension of a symbol is known, and therefore the Cdim or Rdim parameter can be omitted. If both Cdim and Rdim are omitted, the program assumes that Cdim = 1 and Rdim= dimension - 1.

ColMerge = Integer

The number of columns that will use a previous value in that column if the cell is empty. Can only be used when reading from a spreadsheet. See ColMerge example.

Symbol Options

The options below are only valid when reading a GDX file and writing to a spreadsheet.

By default, writing data to a spreadsheet will include the row and column labels in addition to the data. The row and column labels will appear in the same order as they appear in the GDX file.

Merge

Using the 'Merge' option assumes that the row and column labels are in the spreadsheet already. For each value read from the GDX file, the location of the row and column labels is used to update the spreadsheet. Using the merge option will force the data to be presented in a given order using the row and column labels. Spreadsheet cells for which there is no matching row/column pair will not be changed. The matching of labels is not case sensitive. See Writing to Spreadsheet with Merge Option Example.

Warning: The Merge or Clear option will clear the Excel formulas in the rectangle used, even if the cells do not have matching row / column headings in the GDX file. Cells containing strings or numbers are not affected.

Clear

The clear option is similar as the Merge option, except that the data range will be cleared before any data is written. See Writing to Spreadsheet with Clear Option Example.

ReCalc = Flag (default = N)

Enable or disable the recalculations of cells inside Excel after writing to the spreadsheet. If there are many formulas in the spreadsheet the recalculation of cells can become very expensive and slowing down the writing process. By default, the recalculation is disabled and can be enabled via this option.

IntAsText = Flag (default = Y)

Unique elements that are a proper integer can be written as text or as an integer value. The default is N, which will write the unique element as a string. Note that this impacts the sorting order and can be used when using an Excel filter on a data range.

Index = Excel Range

The Index option is used to obtain the parameters by reading from the spreadsheet directly. The parameters are read using the specified range, and treated as if they were specified directly on the command line. The first three columns of the range have a fixed interpretation: DataType, Symbol identifier and Data range. The fourth and following columns can be used for additional parameters. The column header contains the keyword when necessary, and the Cell content is used as the option value. See Reading Spreadsheet with Index Option Example.

Ignoring Rows and Columns when reading from a spreadsheet

IgnoreRows=rownr, rownr, rownr:rownr
IgnoreColumns=colnr, colnr, colnr:colnr

Row numbers are represented by integers. Column numbers are represented by Excel column numbers , like A, CD, IV etc, or by integers.

Note
  • Ignoring rows or columns is only allowed when reading a spreadsheet
  • The specification of ignored rows or columns follows the symbol specification and only applies to that symbol.
  • When ignoring a column that would be part of an index if the column was not ignored, the range for the index will be extended for each column that is ignored. The same holds for ignored rows that are part of an index.

See Ignore Rows and Columns Example.

Options

The following options affect the symbols that follow the option. They remain in effect unless they are used again for another symbol.

Acronyms = integer (default = 0)

A non-zero value indicates that acronyms can be expected and should be processed.

If no acronym processing takes place, reading an identifier in the data section of a sheet will generate an error. Writing an acronym to a sheet will write the internal numerical representation of the acronym.

Processing acronyms:

When reading a spreadsheet, an identifier in the data section of the sheet will be interpreted as an acronym and will be written to the gdx file.

When writing to a spreadsheet, a data tuple containing an acronym will be stored using the corresponding identifier of the acronym.

CMerge = integer (default = 0)

Option indicating how to read an empty cell that is part of a merged Excel range. See CMerge example. Possible values and their interpretation are:

0   Leave the cell empty
1   Use merged value in row and column headers only
2   Use merged value in all cells

EpsOut = istring (default = Eps)

String to be used when writing the value for 'Epsilon'.

Filter = integer (default = 0)

Set the Excel filter for symbols written to Excel. Using this option when reading an Excel file will result in an error. Specifying filter=1 will set an Excel filter for the row of labels labels that are closest to the data values. When there are multiple rows in a column header (CDIM > 1) we can specify a filter=2 indicating to use a row away from the data values. See filter example.

IncRC = flag (default = N)

Valid only when reading a spreadsheet.

Include Excel row and column indices when a symbol is written to the gdx file. For example, when we write a parameter P with indices I and J, without this option it will be written a P(I, J). When IncRC is enabled, the parameter will be written as P(Excel_Rows, I, Excel_Columns, J). Note that the sets Excel_Rows and Excel_Columns will be added the gdx file automatically.

MinfOut = string (default = -Inf)

String to be used when writing the value for 'Negative infinity'.

NaIn = string

String to be used when reading a value for 'Not available'; this string is used in addition to the string 'NA' and is not case sensitive.

NameConv = flag
or
NC = flag

The naming convention parameter is used to change the interpretation of an Excel range that does not contain an '!' (exclamation mark). For details see Ranges below. The default value is false.

NaOut = string (default = NA)

String to be used when writing the value for 'Not available'.

PinfOut = string (default = +Inf)

String to be used when writing the value for 'Positive infinity'.

ResetOut

Reset the output strings for special values to their defaults.

Squeeze = flag (default = Y)
or
SQ = flag

Writing to a spreadsheet:

The squeeze option affects the writing of sub-fields of variables and equations. A value for the field that is the default value for that type of variable or equation will not be written to the spreadsheet. For example, the default for .L (Level value) is 0.0, and therefore zero will not be written to the spreadsheet. When we set SQ=N, all values will be written to the spreadsheet.

Reading a spreadsheet:

When the squeeze option is enabled, zero values for parameters will not be written to the GDX file. When the squeeze option is disabled, zero values will be written to the GDX file. In either case, empty cells, or cells containing blanks only, will never be written to the GDX file.

SkipEmpty = integer (default = 1)
or
SE = integer

The SkipEmpty option can be used when reading a spreadsheet, and the range is specified using the top left corner instead of a block range. The value defines the number of empty row or column cells signal the end of a block. Valid values are 0..n.

See Skipping Empty Rows and Columns Example.

UndfOut = string (default = Undf)

String to be used when writing the value for 'Undefined'.

AllUELs = flag (default = Y)

Valid only when reading a spreadsheet.

When enabled, all unique elements found in a range will be entered in the GDX file. When disabled, only those unique elements that are used in conjunction with a value will be entered in the GDX file.

ZeroOut = string (default = 0)

String to be used when writing the value for 'Zero'; by default this is '0'.

Syntax elements

Element Description
integer An unsigned integer
string A string of characters; a string can be quoted with single or double quotation marks.
flag True values: 1, Y or Yes
False values: 0, N or No
(not case sensitive)

Ranges

An Excel Range is specified using the standard Excel notation: SheetName!CellRange.

When the SheetName! is omitted, the first sheet will be used. A CellRange is specified by using the TopLeft:BottomRight cell notation like A1:C12. When ':BottomRight' is omitted, the program will extend the range as far down and to the right as possible. (Using '..' in stead of ':' is supported.)

Excel also allows for named ranges; a named range includes a sheet name and a cell range. Before interpreting a range parameter, the string will be used to search for a pre-defined Excel range with that name.

When writing to a spreadsheet and a sheet name has been specified that does not exist, a new sheet will be added to the workbook with that name. Reading a spreadsheet and using an unknown range or sheet name will result in an error.

The following table summarizes all possible input combinations and their interpretation:

Input Sheet used Cell(s) used
First sheet A1
! First sheet A1
Name First sheet Name When nc=0
Name Name A1 When nc=1
Name! Name A1
!Name First sheet Name
Name1!Name2 Name1 Name2

Return Codes

On success, GDXXRW will return 0 as exit code. However, there might be an error which will be signaled with a specific return code in addition to an error message.

Return Code Interpretation
0 No error
1 Cannot write log
2 GDX error
3 No input file
4 Input file notfound
5 Bad parameter
6 Read error
7 Problem loading GDX DLL
8 Symbol not found
9 Dimension different
10 Types different
11 Bad uels
12 Bad output file
13 Problem opening Excel
14 Problem writing to Excel
15 Problem reading from Excel
16 Duplicate entry
17 Cannot add sheet
18 Bad cell value
19 Dimension conflict
20 Data exceeds range
21 Data exceeds range
22 Deprecated
23 Progrma aborted
24 Merge range empty
25 Too many columns skipped
26 Too many rows skipped

Warning

Warning: When executing gdxxrw.exe twice and redirecting output to the same log file may result in a fatal error.

For example:

Gdxxrw step1 parameters > logfile
Gdxxrw step2 parameters > logfile

The execution of step2 may fail, because Excel will close the logfile in step1 in a delayed fashion, but return control to gdxxrw.exe immediately. Using the Log or LogAppend parameter will avoid this problem.

Examples

Reading Set from Spreadsheet

Assuming we want to read set elements from the spreadsheet file test.xls and write the data to test.gdx.

loadrowofsets.png

Either of the following 2 statements below loads row 2 of set elements from the spreadsheet above:

gdxxrw test.xls set=i1 Rng=Sheet1!a2:c2 cdim=1
gdxxrw test.xls dset=i1a Rng=Sheet1!a2:c2 cdim=1

The sheet name in a range can be omitted when it refers to the first sheet. When the output file is not specified, the output file will be derived from the input file by changing the file extension of the input file and removing any path information.

On the other hand if we want to read set elements listed in a column:

loadcolumnofsets.png

Either of the following 2 statements loads column A of set element from the spreadsheet above:

gdxxrw test.xls set=j1 Rng=a35:a37 rdim=1
gdxxrw test.xls dset=j1a Rng=a35:a37 rdim=1

Reading Set only from Spreadsheet with Data or Text

Suppose a spreadsheet contains a potential list of elements names and we want to load those element names associated with nonzero data or yes such as the section below:

loadsets_datatext.png

The following statement reads i2 set containing only the elements boats and watercraft since the other two elements are associated with a blank or a no.

gdxxrw test.xls set=i2 rng=a5:d6 cdim=1 values=yn

Reading Set from Data Tables

One may wish to load set elements from a data table. Given a spreadsheet segment like the following:

loadsetsfromtables.png

We can take the set across the top with any of the following:

gdxxrw test.xls set=i6 Rng=b20:d20 cdim=1
gdxxrw test.xls dset=i6a Rng=b20:d20 cdim=1
gdxxrw test.xls set=i6c Rng=b20:d21 cdim=1

We can also take a set vertically from Column A as follows:

gdxxrw test.xls dset=j4 Rng=a21:a23 rdim=1

Reading Set from Lists with Duplication

One may wish to extract set elements from a spreadsheet where there is no unique list of elements that can be read but rather a list where the name is repeated. In the example below note that in rows 26 and 27 there are set elements names but they are duplicated:

loadsetsfromlists.png

One can read this with DSET as follows:

gdxxrw test.xls dset=i7 Rng=sheet1!b26:e26 cdim=1
gdxxrw test.xls dset=i8 Rng=sheet1!b27:e27 cdim=1

Reading Set and Explanatory text

Explanatory text is read using the command:

gbdxxrw test.xls set=i3 Rng=sheet1!a9:e10 cdim=1

and a spreadsheet segment like the following from sheet1:

useofsets.png

where the resulting set and its explanatory text elements are

Element        Explanatory text

new york     city1
chicago      city2
boston       city3
skipme       No
skipme2      skipme2

Note here the skipme2 explanatory text is just the element name as it has a blank entry for the explanatory text.

Reading Parameter from Spreadsheet

Assuming we want to read parameter Data1 from the file test1.xls and write the data to test1.gdx.

gdxxrw01.gif

The following statement reads parameter Data1 from the spreadsheet above:

gdxxrw test1.xls par=Data1 rng=a1:d3 cdim=1 rdim=1

The sheet name in a range can be omitted when it refers to the first sheet.

Reading Parameter from Spreadsheet with Duplication

The same data as in the previous example, but organized differently. We use the Dset option to read set I (in column A) and set A (in column B).

gdxxrw02.gif

The following statement reads parameter Data2 from the spreadsheet above:

gdxxrw test1.xls par=Data2 rng=Sheet1!A1 Rdim=2 Dset=I rng=A1 Rdim=1 Dset=A rng=Sheet1!B1 Rdim=1

When using a few symbols, the command line can become too long to be practical. In such case, use a text file to hold the parameters. A parameter file can contain multiple lines to increase readability and a line starting with a '*' will be ignored.

*file example1.txt
par=Data2  rng=Sheet1!A1 RDim=2
Dset=I     rng=Sheet1!A1 Rdim=1
Dset=A     rng=Sheet1!B1 Rdim=1

A parameter file is indicated by preceding the file name with a @ (At sign.).

gdxxrw test1.xls @example1.txt
Note
  • A parameter file can contain multiple lines to increase readability.
  • When reading parameters from a text file, lines starting with an asterisk (*) will be ignored and act as a comment.
  • A parameter file can also be written during the execution of a GAMS model using the GAMS PUT facility and the subsequent gdxxrw commands must use execute command so the put file is written before it is to be read (This will the case with $call).

Reading Multi-dimensional Parameter from Spreadsheet

This example illustrates how a four dimensional parameter can be specified:

gdxxrw03.gif
gdxxrw test1.xls par=Data3 rng=Sheet1!A1:F6 Rdim=2 Cdim=2

When we specify the range as a block, an empty row or column will be ignored. When we specify the top left cell only, the SkipEmpty option can be used to ignore one or more empty rows or columns. When we specify SkipEmpty=0, and cells A7, B7, G1 and G2 are empty, the range can be specified with a top left cell only:

gdxxrw test1.xls par=Data3 rng=Sheet1!A1 Rdim=2 Cdim=2

The sheet name (Sheet1) in a range can be omitted when it refers to the first sheet.

Reading Special Values from Spreadsheet

Special values can be read and written; the division by zero error in the spreadsheet will be written as 'Undefined'.

gdxxrw04.gif

The following statement reads parameter Data4 from the spreadsheet above:

gdxxrw test1.xls par=Data4 rng=Sheet1!A1:F2 Cdim=1
Note
  • when writing to a spreadsheet, special values such as Eps, NA and Inf will be written but this can be changed. When reading data from a spreadsheet, the ASCII strings for these special character stings will be used to write corresponding special values to the GDX file.
  • Cells that are empty or zero will not be written to the GDX file.

Reading Spreadsheet with Index Option

The Index option is used to read a number of parameters and sets based on information stored in the spreadsheet itself. The first row of the range is used for column headings indicating additional parameters.

Suppose we want to read the following parameters and sets from the following spreadsheet named Sheet1:

index1.png

and the information about all parameters and sets stored the following spreadsheet myindex in the same Spreadsheet file testindex.xls as Sheet1 above :

index2.png

The following statement reads parameters and sets from the spreadsheets using Index option:

gdxxrw testindex.xls o=gdxall.gdx index=myindex!a1
Note
  • The parameters are read using the specified range, and treated as if they appeared directly on the command line.
  • In the spreadsheet the first three columns of the range have a fixed interpretation: DataType (Par, Set, Dset, Equ, or Var), Item name identifier and spreadsheet data range. The fourth and following columns can be used for additional parameters like dim, rdim, cdim , merge, clear and skipempty. The column header contains the keyword when necessary, and the Cell content is used as the option value.
  • When an entry appears in a column without a heading then it is directly copied into the Gdxxrw parameter file. Thus in the example the items in column G are directly copied into the file.
  • Rows do not have to have entries in the first three columns if one just wants to enter persistent options such as skipempty or some of the special character string redefinitions.

Writing Data to Spreadsheet

First, we create a GDX file using the GDX parameter in the GAMS call:

*file makedata.gms
set i /i1*i4/
    j /j1*j4/
    k /k1*k4/;

parameter v(i,j,k);
v(i,j,k)$(uniform(0,1) < 0.30) = uniform(0,1);

When we run this GAMS model, the file test2.gdx will be created at the end of the run.

GAMS makedata gdx=test2

Using the file test2.gdx, we can write to a spreadsheet:

Write parameter V to the first cell in the first sheet; because we only specify the top left corner of the sheet, the complete sheet can be used to store the data. We do not specify the row and column dimension, so they will default to rdim=2 and cdim=1. (See dimensions)

Before executing this example, open the Excel file (test2.xls) and use the Excel Tools menu to make this a shared notebook. After writing to the spreadsheet, use the Excel "File Save" command to verify the changes made.

gdxxrw test2.gdx par=V rng=a1

The steps above can be combined in a single GAMS model using the execute_unload and execute statements as follows:

set i /i1*i4/
    j /j1*j4/
    k /k1*k4/;

parameter v(i,j,k);
v(i,j,k)$(uniform(0,1) < 0.30) = uniform(0,1);
Execute_Unload "test2.gdx",I,J,K,V;
Execute 'GDXXRW.EXE test2.gdx par=V rng=a1';
Note
  • a workbook cannot in general be open unless you have made special provisions with an error signaled indicating a file sharing conflict will arise when the target file is open in Excel.
  • To avoid this the sharing conflict error the user must either close the file or indicate that the spreadsheet is a shared Excel workbook in using the Excel Tools Share Workbook dialogue.
  • In an open shared workbook the contents are not updated until you have done a file save in Excel.
  • Writing to a shared workbook can be painfully slow.
  • In general it is best to close the workbook.

Writing to Spreadsheet with Merge Option

When writing to a spreadsheet one can control data handling and matching using the Merge command line parameter. When 'Merge' is active the only data that will be written to the spreadsheet are those data for which the element names match row and column labels that are in the spreadsheet already. Also under Merge spreadsheet cells for which there is no matching row/column pair will not be changed.

Suppose we have Parameter Data1 in the following data1.gdx:

Parameter Data1(*,*,*) /
'brussels'.'chicago'.'ship' 6000,
'brussels'.'cleveland'.'ship' 5000,
'san francisco'.'cleveland'.'rail' 2000,
'san francisco'.'cleveland'.'ship' 2200 /;

In the following Sheet1 there is a range in a spreadsheet that appears in the range b1:g4 with similar contents as in data1.gdx:

merge1.png

use the commands:

$call "gdxxrw data1.gdx o=test2.xls  par=Data1 Rng=Sheet1!b1:g4 rdim=2 cdim=1 merge"
$call "gdxxrw data1.gdx o=test2.xls  par=Data1 Rng=Sheet1!b8 rdim=2 cdim=1"

then the resultant spreadsheet looks like:

merge2.png

where the portion in rows b8 - f12 is what happens without the merge and the part in rows 1-4 is what happened with it. Note that the column and row orders vary and the san francisco chicago row is missing since it is not mentioned in the labels before the merge operation and the horse column is present with it's data left alone.

Note
  • Using the merge option will force the data to be presented in the order in which the row and column labels are entered.
  • GDX file contents that do not have matching row/column pair of named elements in the spreadsheet will be overlooked.
  • A write under a merge option addressing a blank area of a spreadsheet will always be blank as there will not be matching set elements.
  • The matching of labels is not case sensitive.
  • Warning: The Merge option will clear the Excel formulas in the rectangle used, even if the cells do not have matching row / column headings in the GDX file. Cells containing strings or numbers are not affected.

Writing to Spreadsheet with Clear Option

When writing to a spreadsheet one can also use the Clear option to control data handling and matching. When 'Clear' is active the only data that will be written to the spreadsheet are those data for which the element names match row and column labels that are in the spreadsheet already but all data and formulas in the target range will be removed.

Suppose we have Prameter Data1 in data1.gdx from example above and there is a range in the following sheet1 that appears in the range i1:n4 with similar contents as in data1.gdx: :

clear1.png

use the command:

$call "gdxxrw data1.gdx o=test3.xls  par=moded4 Rng=Sheet1!i1 rdim=2 cdim=1 clear"

then the result is

clear2.png

which shows results similar to those under merge but the old data in the column labeled horse has been removed.

Note
  • Using the clear option will force the data to be presented in the order in which the row and column labels are entered.
  • GDX file contents that do not have matching row/column pair of named elements in the spreadsheet will be overlooked.
  • A write under a clear option addressing a blank area of a spreadsheet will always be blank as there will not be matching set elements.
  • The matching of labels is not case sensitive.
  • Warning: The Clear option will clear all Excel formulas and values in the rectangle used, even if the cells do not have matching row / column headings in the GDX file.

Reading Data from Spreadsheet and Loading into GAMS

One can use $call to execute the gdxxrw command in the gams code to read from Spreadsheet at compilation time:

$call "gdxxrw test.xls set=i9 Rng=Sheet1!b20:c21 cdim=1"

Getting a set from the spreadsheet into a GDX file is only half the battle. One must also use commands in GAMS to load the data as discussed in the chapter Using GAMS Data Exchange or GDX Files. At compile time this is done using:

set i9;
$call "gdxxrw test.xls set=i9 Rng=Sheet1!a2:c2 cdim=1"
$gdxin test.gdx
$load i9

where the set must be declared in a set statement then one can if needed create the GDX file using GDXRW, then one uses a $gdxin to identify the source and a $load to bring in the data.

Some users may wish to load sets at execution time. This is; however, limited to subsets that are dynamic sets and cannot be used in domains. To do this one simply uses the statements as above, but substitutes execute in place of $call as follows:

set i9(i6a);
* set from data
execute "gdxxrw test.xls set=i9 Rng=Sheet1!b20:c21 cdim=1"
execute_load 'test' i9;

where the set must be declared as a subset in a set statement then one can if needed create the GDX file using execution time GDXXRW, and an execute_load to bring in the data with an identification of the GDX source file name.

One can load the universe of labels from a GDX file into a set at run-time using the syntax:

execute_load 'someFile', someSet=*;
Note
in doing this, only labels known to the GAMS program will be loaded.

Unloading Data from GAMS and Writing to Spreadsheet

One must also use commands in GAMS to place the data into the GDX file as discussed in the chapter Using GAMS Data Exchange or GDX Files. When loading data it is often desirable to use $call and allows domain definitions. This is hardly ever desirable when unloading or writing to a spreadsheet. This should generally not be done at compile time so one should only use the execute command as follows:

execute_unload ' ' threedim,i,j,k,ii;
execute "gdxxrw test.gdx o=test.xls set=i Rng=output2!a1  cdim=1 "

where the execute_unload tells what data to place in the GDX file and identifies the GDX source file name. The matching gdwxrw execution tells the name of the GDX file, the name of the spreadsheet and identifies the data to unload.

Note
  • One must be careful when using gdxxrw as each time the command is executed the GDX file is erased and only has the current contents and thus should be written just before if reusing the name.
  • One also obtains output of sets using the command execute_unloaddi where the GDX file automatically includes all sets associated with unloaded parameters, variables and equations without need to list the set element names.

Reading Data from and Writing Data to Spreadsheet

In the following example, we read data from a spreadsheet and save the data in a GDX file. Using the $GDXIN and $LOAD GAMS directives, we read data from the GDX file into GAMS. The GAMS program modifies the data and at the end of the run the data is saved in a new GDX file (tmp.gdx). The last step updates the spreadsheet with the modified parameter.

The data in spreadsheet test1.xls:

gdxxrw01.gif
$call "gdxxrw test1.xls Set=I rng=A2:A3 Rdim=1 Set=A rng=B1:D1 Cdim=1 Par=X rng=A1:D3 Rdim=1 Cdim=1"
$gdxin test1.gdx
Set I(*),A(*);
$load I A
Parameter X(I,A);
$load X
Display I,A,X;
$gdxin
X(I,A) = - X(I,A);
execute_Unload 'tmp.gdx',I,A,X;
execute 'gdxxrw tmp.gdx O=test1.xls par=X rng=EX6!A1:D3 rdim=1 cdim=1';

Reading Data from and Writing Data to Spreadsheet after Solve

In this example we use a modified version of the [trnsport] model from the GAMS model library. This example illustrates:

  • Compilation phase
  • Read data from a spreadsheet and create a gdx file
  • Reading sets from the gdx file
  • Using the sets as a domain for additional declarations
  • Reading additional data elements
  • Execution phase
  • Solve the model
  • Write solution to a gdx file
  • Use gdx file to update spreadsheet
gdxxrw09.gif
Data for trnsport model
$onecho > taskin.txt
dset=i rng=a3:a4 rdim=1
dset=j rng=b2:d2 cdim=1
par=d rng=A2 Cdim=1 Rdim=1
par=a rng=a8 Rdim=1
par=b rng=a13 Rdim=1
par=f rng=a19 Dim=0
$offecho

$call gdxxrw.exe trnsportdata.xls @taskin.txt

$gdxin trnsportdata.gdx

sets
   i(*) 'canning plants'
   j(*) 'markets';

$load i j

display i,j;

Parameters
   a(i)  'capacity of plant i in cases'
   b(j)  'demand at market j in cases'
   d(i,j)  'distance in thousands of miles';
scalar f 'freight in dollars per case per thousand miles' ;
$load d a b f
$gdxin

Parameter c(i,j)  'transport cost in thousands of dollars per case' ;
   c(i,j) = f * d(i,j) / 1000 ;


VARIABLES
         x(i,j) 'shipment quantities in cases'
         z      'total trnasportation costs in thousands of dollars' ;

POSITIVE VARIABLE x ;

EQUATIONS
         cost            'define objective function'
         supply(i)       'observe supply limit at plant i'
         demand(j)       'satisfy demand at market j' ;

cost ..          z =e= sum((i,j),c(i,j)*x(i,j));
supply(i)..      sum(j,x(i,j)) =l= a(i);
demand(j)..      sum(i,x(i,j)) =g= b(j);

MODEL transport /all/ ;

SOLVE transport using lp minimizing z ;

DISPLAY x.l, x.m ;

execute_unload 'trnsportdata.gdx', x;
execute 'gdxxrw.exe trnsportdata.gdx var=x.l rng=sheet2!a1' ;
gdxxrw10.gif
Solution written to sheet2

Writing Spreadsheet using a Filter

The following example creates a small gdx file; the gdx file is used to write the symbol A to a spreadsheet with the filter enabled.

set i /i1*i2/
    j /j1*j2/
    k /k1*k2/;
parameter A(i,j,k);
A(i,j,k)=uniform(0,1);
execute_unload 'test.gdx', A;
execute 'gdxxrw.exe test.gdx filter=1 par=A rdim=1 cdim=2 rng=sheet1!a1';
gdxxrw11.gif

The screenshot above shows the filter in Excel. When we specify filter=2 in this example with two dimensions for the column header, the row with the filter moves away from the data range as illustrated below.

filter2.png

Writing Spreadsheet using Text and Hyperlinks

The following example illustrates the use of the Text directive.

First we write some data to a gdx file and we use text directive to write text to various cells; some of the cells are hyperlinks to other locations.

$onecho > task.txt
text="Link to data" rng=Index!A2 linkid=A
text="Below the data for symbol A" rng=data!c2
par=A rng=data!c4
text="Back to index" rng=data!a1 link=Index!A1
text="For more information visit GAMS" rng=data!c1 link=http://www.gams.com
$offecho

set i /i1*i9/
   j /j1*j9/;
parameter A(i,j);
A(i, j) = 10 * Ord(i) + Ord(j);
execute_unload "pv.gdx";
execute 'gdxxrw pv.gdx o=pv.xls @task.txt'

Below a screen shot showing the sheet 'data' created by the commands above:

gdxxrw12.jpg

ColMerge Example

Using ColMerge to read from a spreadsheet:

colmerge.png

Reading the above spreadsheet using the following GAMS statement:

$call gdxxrw file.xls par=A rng=B2 rdim=3 cdim=1
colmerge2.png

Results in empty cells B4, B5, C4 and D5. Using the ColMerge parameter, we use the non-empty content of the previous cell in the same column as the content for the empty cell. Specifying ColMerge=2, will do this for the first two columns.

$call gdxxrw file.xls par=A rng=B2 rdim=3 cdim=1 ColMerge=2
colmerge3.png

CMerge Example

Using CMerge to read merged column headers:

cmerge1.png
Spreadsheet with merged cells

Note that the label 'red' is centered over columns B, C and D and label 'green' over columns E and F. We can read this spreadsheet using the following call:

$call gdxxrw file.xls cmerge=1 par=A rng=A1 rdim=1 cdim=2

And display the file in the GAMSIDE:

cmerge2.png
Symbol A displayed in GAMSIDE

Skipping Empty Rows and Columns Example

We can control the way blank rows or columns are handled and causes GDXXRW to either stop or skip over or stop when a blank row or column is encountered.

skipempty.png

We can read this spreadsheet and skip blank rows and columns with the following call to gdxxrw:

$call gdxxrw gdxxrwss.xls gdx par=moded4 Rng=skipempty!a2:g69

or

$call gdxxrw test.xls se=1 gdx par=moded4 Rng=skipempty!a2:g69

After loading into GAMS the data become:

                               ship       truck        rail
brussels     .cleveland    5000.000
brussels     .chicago      6000.000
san francisco.cleveland                2200.000    2200.000
san francisco.chicago                  2000.000    2000.000

On the other hand if skipempty is set to zero

$call gdxxrw test.xls se=0  par=moded3 Rng=skipempty!a2:g69 rdim=2 cdim=1

the blanks terminate the read not reading the rail column and the san francisco.chicago row and the result is:

                               ship       truck
brussels     .cleveland    5000.000
brussels     .chicago      6000.000
san francisco.cleveland                2200.000
Note
The skipempty parameters must appear before any parameter, set, dset etc statements that use it and will persist for the rest of the statements in a command unless it is set to another value.

Ignoring Rows and Columns Example

We can use the options IgnoreColumns and IgnoreRows to ignore columns and or rows when reading data for a symbol. The following sheet uses the red colored columns and rows we want to ignore:

ignore1.png
Red columns and rows to be ignored

We can read this spreadsheet and ignore the red colored columns and rows with the following call to gdxxrw:

$call gdxxrw test3.xlsx par=A rng=C1 Cdim=2 Rdim=2 IgnoreRows=2,6 IgnoreColumns=D,G

After opening the test3.gdx file in the gamside and rearranging the display, we see:

ignore2.png
gdx file displayed in the gamside

In the example above we ignored column D which would have been part of the index for the rows. So the range for the row index was extended with column 'E'. The 'E' column is no longer part of the data range. The treatment of the column index is similar. Row '2' would have been part of the column index, and now that the row is ignored, the next row becomes part of the column index and row '3' is no longer part of the data range.

Note
The IgnoreRows and IgnoreColumns parameters appear after any parameter, set, dset etc GDXXRW command instruction and only affect reading of that item.

Changes in the Set Values parameter

The following documents some changes that were made when reading a Set using the Values=Strings option. Reading a Domain or a parameter was not affected by these changes.

To illustrate the various behaviors in different versions of GDXXRW, we are using the spreadsheet data as shown below, using the following call:

$call gdxxrw.exe test.xlsx set=one rng=b2 rdim=1 values=string set=two rng=b1 rdim=1 cdim=1 values=string
setvalues1.png
Spreadsheet data to illustrate Set Values

We read the one dimensional set in column B by specifying the top-left corner of the data (cell B2) or the full range (B2..B5). A two dimensional set is read using the top-left corner of the data (cell B1) or the full range (B1..E5). Variations are introduced by specifying options for Values to be String or YN or NoData. The option value All is only available in later versions of GDXXRW and was used to introduce the same behavior as Strings in earlier versions.

Chronological description of the changes made to the Values option:

  • GAMS versions prior to version 24.3:

    Values=String results in reading the data dense. The contents of a cell is used for the set associated text and an element is included even if the data cell is empty.

    gdxxrw01a.png
    gdxxrw01b.png

  • GAMS version 24.3.1

    We changed the interpretation of String to mean that the set element was only to be included when the string data was not empty. Note below that element a4 is missing from the one-dimensional set and so are a1.b1, a2.b2 etc from the two-dimensional set. For the two-dimensional case this looked more or less how the GAMS compiler interprets a table statement. Unfortunately, the interpretation of empty data cells was also applied to one-dimensional sets leading to undesired results.

    gdxxrw02a.png
    gdxxrw02b.png

  • GAMS version 24.4.1

    Recognizing that reading a set dense was no longer available, we introduced a new option Values=All. This allowed us to read Excel data the same way as was possible before version 24.3.1 using the Values=String i.e. reading the data dense and including all cells whether the data cell is empty or not and use the content of the data cell for the set associated texts.

  • GAMS version 24.4.6 (Current status)

    We decided to remove some confusion with the interpretation of the Values option by introducing the options Dense and Sparse and flagging the options Strings and All as deprecated. Both Strings and All are replaced with Dense.

Backward compatibility issues.

With these changes we broke our in house rule not to introduce changes that break backward compatibility. Because of this, the user needs to change the parameters for the GDXXRW call or change the workbook data. The parameters for the call should be changed from Values=String to Values=Dense for one-dimensional sets where we specify the top-left corner only. In case the data in the workbook needs to be changed, inserting a string to in the data cell will address the issue. In the example on the top of the page, inserting a 'Y' in cell C5.