Importing data from Excel | sets as domain of parameter

GAMS
1

Hi,

I am fairly new to GAMS. I am trying to import a large data set from Excel. I import the data successfully, but the domains of my parameter don’t seem to be recognised. My code is below. The parameter lsl should have 3 domain sets – seg, rcp_pt and t. But in the gdx file it is just lsl(,,*). Could someone show me where I am going wrong?

Many thanks,

Simon

Set seg row labels
rcp_pt row labels
t column labels;

Parameter lsl(seg,rcp_pt,t)

$onecho > task.txt
dSet=t rng=Sheet1!a1:v1 rDim=0 cDim=1
dSet=seg rng=Sheet1!a1 rDim=1 cDim=0
dSet=rcp_pt rng=Sheet1!b1 rDim=1 cDim=0
par=lsl rng=Sheet1!a1 rDim=2 cDim=1
$offecho

$call GDXXRW LSLR_functions_transfer.xlsx trace=3 @task.txt
$gdxIn LSLR_functions_transfer.gdx

2

Hi Simon

Could you attach your excel sheet, so I can check what the problem is.?

Cheers
Renger

3

Hi Renger,

Thanks for looking at this – the excel sheet should be attached to this message.

Best wishes,

Simon
LSLR_functions_transfer.xlsx (16.3 MB)

4

Hi Simon
I am at a loss. But it isn’t very important as you read lsl correctly defined over the sets.
Cheers
Renger

5

Simon, the dset gdxxrw keyword does not automatically get you the domain sets. You also need explicit load instructions to get the data into GAMS. There are lots of ways that get you to the same point. Here are a few examples:

Using gdxxrw feature to identify domains from data:

Set seg    'row labels'
    rcp_pt 'row labels'
    t      'column labels';

Parameter lsl(seg,rcp_pt,t)

$onecho > task.txt
dSet=t rng=Sheet1!a1:v1 rDim=0 cDim=1
dSet=seg rng=Sheet1!a1 rDim=1 cDim=0
dSet=rcp_pt rng=Sheet1!b1 rDim=1 cDim=0
par=lsl rng=Sheet1!a1 rDim=2 cDim=1
$offecho

$call.checkErrorLevel GDXXRW LSLR_functions_transfer.xlsx trace=3 @task.txt
$gdxIn LSLR_functions_transfer.gdx
$loadDC seg rcp_pt t lsl

Using $load x<y.dimN (see https://www.gams.com/latest/docs/UG_DollarControlOptions.html#DOLLARload) feature to identify domains from data:

Set seg    'row labels'
    rcp_pt 'row labels'
    t      'column labels';

Parameter lsl(seg,rcp_pt,t)

$call.checkErrorLevel GDXXRW LSLR_functions_transfer.xlsx trace=3 par=lsl rng=Sheet1!a1 rDim=2 cDim=1
$gdxIn LSLR_functions_transfer.gdx
$loadDC seg<lsl.dim1 rcp_pt<lsl.dim2 t<lsl.dim3 lsl

Using Domain Defining Symbol Declarations (see https://www.gams.com/33/docs/UG_SetDefinition.html#UG_SetDefinition_ImplicitSetDefinition) feature to identify domains from data:

Set seg    'row labels'
    rcp_pt 'row labels'
    t      'column labels';

Parameter lsl(seg<,rcp_pt<,t<)

$call.checkErrorLevel GDXXRW LSLR_functions_transfer.xlsx trace=3 par=lsl rng=Sheet1!a1 rDim=2 cDim=1
$gdxIn LSLR_functions_transfer.gdx
$loadDC lsl

Hope this helps,
-Michael

6

Thanks very much for your help guys, I appreciate it.

Simon