Saturday, May 28, 2016

Reading a spreadsheet

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'?

Let’s make some data as follows:

image image image

We can read this as follows:

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

The output will look like:

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

It is quite slow however as we do a call to gdxxrw for each sheet (we usually prefer to read all data into a single GDX file using just one invocation of gdxxrw).