gdxxrw12.gms : GDXXRW - Testing the skipEmpty and cMerge Option

Description

At first, the results when reading data with blank rows or columns using the
skipEmpty option is controlled for a small data set. Afterwards, skipEmpty is
tested on data with merged Excel ranges for different values of cMerge.

Contributor: Jan-Erik Justkowiak, September 2018

Small Model of Type : GAMS

Category : GAMS Test library

Main file : gdxxrw12.gms

$title GDXXRW - Testing the skipEmpty and cMerge Option (GDXXRW12,SEQ=781)

$onText
At first, the results when reading data with blank rows or columns using the
skipEmpty option is controlled for a small data set. Afterwards, skipEmpty is
tested on data with merged Excel ranges for different values of cMerge.

Contributor: Jan-Erik Justkowiak, September 2018
$offText

$callTool win32.msappavail Excel
$if errorlevel 1 $abort.noError "No Excel available"

* Expected results
$onEcho > createControlData.gms
Set
   i / a, b, c, d, e /
   j / I, II, III, IV, V /;

Parameter
   block_0(i,j) / a.I  1, a.II  2, a.III  3, a.IV  4, a.V  5
                  b.I  6, b.II  7, b.III  8, b.IV  9, b.V 10
                  c.I 11, c.II 12, c.III 13, c.IV 14, c.V 15
                  d.I 16, d.II 17, d.III 18, d.IV 19, d.V 20
                  e.I 21, e.II 22, e.III 23, e.IV 24, e.V 25 /
   block_1(i,j), block_2(i,j), block_3(i,j), block_4(i,j)
   leftCorner_0(i,j), leftCorner_1(i,j), leftCorner_2(i,j), leftCorner_3(i,j), leftCorner_4(i,j), leftCorner_78542(i,j);;

block_1(i,j) = block_0(i,j);
block_2(i,j) = block_0(i,j);
block_3(i,j) = block_0(i,j);
block_4(i,j) = block_0(i,j);
execute_unload 'blockControl.gdx' block_0 block_1 block_2 block_3 block_4;

leftCorner_0(i,j)$(ord(i)<=1 and ord(j)<=1) = block_0(i,j);
leftCorner_1(i,j)$(ord(i)<=2 and ord(j)<=2) = block_0(i,j);
leftCorner_2(i,j)$(ord(i)<=3 and ord(j)<=3) = block_0(i,j);
leftCorner_3(i,j)$(ord(i)<=4 and ord(j)<=4) = block_0(i,j);
leftCorner_4(i,j)$(ord(i)<=5 and ord(j)<=5) = block_0(i,j);
leftCorner_78542(i,j)$(ord(i)<=5 and ord(j)<=5) = block_0(i,j);

execute_unload 'leftCornerControl.gdx' leftCorner_0 leftCorner_1 leftCorner_2 leftCorner_3 leftCorner_4 leftCorner_78542
$offEcho

$call gams createControlData.gms lo=%GAMS.lo%
$ifE errorLevel<>0 $abort Error calling createControlData!
*-------------------------------------------------------------------------------

* Read in a simple parameter with blank rows and columns (and some noise data)
* If the range is specified using the block range specification, any value of
* skipEmpty will be ignored.
$onEcho > howToRead.txt
skipEmpty=0 par=block_0 rng=A1:P16 rDim=1 cDim=1
skipEmpty=1 par=block_1 rng=A1:P16 rDim=1 cDim=1
skipEmpty=2 par=block_2 rng=A1:P16 rDim=1 cDim=1
skipEmpty=3 par=block_3 rng=A1:P16 rDim=1 cDim=1
skipEmpty=4 par=block_4 rng=A1:P16 rDim=1 cDim=1
$offEcho

$call gdxxrw skipEmpty_cMerge.xlsx output=blockResults.gdx @howToRead.txt trace=0
$ifE errorLevel<>0 $abort Error reading with skipEmpty using block range specification!

* Check the data
$call gdxdiff blockControl.gdx blockResults.gdx > %system.nullfile%
$ifE errorLevel<>0 $abort Data is not correct (skipEmpty with block range specification)!

* Using the top left corner specification
$onEcho > howToRead.txt
skipEmpty=0 par=leftCorner_0 rng=A1 rDim=1 cDim=1
skipEmpty=1 par=leftCorner_1 rng=A1 rDim=1 cDim=1
skipEmpty=2 par=leftCorner_2 rng=A1 rDim=1 cDim=1
skipEmpty=3 par=leftCorner_3 rng=A1 rDim=1 cDim=1
skipEmpty=4 par=leftCorner_4 rng=A1 rDim=1 cDim=1
* random large integer (no more data in the sheet)
skipEmpty=78542 par=leftCorner_78542 rng=A1 rDim=1 cDim=1
$offEcho

$call gdxxrw skipEmpty_cMerge.xlsx output=leftCornerResults.gdx @howToRead.txt trace=0
$ifE errorLevel<>0 $abort Error reading with skipEmpty using top left corner specification!

* Check the data
$call gdxdiff leftCornerControl.gdx leftCornerResults.gdx > %system.nullfile%
$ifE errorLevel<>0 $abort Data is not correct (skipEmpty with top left corner range specification)!
*-------------------------------------------------------------------------------


* Read in a set with merged Excel ranges while using the skipEmpty option
$onEcho > howToRead.txt
skipEmpty=0 cMerge=0 set=i0_0 rng=Sheet2!A1 cDim=1
skipEmpty=1 cMerge=0 set=i1_0 rng=Sheet2!A1 cDim=1
skipEmpty=2 cMerge=0 set=i2_0 rng=Sheet2!A1 cDim=1
skipEmpty=0 cMerge=1 set=i0_1 rng=Sheet2!A1 cDim=1
skipEmpty=1 cMerge=1 set=i1_1 rng=Sheet2!A1 cDim=1
skipEmpty=2 cMerge=1 set=i2_1 rng=Sheet2!A1 cDim=1
$offEcho

$call gdxxrw skipEmpty_cMerge.xlsx maxDupeErrors=18 @howToRead.txt trace=0
$ifE errorLevel<>0 $abort Error reading set with merged Excel ranges while using the skipEmpty option!

* Expected results
Set
   i0_0 / a, b /
   i1_0 / a, b, c /
   i2_0 / a, b, c, d /
   i0_1 / a, b, c, d /
   i1_1 / a, b, c, d /
   i2_1 / a, b, c, d /;

$gdxOut skipEmpty_cMergeControl.gdx
$unLoad i0_0 i1_0 i2_0 i0_1 i1_1 i2_1
$gdxOut

* Check the data
$call gdxdiff skipEmpty_cMergeControl.gdx skipEmpty_cMerge.gdx > %system.nullfile%
$ifE errorLevel<>0 $abort Data is not correct (after reading set with merged Excel ranges while using the skipEmpty option)!