$title CSV2GDX - Testing the Field Separator Semicolon (CSV2GDX10,SEQ=755) $onText The semicolon 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 > dataSemicolonP.csv a;b;c 1.3;2.5;3.9 $offEcho $log $log 1. Semicolon separated <-> fieldSep=semicolon, period decimals <-> decimalSep=period $call csv2gdx dataSemicolonP.csv id=x fieldSep=semicolon decimalSep=period useHeader=y values=1..3 trace=0 $ifE errorLevel<>0 $abort Error: CSV2GDX should have succeeded when running 1.! $call gdxdiff dataSemicolonP.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. Semicolon separated <-> fieldSep=semicolon, period decimals <-> decimalSep=comma $call csv2gdx dataSemicolonP.csv id=x fieldSep=semicolon decimalSep=comma useHeader=y values=1..3 trace=0 $ifE errorLevel<>0 $abort Error: CSV2GDX should have succeeded when running 2.! $call gdxdiff dataSemicolonP.gdx controlData1.gdx > %system.nullfile% $ifE errorLevel<>0 $abort Data is not correct after running 2.! $log *------------------------------------------------------------------------------- * 3. DecimalSep is not set correctly * The data should not be read succesfully! $onEcho > dataSemicolonC.csv a;b;c 1,3;2,5;3,9 $offEcho $log $log 3. Semicolon separated <-> fieldSep=semicolon, comma decimals <-> decimalSep=period $call csv2gdx dataSemicolonC.csv id=x fieldSep=semicolon useHeader=y values=1..3 trace=0 $ifE errorLevel<>0 $abort Error: CSV2GDX should have succeeded when running 3.! $call gdxdiff dataSemicolonC.gdx controlData1.gdx > %system.nullfile% $ifE errorLevel=0 $abort Data should not be correct after running 3.! $log *------------------------------------------------------------------------------- * 4. DecimalSep is set correctly * This should succeed! $log $log 4. Semicolon separated <-> fieldSep=semicolon, comma decimals <-> decimalSep=comma $call csv2gdx dataSemicolonC.csv id=x fieldSep=semicolon decimalSep=comma useHeader=y values=1..3 trace=0 $ifE errorLevel<>0 $abort Error: CSV2GDX should have succeeded when running 4.! $call gdxdiff dataSemicolonC.gdx controlData1.gdx > %system.nullfile% $ifE errorLevel<>0 $abort Data is not correct after running 4.! $log *------------------------------------------------------------------------------- * 5. FieldSep is not set correctly * This should fail, since there is no tab in the header row! $log $log 5. Semicolon separated <-> fieldSep=tab $call csv2gdx dataSemicolonP.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. FieldSep is not set correctly * This should fail, since there is no comma in the header row! $log $log 6. Semicolon separated <-> fieldSep=comma $call csv2gdx dataSemicolonP.csv id=x fieldSep=comma 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=semicolon on nasty data in combination with most of the other options * Also note that the field separator is enclosed by quotes in some index 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 4.4 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=semicolon 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.! *-------------------------------------------------------------------------------