Indexing range when importing from Excel

GAMS
1

Hello everyone,

does anyone know if there is the possibility to index the value of a range when importing from excel with GDXXRW?

For example:

$call gdxxrw.exe Test.xlsx set=art rng=CO!A7:A92

I would like to store that “92” in a variable, so that i could just modify an initial (ie) scalar i and then call the range with something like A7:A(i).

Thank you for your time.

Cheers, Leonardo

2

Since you run gdxxrw at compile time ($call) using a calculated scalar makes little sense (because the calculation happens at run time, so after the compile phase). You could store the 92 in a compile time variable:

$set art_lastrow 92
$call gdxxrw.exe Test.xlsx set=art rng=CO!A7:A%art_lastrow%

You can do some limited calculations with compile time variables, see $eval (https://www.gams.com/latest/docs/UG_DollarControlOptions.html#DOLLAReval):

$set art_lastrow 92
$call gdxxrw.exe Test.xlsx set=art rng=CO!A7:A%art_lastrow%
$eval xxx %art_lastrow%+15
$call gdxxrw.exe Test.xlsx set=xxx rng=XX!A7:A%xxx%

Gdxxrw is also nice because you can also just supply to north east corner of a range and gdxxrw stops after it got two (you can configure the number using the gdxxrw parameter skipEmpty) blank rows and to blank columns.

-Michael

3

I have been looking for a solution like this on the net for several hours, but I wasn’t able to solve my issue.
Thank you for your precious help Michael.
All the best