I would like to define a string set in GAMS to loop to read different xlsx sheet names in gdxxrw. However, I didn't find the appropriate code in the help documentation using string formatting and String Substitution as keywords.
'''
SET
sheets /index1, index2, index3/
B /a, b, c/
;
PARAMETER
POP0(B)
POP(B)
;
LOOP(sheets,
* Write Excel data to GDX
put_utility 'exec' / 'gdxxrw.exe Input.xlsx se=0 index='sheets.tl'!a1';
$Load POP0
POP(B)=POP0(B);
execute_unload "Output.gdx"
POP
* Write GDX data to excel
put_utility 'exec' / 'gdxxrw.exe RESULT.gdx o=RESULT.xlsx index='sheets.tl'!a7';
''' I read loop in GAMS for scenario generation in excel. But GAMS report error as that '''
116 Label is unknown
141 Symbol declared but no values have been assigned. Check for missing
data definition, assignment, data loading or implicit assignment
via a solve statement.
A wild shot: You may have spurious commas in the explanatory
text of a declaration. Check symbol reference list.
300 Remaining errors not printed for this line
352 Set has not been initialized
353 The domain for this index position is unknown and the element
cannot be checked at this point. Missing data statement.
502 GDXIN file not open - ignore rest of line
509 Dollar control statements are processed during compilation
and NOT during execution. The use of dollar statements
that change data may be misleading when inside a LOOP
or IF statement.
''' I think the key is the apply of $Load or $loaddc. Does GAMS have a string formatting method control index sheet in quotes?
After a night of debugging, I finally solved the problem. '''
SET
sheets /index1, index2, index3/
B /a, b, c/
;
PARAMETER
POP0(B)
POP(B)
;
LOOP(sheets,
* Write Excel data to GDX
put_utility 'exec' / 'gdxxrw.exe Input.xlsx se=0 index='sheets.tl:0'!a1';
*":0" is needed
* Load data from GDX (substitution of $load)
execute_load 'data.gdx',
POP0
;
POP(B)=POP0(B);
* Load data from GDX
execute_unload "Output.gdx",
POP
;
* Write GDX data to excel
put_utility 'exec' / 'gdxxrw.exe RESULT.gdx o=RESULT.xlsx index='sheets.tl:0'!a7';
'''
Very thank Lutz!