R script to convert Excel to GDX:
- Can replace GDXXRW for Excel-to-GDX conversion.
- Accepts the same arguments and a subset of the options that GDXXRW does.
- Unlike GDXXRW, works on non-Windows platforms and does not require Office.
Note
Consider alternatives first:
- Excel files are not a good choice for storing data for automatic processing. If you have a choice, pick a more robust and standard data storage format.
- With the release of GAMS 39, the GAMS Connect framework was introduced. It provides GAMS with an alternative way to read and convert Excel files in a cross-platform-compatible manner.
- In the GAMS 41 release notes, GAMS Corp. announced that the gdxrrw package on which this script depends is deprecated.
Tests are located in the separate private xl2gdx-tests repository. That repository is private because the licensing conditions of the corner-case input Excel sheets included with the tests are diverse and were not reviewed. To request access to the tests repository, email the author or post an issue in the issue tracker.
For further information read the header comments in the script and see the GDXXRW documentation.
This is not an R package, instead xl2gdx.R is a utility script that can be
invoked with command line parameters. Just copy it to a handy location. The
same holds for the project_to_ASCII.R helper script. The dependencies listed
below should first be installed though, and some environment variable may need
to be set.
Carefully read the instructions below, heed the warnings, and follow the links where needed.
The R language runtime is required to run xl2gdx.R.
After installation, ensure that R and Rscript are on-path by adding the right
installation subdirectory to the PATH environment variable. On Windows, this
directory ends in R-x.y.z\bin\x64 for the 64-bit binaries, where x.y.z is the
R version.
Warning
When installing R, old R versions are not automatically removed. Having
multiple R versions installed can cause confusion. Remove any older R installations unless you
have good reasons to keep it. Be sure to delete any references to a removed R installation
as could be present in the PATH, R_GAMS_SYSDIR, and other environment variables.
Note
After updating R, you will need re-install R packages and update the environment variables that point to the R installation directory since the path includes the version number.
Important
When you use RStudio and update R, you should make sure that RStudio is using the new R installation by configuring it under Tools >> Global options ... >> General.
The tidyverse curated R package collection must be installed. You can do so from the R prompt by issuing:
install.packages("tidyverse")Alternatively, you can use the RStudio package management tab, but make sure that RStudio is using the on-path R installation.
gdxrrw is an R package for
reading/writing GDX files from R. It is required by xl2gdx.R.
You cannot install it from CRAN, you have to install it from GitHub as per
these instructions.
To make gdxrrw find the GAMS system directory
containing the GDX libraries that it needs to read/write GDX files, you
can use the sysdir command line option (see below) or make sure a
sufficiently recent GAMS installation directory is included in either the
PATH (on Windows), or LD_LIBRARY_PATH (on Linux), or DYLD_LIBRARY_PATH
(on MacOS) environment variable.
However, it is strongly recommended to instead make your environment
configuration explicit and purpose-specific by setting the
gdxrrw-dedicated environment variable R_GAMS_SYSDIR to point to
a GAMS installation directory. For reasons explained below, it is best
to point to the most recent version of GAMS that you have installed.
See here
for guidance on how to set environment variables.
Warning
Changed environment variables are not picked up until you
restart a process. Therefore, after changing one of the above-mentioned
environment variables, first restart your command prompt, shell, GAMS
IDE or GAMS Studio before testing the installation or invoking
xl2gdx.R.
If you use an environment variable to point to the GAMS installation directory, the following should work and report the used environment variable:
$ R
> library(gdxrrw)
> igdx(gamsSysDir='')Warning
The above will result in an error with recent versions of gdxrrw unless you point gdxxrrw at a GAMS 33 or newer installation directory as per the above instructions. The reason is that gdxrrw has switched to using an improved GDX API that is available as of GAMS 33. You may therefore need to install a newer GAMS version and point gdxrrw at it.
Tip
On Windows, it will likely prevent problems when you first install Rtools so that you can compile the gdxrrw and other R packages from source.
Caution
When installing Rtools 4.0 (the version compatible with R 4.0.x or 4.1.y), make sure to not skip the Putting Rtools on the PATH step listed in its installation instructions. Later versions of Rtools do not require this step.
Note
On Windows without Rtools, you should download a binary gdxrrw package that matches your R version. For a list of which binary package versions match what R versions, see the gdxrrw wiki.
project_to_ASCII.R depends on:
- The tidyverse curated R package collection.
Both xl2gdx.R and project_to_ASCII.R can be invoked via the
Rscript utility
that is part of the R installation. When you followed the instructions in the prior
section, the directory containing the Rscript utility will in your PATH
environment variable so that you can use Rscript from a command prompt or shell.
To invoke xl2gdx.R from the command line or shell, issue:
[Rscript ]xl2gdx.R <Excel file> [options] [@<options file>] [symbols]
The supported options are listed below. Details for most options are given in the GDXXRW manual.
On Linux/MacOS, you can omit the leading Rscript since Rscript will invoked
automatically via the shebang header
present in the script.
When replacing a GDXXRW invocation in your GAMS code with xl2gdx.R, you will typically
have a
$call or
execute statement
that invokes GDXXRW. Unless unsupported options are used, it should be possible to
replace the GDXXRW or <path to GAMS dir>/GDXXRW part of that invocation with
Rscript <relative path to xl2gdx.R>/xl2gdx.R and things should work. To verify,
the output of both invocations can be compared with
GDXDIFF.
output=<GDX file>If omitted, output to<Excel file>but with a.gdxextension. The synonymois supported, but its use is discouraged.index='<sheet>!<start_colrow>'squeeze=<Y|N>If Y, do not write zero values. Defaults to Y.sysdir=<GAMS system directory>When omitted, the GAMS installation directory must be reachable via an environment variable (see above).maxdupeerrors=<max>
dset=<name of domain set to write>par=<name of parameter to write>set=<name of set to write>
cdim=<number of column dimensions>rdim=<number of row dimensions>rng='[<sheet>!]<start_colrow>[:<end_colrow>]'⚠️ Unlike GDXXRW sheet names are case sensitive.project=<Y|N>Project Latin special characters to ASCII forpar=symbols. Defaults toN.
Project a windows-1252 or ISO-8859-1 encoded text file to ASCII.
Intended to remove special characters from source files. Can be used
in conjunction with he project=Y feature of xl2gdx.R to locate and
convert special-character references to data in GAMS source files.
Caution
This tool operates in-place, apply it only to source files under version control so that you can review and revert the changes.
To invoke project_to_ASCII.R, issue:
[Rscript ]project_to_ASCII.R <text file with special characters>
This projects the given text file to ASCII when possible, replacing it in-place.
When using xl2gdx.R produces this error, upgrade the Rcpp package to version 1.0.7 or higher.
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent