$title 'Complex Connect Example with CSV Files' (CONNECT02,SEQ=145)

$onText
This model uses GAMS Connect to read and write CSV files

Contributor: Aileen Böhme, March 2022
$offText

Set date,symbol;
Table stockprice(date<,symbol<)
                AAPL       GOOG       MMM      MSFT       WMT
2012-20-11 12.124061 314.008026 60.966354 21.068886 46.991535
2112-20-11 12.139372 311.741516 60.731037 20.850344 47.150307
2212-20-11 12.203673 313.674286 61.467381 20.890808 46.991535
2312-20-11 12.350039 315.387848 62.401108 21.068886 47.626663
2712-20-11 12.448025 318.929565 62.461876 21.076981 47.499634
2812-20-11 12.328911 318.655609 61.604042 20.898905 47.420238
2912-20-11 12.404848 320.000549 62.332813 21.060795 47.626663
3012-20-11 12.401172 321.744019 62.044331 21.012224 47.444057
;

* Use Connect CSVWriter to write GAMS data in CSV format moving the symbol index into the column (unstack: True)
$onEmbeddedCode Connect:
- GAMSReader:
    symbols: [ {name: stockprice} ]
- GDXWriter:
    file: sp_connect.gdx
    symbols: all
- CSVWriter:
    file: sp_connect.csv
    name: stockprice
    header: True
    unstack: True
    quoting: 2
$offEmbeddedCode

* Use gdxdump to create a CSV file and text compare the Connect and gdxdump CSV files
$call.checkErrorLevel  gdxdump sp_connect.gdx symb=stockprice format=csv cDim=Y > sp_gdxdump.csv
$call.checkErrorLevel  diff -q sp_connect.csv sp_gdxdump.csv

* Use Connect CSVReader to read the newly created CSV file and deposit the result in a CSVRead compatible format
$onEmbeddedCode Connect:
- CSVReader:
    file: sp_connect.csv
    name: stockprice
    indexColumns: 1
    valueColumns: "2:lastCol"
- Projection:
    name: stockprice(date,symbol)
    newName: Dim1(date)
    asSet: True
- Projection:
    name: stockprice(date,symbol)
    newName: Dim2(symbol)
    asSet: True
- GDXWriter:
    file: sp_connect.gdx
    symbols: all
    duplicateRecords: first
$offEmbeddedCode

* Use CSVRead to create a GDX file and compare the Connect and CSVRead GDX files
$call gamstool csvread sp_connect.csv id=stockprice index=1 values=2..lastCol useheader=yes gdxout=sp_csvread.gdx > %system.nullfile%
$call.checkErrorLevel gdxdiff sp_connect.gdx sp_csvread.gdx > %system.nullFile%