GAMS World Forum

Posted: **Fri Jun 05, 2020 8:31 am**

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

Posted: **Fri Jun 05, 2020 8:38 am**

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:

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"

GAMS World Forum

Importing only certain columns from a spreadsheet into GAMS

Importing only certain columns from a spreadsheet into GAMS

Re: Importing only certain columns from a spreadsheet into GAMS