$title CSV2GDX - Testing the Field Separator Comma (CSV2GDX11,SEQ=756)

$onText
The comma field separator is tested in combination with different decimal separators.

Contributor Jan-Erik Justkowiak, May 2018
$offText

* Define the control Set and Parameter for comparison with gdxdiff
$onEcho > controlData1.gms
Set Dim1 / a, b, c /;
Parameter x / a 1.3, b 2.5, c 3.9 /;

$gdxOut controlData1
$unLoad Dim1 x
$gdxOut
$offEcho
$call gams controlData1.gms lo=%GAMS.lo%
$ifE errorLevel<>0 $abort Something went wrong with controlData1!
*-------------------------------------------------------------------------------

* 1. DecimalSep is set correctly
*    This should succeed!
$onEcho > dataCommaP.csv
a,b,c
1.3,2.5,3.9
$offEcho

$log
$log 1. Comma separated <-> fieldSep=comma, period decimals <-> decimalSep=period
$call csv2gdx dataCommaP.csv id=x fieldSep=comma decimalSep=period useHeader=y values=1..3 trace=0
$ifE errorLevel<>0 $abort Error: CSV2GDX should have succeeded when running 1.!
$call gdxdiff dataCommaP.gdx controlData1.gdx > %system.nullfile%
$ifE errorLevel<>0 $abort Data is not correct after running 1.!
$log
*-------------------------------------------------------------------------------

* 2. DecimalSep is not set correctly
*    Note that periods are still interpreted as decimal separator!
*    This should succeed!
$log
$log 2. Comma separated <-> fieldSep=comma, period decimals <-> decimalSep=comma
$call csv2gdx dataCommaP.csv id=x fieldSep=comma decimalSep=comma useHeader=y values=1..3 trace=0
$ifE errorLevel<>0 $abort Error: CSV2GDX should have succeeded when running 2.!
$call gdxdiff dataCommaP.gdx controlData1.gdx > %system.nullfile%
$ifE errorLevel<>0 $abort Data is not correct after running 2.!
$log
*-------------------------------------------------------------------------------

* 3. Comma separated <-> fieldSep=comma, comma decimals <-> decimalSep=period
*    This should fail!
$onEcho > dataCommaC.csv
a,b,c
1,3,2,5,3,9
$offEcho

$log
$log 3. Comma separated <-> fieldSep=comma, comma decimals <-> decimalSep=period
$call csv2gdx dataCommaC.csv id=x fieldSep=comma useHeader=y values=1..3 trace=0
$ifE errorLevel=0 $abort CSV2GDX should have failed when running 3.!
$log Expected Error: Column number exceeds column count!
$log
*-------------------------------------------------------------------------------

* 4. Comma separated <-> fieldSep=comma, comma decimals <-> decimalSep=comma
*    This should fail!
$log
$log 4a. Comma separated <-> fieldSep=comma, comma decimals <-> decimalSep=comma
$call csv2gdx dataCommaC.csv id=x fieldSep=comma decimalSep=comma useHeader=y values=1..3 trace=0
$ifE errorLevel=0 $abort CSV2GDX should have failed when running 4a.!
$log Expected Error: Column number exceeds column count!
$log

* Quoted commas should work
$onEcho > temp.csv
a,b,c
"1,3","2,5","3,9"
$offEcho

$log
$log 4b. Comma separated <-> fieldSep=comma, comma decimals <-> decimalSep=comma
$call csv2gdx temp.csv id=x fieldSep=comma decimalSep=comma useHeader=y values=1..3 trace=0
$ifE errorLevel<>0 $abort Error: CSV2GDX should have succeeded when running 4b.!
$call gdxdiff temp.gdx controlData1.gdx > %system.nullfile%
$ifE errorLevel<>0 $abort Data is not correct after running 4b.!
$log
*-------------------------------------------------------------------------------

* 5. Comma separated <-> fieldSep=tab
*    This should fail, since there is no tab in the header row!
$log
$log 5. Comma separated <-> fieldSep=tab
$call csv2gdx dataCommaP.csv id=x fieldSep=tab useHeader=y values=1..3 trace=0
$ifE errorLevel=0 $abort Error: CSV2GDX should have failed when running 5.!
$log Expected Error: Value(s) column number exceeds column count!
$log
*-------------------------------------------------------------------------------

* 6. Comma separated <-> fieldSep=semicolon
*    This should fail, since there is no semicolon in the header row!
$log
$log 6. Comma separated <-> fieldSep=semicolon
$call csv2gdx dataCommaP.csv id=x fieldSep=semicolon useHeader=y values=1..3 trace=0
$ifE errorLevel=0 $abort Error: CSV2GDX should have failed when running 6.!
$log Expected Error: Value(s) column number exceeds column count!
$log
*-------------------------------------------------------------------------------

* Testing the fieldSep=comma on nasty data in combination with most of the other options
* Also note that the field separator is enclosed by quotes in some index and value columns
$onEcho > specialValues.csv
one,two,three,four,five,six
red,red,,Undef,'3.3',red
red,red,"4,4",5.5,Eps,green
"red , 1",'green',7.7e+02,8.8°,-Inf,blue
blue,blue,"10%",0,NA,"'purple ,'"
green,blue,"Eps","NA","Undef","pur , ple"
green,blue,"0","","  ",'green'
$offEcho

$onEcho > controlData2.gms
Set
   Dim1 / r1*r6 /
   Dim2 / red, green, "red , 1", blue /
   Dim3 / red, "'green'", blue/
   Dim4 / red, green, "'green'", "'purple ,'", "pur , ple", blue /
   Dim5 / c1*c3 /;

Parameter A
$onUNDF
/
r1.red.red.red.c2 Undf
r1.red.red.red.c3 Undf
r2.red.red.green.c1 Undf
r2.red.red.green.c2 5.5
r2.red.red.green.c3 Eps
r3."red , 1"."'green'".blue.c1 770
r3."red , 1"."'green'".blue.c2 Undf
r3."red , 1"."'green'".blue.c3 -Inf
r4.blue.blue."'purple ,'".c1 Undf
r4.blue.blue."'purple ,'".c2 Eps
r4.blue.blue."'purple ,'".c3 NA
r5.green.blue."pur , ple".c1 Eps
r5.green.blue."pur , ple".c2 NA
r5.green.blue."pur , ple".c3 Undf
r6.green.blue."'green'".c1 Eps
/;
$offUNDF

$gdxOut controlData2
$unLoad Dim1 Dim2 Dim3 Dim4 Dim5 A
$gdxOut
$offEcho
$call gams controlData2.gms lo=%GAMS.lo%
$ifE errorLevel<>0 $abort Something went wrong with controlData2!

$log
$log 7.
$call csv2gdx specialValues.csv id=A fieldSep=comma index=1,2,6 values=3..5 colCount=6 autoRow=r autoCol=c valueDim=y useHeader=y storeZero=y trace=0
$ifE errorLevel<>0 $abort Problems reading data with CSV2GDX in 7.!
$call gdxdiff specialValues.gdx controlData2.gdx > %system.nullfile%
$ifE errorLevel<>0 $abort Data is not correct after running 7.!
*-------------------------------------------------------------------------------