Importing only certain columns from a spreadsheet into GAMS

Frequently asked questions about GAMS

Moderator: aileen

Forum rules
Please ask questions in the other sub-forums
Locked
aileen
User
User
Posts: 131
Joined: 1 year ago

Importing only certain columns from a spreadsheet into GAMS

Post by aileen » 1 year ago

How do I import certain columns from a spreadsheet into GAMS?

aileen
User
User
Posts: 131
Joined: 1 year ago

Re: Importing only certain columns from a spreadsheet into GAMS

Post by aileen » 1 year ago

Sometimes one has to import certain columns from a spreadsheet into GAMS, which are not connected. Also the layout of the data may require some work to get it in a form suitable for GAMS. Here is an example:
ws.png
The simplest way to get these data into GAMS is to import it "raw" and to do all the work on the data within GAMS.

Code: Select all

set p     /'p=1'*'p=50'/;
parameter rawdata(p,*) 'complete work sheet'
$call"=gdxxrw i=testdaten_GAMS.xls o=data.gdx par=A rng=Tabelle1!a2"
$gdxin data
$load rawdata=A
display rawdata

Code: Select all

----      6 PARAMETER rawdata  complete work sheet

           A(kp)       E(kp)    B(a=1,2)  B(a=3,…,7)         a=1         a=2         a=4         a=5         a=6           V      U(1kp)      U(2kp)      U(4kp)      U(5kp)      U(6kp)         a=3      U(3kp)         a=7      U(7kp)

p=1    14090.000      11.500       0.777       1.625       1.000       1.000       1.000       1.000       1.000       1.000       1.000       1.000       1.000       1.000       1.000
p=2      505.000      14.500       0.728       1.471       1.000       1.000       1.000       1.000       1.000       1.000       1.000       1.000       1.000       1.000       1.000
p=3       60.000      21.500       0.805       1.682       1.000       1.000       1.000       1.000       1.000       1.000       1.000       1.000       1.000       1.000       1.000
p=4                                                        1.000       1.000       1.000       1.000       1.000       1.000       1.000       1.000       1.000       1.000       1.000
p=5     5845.000       9.000       0.682       1.704       1.000       1.000                                           1.000       1.000       1.000                                           1.000       1.000
...
Now we have all data available within GAMS and can easily continue from here. In most cases this approach is faster and less error-prone than using several gdxxrw statements to import data. However, if it is just about skipping certain rows or columns, the GDXXRW options ignoreRows / ignoreColumns can be used. To ignore the column with the header V (Excel column M) in the example from above just change the gdxxrw call to:

Code: Select all

$call"=gdxxrw i=testdaten_GAMS.xls o=data.gdx par=A rng=Tabelle1!a2 ignoreColumns=M"

Locked