Reading from CSV Files

Problems with syntax of GAMS
Post Reply
scsaxena
User
User
Posts: 13
Joined: 11 months ago

Reading from CSV Files

Post by scsaxena » 2 months ago

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,
Attachments
DC03092018.csv
(54.26 KiB) Downloaded 12 times

User avatar
Gideon Kruseman
User
User
Posts: 23
Joined: 1 year ago

Re: Reading from CSV Files

Post by Gideon Kruseman » 2 months ago

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
Gideon Kruseman
ex-ante and foresight lead @CIMMYT, big data focal point @CIMMYT, coordinator CoP socio-economic data @CGIAR_BigData

scsaxena
User
User
Posts: 13
Joined: 11 months ago

Re: Reading from CSV Files

Post by scsaxena » 2 months ago

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

Best regards,

User avatar
Gideon Kruseman
User
User
Posts: 23
Joined: 1 year ago

Re: Reading from CSV Files

Post by Gideon Kruseman » 2 months ago

Gideon Kruseman
ex-ante and foresight lead @CIMMYT, big data focal point @CIMMYT, coordinator CoP socio-economic data @CGIAR_BigData

User avatar
bussieck
Moderator
Moderator
Posts: 198
Joined: 2 years ago

Re: Reading from CSV Files

Post by bussieck » 2 months ago

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:

Code: Select all

$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

Post Reply