Indexing range when importing from Excel Topic is solved

Problems with syntax of GAMS
Post Reply
vigoleo
User
User
Posts: 6
Joined: 1 month ago

Indexing range when importing from Excel

Post by vigoleo » 4 weeks ago

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:

Code: Select all

$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

User avatar
bussieck
Moderator
Moderator
Posts: 208
Joined: 2 years ago

Re: Indexing range when importing from Excel

Post by bussieck » 4 weeks ago

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:

Code: Select all

$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_Dol ... DOLLAReval):

Code: Select all

$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

vigoleo
User
User
Posts: 6
Joined: 1 month ago

Re: Indexing range when importing from Excel

Post by vigoleo » 4 weeks ago

bussieck wrote:
4 weeks ago
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:

Code: Select all

$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_Dol ... DOLLAReval):

Code: Select all

$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
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

Post Reply