Importing data from Excel | sets as domain of parameter

Problems with syntax of GAMS
Post Reply
sidietz
User
User
Posts: 3
Joined: 1 month ago

Importing data from Excel | sets as domain of parameter

Post by sidietz » 1 month ago

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

User avatar
Renger
Posts: 633
Joined: 4 years ago

Re: Importing data from Excel | sets as domain of parameter

Post by Renger » 1 month ago

Hi Simon

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

Cheers
Renger
____________________________________
Enjoy modeling even more: Read my blog on modeling at The lazy economist

sidietz
User
User
Posts: 3
Joined: 1 month ago

Re: Importing data from Excel | sets as domain of parameter

Post by sidietz » 1 month ago

Hi Renger,

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

Best wishes,

Simon
Attachments
LSLR_functions_transfer.xlsx
(16.34 MiB) Downloaded 16 times

User avatar
Renger
Posts: 633
Joined: 4 years ago

Re: Importing data from Excel | sets as domain of parameter

Post by Renger » 1 month ago

Hi Simon
I am at a loss. But it isn't very important as you read lsl correctly defined over the sets.
Cheers
Renger
____________________________________
Enjoy modeling even more: Read my blog on modeling at The lazy economist

User avatar
bussieck
Moderator
Moderator
Posts: 525
Joined: 4 years ago

Re: Importing data from Excel | sets as domain of parameter

Post by bussieck » 1 month ago

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:

Code: Select all

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_Dol ... DOLLARload) feature to identify domains from data:

Code: Select all

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_SetDefi ... Definition) feature to identify domains from data:

Code: Select all

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

sidietz
User
User
Posts: 3
Joined: 1 month ago

Re: Importing data from Excel | sets as domain of parameter

Post by sidietz » 1 month ago

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

Simon

kayalvizhi
User
User
Posts: 2
Joined: 1 month ago

Re: Importing data from Excel | sets as domain of parameter

Post by kayalvizhi » 1 month ago

The quickest way to get your Excel file into SQL is by using the import wizard: Open SSMS (Sql Server Management Studio) and connect to the database where you want to import your file into. Import Data: in SSMS in Object Explorer under 'Databases' right-click the destination database, select Tasks, Import Data.
https://www.acte.in/devops-training-in-chennai]

kayalvizhi
User
User
Posts: 2
Joined: 1 month ago

Re: Importing data from Excel | sets as domain of parameter

Post by kayalvizhi » 1 month ago

The quickest way to get your Excel file into SQL is by using the import wizard: Open SSMS (Sql Server Management Studio) and connect to the database where you want to import your file into. Import Data: in SSMS in Object Explorer under 'Databases' right-click the destination database, select Tasks, Import Data.
https://www.acte.in/devops-training-in-chennai]

Post Reply