Spreadsheet files may contain blank rows or columns. The skipempty command controls the way blank rows or columns are handled and causes Gdxxrw to either stop when a blank row or column is encountered or skip over that. This is done using the syntax
SkipEmpty=integer
or
SE=integer
which must precede any par, set etc statements that will be affected by it
where
The integer value tells the number of maximum number of blank rows or columns that may be contained within a block of data and that integer value is one less that the number of blank rows or columns that if found will signal the end of the data block.
Allowable values are 0 to N, and the default is 1.
A value of 0 causes Gdxxrw to stop loading data when an empty row or column is encountered.
A value of 1 causes GDXXRW to skip over incidences of one blank row or column but terminate the read if 2 or more adjacent blank rows or columns are found.
When a the range is specified only in terms of the upper left corner ( a single cell), and skip empty is set to zero an empty row or column will always indicate the end of the row or column range.
Examples:
Suppose I have a spreadsheet table that has blank entries in it like the skipempty sheet of the workbook gdxxrwss.xls
and I read it using the default setting (gdxxrwskipempty.gms)
$call "Gdxxrw gdxxrwss.xls o=GDXse.gdx par=moded4 Rng=skipempty!a2:g69"
or
$call "Gdxxrw gdxxrwss.xls se=1 o=GDXse.gdx par=moded4 Rng=skipempty!a2:g69"
then after loading into GAMS the data become
ship truck rail
brussels .cleveland 5000.000
brussels .chicago 6000.000
san francisco.cleveland 2200.000 2200.000
san francisco.chicago 2000.000 2000.000
On the other hand if I read it with skipempty set to zero
$call "Gdxxrw gdxxrwss.xls se=0 o=GDXse.gdx par=moded3 Rng=skipempty!a2:g69 rdim=2 cdim=1"
the blanks terminate the read not reading the rail column and the san francisco.chicago row and the result is
ship truck
brussels .cleveland 5000.000
brussels .chicago 6000.000
san francisco.cleveland 2200.000
Note:
| • | The skipempty parameter must appear before any parameter statements that use it and will persist for the rest of the statements in a command unless it is set to another value. |