excelloopsmathematical-optimizationgams-math

How to import data from multiple excel sheets in GAMS using loops?

I want to import the data for a three-dimensional parameter p(i,j,k) that is stored in in k excel sheets but GAMS does not let me use dollar control statements in loops. Is there any way to do that using loops or other flow control statements like 'for' or 'while'?

I need to do something like this but it is seemingly impossible:

loop(k,
$call gdxxrw Data.xlsx par=temp rng=k!A1:Z20 rdim=1 cdim=1
$gdxin Data.gdx
$load temp
$gdxin
p(i,j,k)=temp(i,j);
);
Solution

  • Suppose each sheet looks like:

    enter image description here

    (only difference is I use 2's in sheet2 and 3's in sheet3).

    To read this do:

    $set xls  d:\tmp\test2.xlsx
$set gdx  s.gdx

set
  i /i1*i3/
  j /j1*j5/
  k 'sheet names' /Sheet1*Sheet3/
;

parameter
  s(i,j)  'single sheet'
  a(i,j,k)  'all data'
;

file f /task.txt/;
loop(k,
  putclose f,'par=s rng=',k.tl:0,'!a1 rdim=1 cdim=1'/
  execute 'gdxxrw i=%xls% o=%gdx%  @task.txt trace=2';
  execute_loaddc '%gdx%',s;
  a(i,j,k) = s(i,j);
);

display a;

    My results are:

    ----     23 PARAMETER a  all data

           sheet1      sheet2      sheet3

i1.j1       1.000       2.000       3.000
i1.j2       1.000       2.000       3.000
i1.j3       1.000       2.000       3.000
i1.j4       1.000       2.000       3.000
i1.j5       1.000       2.000       3.000
i2.j1       1.000       2.000       3.000
i2.j2       1.000       2.000       3.000
i2.j3       1.000       2.000       3.000
i2.j4       1.000       2.000       3.000
i2.j5       1.000       2.000       3.000
i3.j1       1.000       2.000       3.000
i3.j2       1.000       2.000       3.000
i3.j3       1.000       2.000       3.000
i3.j4       1.000       2.000       3.000
i3.j5       1.000       2.000       3.000