Reading from CSV Files

Hello,

I am a new user of GAMS and am looking for some examples / tutorial to help me read in a CSV file and likewise write to a CSV File.

Would be thankful if someone could guide me or point me to a resource where I can find such usage.

The CSV file I am trying to read is attached for reference.

Thanks and regards,
DC03092018.csv (54.3 KB)

reading csv files
Spreadsheets and other programs can read and write CSV (comma separated value) files. Therein
commas separate fields and text items can be in quotes. GAMS can also include such files using the $
command $Ondelim and $Offdelim.

Table Data(.
$ondelim
$include DC03092018.csv
$offdelim

Your example is however not a standard CSV input file for GAMS.

It would need to look more like this (first few rows and columns):

“Time block”,“Time Desc”,“ANTA_GF(0)”,“ANTA_LF(0)”,“ANTA_RF(0)”
1,“00:00-00:15”,0,137,273
2,“00:15-00:30”,0,137,273
3,“00:30-00:45”,0,137,273

And it should not contain the rows with averages, etcetera, unless you add an identifier in the first column as well.


writing csv files:
You can use put statements to write GAMS output to any text file format.

GAMS users do not have to do put file programming to move data in CSV format. Rather they can use
a libinclude routine called Gams2csv developed by Rutherford and associates at the University of
Colorado. That program and a write-up is available at http://www.mpsge.org/gams2csv/gams2csv.htm.

Hope this helps,

Gideon

Thanks Gideon.
I would go through the reference given and would attempt.

Best regards,

also check out: https://www.gams.com/mccarlGuide/gams_user_guide_2005.htm

Hi, The file encoding of your CSV file is UCS-2 storing each character in 2-bytes. The GAMS compiler and some other tools are not set up for dealing with this type of file encoding. You need to convert the file to a regular (e.g. utf-8) encoding. I did this by loading the file into notepad++ and changing the encoding to UTF-8. The new GAMS Studio is also capable or reading the file properly, but the version that is public does not do the conversion yet. The converted file could be read directly by GAMS if the items in the header line and the index column “Time Desc” with labels like this 00:00-00:15 would be quoted with double quotes. The tool csv2gdx is more flexible in the format it understands (it still does not understand file encoding UCS-2) but is more forgiving with missing quotes. So the following works:

$call csv2gdx DC03092018_utf8.csv id=timeSeries useheader=y index=(1,2) values=(3..lastcol)
$if errorlevel 1 $abort problems with csv2gdx
set t, tdesc, cols;
parameter ts(t,tdesc,cols) "time series";
$gdxin DC03092018_utf8
$load t=dim1 tdesc=dim2 cols=dim3 ts=timeSeries
display cols, t, tdesc, ts;

-Michael

1 Like

Hi Michael, how would you try to import multiple CSV files with this structure? Whereas each csv file corresponds to one parameter and each parameter can have various dimensions from 1 to 4 dimensions.

Is it easier to have each parameter csv file with 5 columns titled Dim1,Dim2,Dim3,Dim4,Value and only those dimensions used would have values and the rest empty.

Or is it better to have each csv file with only the columns used, for instance par(x,y) would be par.csv with columns Dim1,Dim2,Value while por(x,y,z) would be por.csv with Dim1,Dim2,Dim3,Value?