How do I import certain columns from a spreadsheet into GAMS?
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.
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
---- 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:
$call"=gdxxrw i=testdaten_GAMS.xls o=data.gdx par=A rng=Tabelle1!a2 ignoreColumns=M"