Beginner Question: Using csv as an input

Problems with syntax of GAMS
leobarlach
User
User
Posts: 2
Joined: 6 years ago

Beginner Question: Using csv as an input

Post by leobarlach »

Hello,

I'm having some difficulties with inputting tables from csv.

I believe it has something to do with the headers of the csv, which I could remove manually, but would like to maintain because that's how they come out of R.

Whenever I try to read the table, I get the following error:
326 An empty table statement is not followed by a legal statement
Would appreciate any help.

My code:

Code: Select all

$Title Debugging model

$OnEmpty OnMulti

* definitions

sets
   y             /2015,2020/
   p             /1*4/
   sc            /sc1,sc2/
   g             /Coal,NaturalGas,Nuclear,FuelOil/
   nd            /A,B/

parameters
   pDemand     (y,sc,nd,p)
   pWindGen    (y,sc,nd,p)

   pGenCap     (y, g,nd  )


$ondelim
table
Hourly(y,sc,nd,h,*)
/
$include hourlyDataInput.csv
/
;
$offdelim

pDemand  (y,sc,nd,p) = Hourly (y,sc,nd,p,'Demand') ;

pWindGen (y,sc,nd,p) = Hourly (y,sc,nd,p,'WindGen');

parameter    pGenCap(y,   g,nd)  "Generation capacity per year and hub "
/
$ondelim
$include CapDataInput.csv
$offdelim
/
;
The CapDataInput.csv file:

Code: Select all

y,g,nd,GenCap
2015,Coal,A,20
2020,Coal,A,22
2015,NaturalGas,A,30
2020,NaturalGas,A,40
2015,Nuclear,A,5
2020,Nuclear,A,5
2015,FuelOil,A,2
2020,FuelOil,A,2
2015,Coal,B,10
2020,Coal,B,10
2015,NaturalGas,B,35
2020,NaturalGas,B,48
2015,Nuclear,B,0
2020,Nuclear,B,0
2015,FuelOil,B,3
2020,FuelOil,B,3
The hourlyDataInput.csv file:

Code: Select all

y,sc,nd,p,Demand,WindGen
2015,sc1,A,1,17.64,4.52
2015,sc1,A,2,21.13,3.33
2015,sc1,A,3,22.48,5.74
2015,sc1,A,4,22.01,4.81
2020,sc1,A,1,27.32,4.44
2020,sc1,A,2,19.94,2.5
2020,sc1,A,3,28.08,4.05
2020,sc1,A,4,19.77,4.2
2015,sc1,B,1,24.03,5.41
2015,sc1,B,2,15.89,3.03
2015,sc1,B,3,18.71,4.11
2015,sc1,B,4,23.36,3.67
2020,sc1,B,1,16.62,1.9
2020,sc1,B,2,24.22,4.91
2020,sc1,B,3,29.33,3.33
2020,sc1,B,4,27.38,2.89
2015,sc2,A,1,22.82,3.82
2015,sc2,A,2,25.19,3.2
2015,sc2,A,3,23.77,4.39
2015,sc2,A,4,20.67,4.21
2020,sc2,A,1,20.69,3.61
2020,sc2,A,2,23.15,2.35
2020,sc2,A,3,19.1,2.4
2020,sc2,A,4,25.26,3.19
2015,sc2,B,1,26.72,4.21
2015,sc2,B,2,28.73,3.18
2015,sc2,B,3,26.8,3.93
2015,sc2,B,4,23.23,4.05
2020,sc2,B,1,21.67,2.65
2020,sc2,B,2,27.35,5.91
2020,sc2,B,3,13.6,5.23
2020,sc2,B,4,17.97,3.87
User avatar
Renger
Posts: 639
Joined: 7 years ago

Re: Beginner Question: Using csv as an input

Post by Renger »

Hi

You made some mistakes in the syntax and the definition of the tables themselves (the second table was defined as a parameter at the beginning of your code and you don't have to use the forward slashes).
Here is the correct code:

Code: Select all

table Hourly(y,sc,nd,*,*)
$ondelim
$include hourlyInputData.csv
$offdelim
;
pDemand  (y,sc,nd,p) = Hourly (y,sc,nd,p,'Demand') ;

pWindGen (y,sc,nd,p) = Hourly (y,sc,nd,p,'WindGen');

table    pGenCap(y,g,nd,*)  "Generation capacity per year and hub "
$ondelim
$include CapDataInput.csv
$offdelim
;
CHeers
Renger
____________________________________
Enjoy modeling even more: Read my blog on modeling at The lazy economist
leobarlach
User
User
Posts: 2
Joined: 6 years ago

Re: Beginner Question: Using csv as an input

Post by leobarlach »

Thank you. As with everything with new languages, I though I had tried that before, but clearly was making some mistake.

For using csv for parameters, is there also a way I can use headers? Or is it better to assign a new table and then the parameter? (if we want to retain the csv with header for future work).

For example:

Code: Select all

parameter
	param(y) 	
	
parameter
param(y)
$ondelim
$include param.csv
$offdelim
With the param.csv file looking like:

Code: Select all

y,param
2015,0.5
2020,0.6
User avatar
Renger
Posts: 639
Joined: 7 years ago

Re: Beginner Question: Using csv as an input

Post by Renger »

Hi Leo

Why would you use a header? I would do it like this:

Code: Select all

sets
   y   /2015,2020/
;
parameter param(y) /
$ondelim
$include param.csv
$offdelim
/;

display param;
with param.csv

Code: Select all

2015,0.5
2020,0.6
Cheers
Renger
____________________________________
Enjoy modeling even more: Read my blog on modeling at The lazy economist
User avatar
dirkse
Moderator
Moderator
Posts: 214
Joined: 7 years ago
Location: Fairfax, VA

Re: Beginner Question: Using csv as an input

Post by dirkse »

If you want to use CSV files with headers and not change them at all, you could use the GAMS utility csv2gdx. The datalib model csv2gdx1 gives you a working example: there's a call to csv2gdx in that model, and also some error checking.

To read your file, I did:

Code: Select all

csv2gdx param.csv output=param.gdx ID=param useheader=T trace=3 index=(1) values=(2)
rj235
User
User
Posts: 5
Joined: 4 years ago

Re: Beginner Question: Using csv as an input

Post by rj235 »

Hi all,

I am also a GAMS n00b and have the same type of question/same persistent issue. I have tried a variety of fixes to no avail: I must be missing something very basic. All of this would probably be easier if I had access to GDX, but I'm on a Mac, so...

Basically, I am trying to import a .csv file following the example given <a href="https://www.gams.com/latest/docs/UG_Dat ... l">here</a>. I have a small table with years in rows and scenarios in columns, with values for my scenarios in the table.

According to the example:
----
A file data.csv that specifies the data of this table in CSV format would have the content

,new-york,chicago,topeka
seattle,2.5,1.7,1.8
san-diego,2.5,1.8,1.4
Notice the empty first element in the first line, which corresponds to the top-left blank in the above table.

This file can now be included directly into GAMS by using the $ondelim and $offdelim commands:

Table d(i,j) 'distance in thousands of miles' ;
$ondelim
$include data.csv
$offdelim
---

Therefore, my code reads:

---

SETS
yr year /2019*2039/
scn scenario /sp1,sp2/

Table shockC(yr,scn) 'shock path'
$ondelim
$include shockC.csv
$offdelim
;

display shockC;

----

shockC.csv file looks like:
-----

,sp1,sp2
2019,51.59000962,221.2765056
2020,297.1442391,198.4488019
2021,30.89360312,106.7259835

-----

I have tried this a variety of ways, with no success: I thought I may have simply misplaced a delimiter, but it must be something even more ridiculous. I typically get an error like:

---
7 Table shockC(yr,scn) 'shock path'
INCLUDE /Users/... /shockC.csv
10 yr,sp1,sp2
**** $463
**** LINE 1 INCLUDE /Users/... /shockC.csv
**** LINE 9 INPUT /Users/... /scenarios4_csv.gms
**** 463 The column section in the previous table is missing
---

I have burned a lot of time on a simple problem at this point: if anyone could illuminate the issue for me, I would be most grateful.

Thanks in advance.
Fred
Posts: 372
Joined: 7 years ago

Re: Beginner Question: Using csv as an input

Post by Fred »

Hi,

I ran the following and it works:

Code: Select all

$onecho > shockC.csv
,sp1,sp2
2019,51.59000962,221.2765056
2020,297.1442391,198.4488019
2021,30.89360312,106.7259835
$offecho

SETS
yr year /2019*2039/
scn scenario /sp1,sp2/

Table shockC(yr,scn) 'shock path'
$ondelim
$include shockC.csv
$offdelim
;
display shockC;
The lst file snippet you shared shows that the first line of shockC.csv is different to what you describe in your text.
INCLUDE /Users/... /shockC.csv
10 yr,sp1,sp2
**** $463
Might be an issue with some exotic encoding of the csv file but that is just a guess.

I hope this helps!

Btw. Why do you think that you do not have access to GDX on MAC? Certain tools like e.g. GDXXRW do not work on Mac but that is due to excel. The tool/platform matrix might be of interest to you: https://www.gams.com/latest/docs/T_MAIN ... _PLATFORMS


Fred
rj235
User
User
Posts: 5
Joined: 4 years ago

Re: Beginner Question: Using csv as an input

Post by rj235 »

Yes, that's what I eventually determined: something strange seems to happen when excel exports to .csv . After making absolutely sure everything else lined up, I simply copied the text and pasted it into a new file using my text editor. Lo and behold, GAMS could read the new file with no problem. ¯\_(ツ)_/¯
rj235
User
User
Posts: 5
Joined: 4 years ago

Re: Beginner Question: Using csv as an input

Post by rj235 »

By the way, thanks for clearing up the (partial) availability of GDX tools.

Also, I hunted down the issue: it turns out to be UTF-8 w/ BOM vs. UTF-8, w/ no problems under UTF-8.
mjcecili
User
User
Posts: 12
Joined: 4 years ago

Re: Beginner Question: Using csv as an input

Post by mjcecili »

Renger wrote: 6 years ago Hi

You made some mistakes in the syntax and the definition of the tables themselves (the second table was defined as a parameter at the beginning of your code and you don't have to use the forward slashes).
Here is the correct code:

Code: Select all

table Hourly(y,sc,nd,*,*)
$ondelim
$include hourlyInputData.csv
$offdelim
;
pDemand  (y,sc,nd,p) = Hourly (y,sc,nd,p,'Demand') ;

pWindGen (y,sc,nd,p) = Hourly (y,sc,nd,p,'WindGen');

table    pGenCap(y,g,nd,*)  "Generation capacity per year and hub "
$ondelim
$include CapDataInput.csv
$offdelim
;
CHeers
Renger

Hi Renger,

I have tried to follow what you mentioned, but I get some errors. I am using the same input files. This is what I wrote:

* definitions
sets
y /2015,2020/
p /1*4/
sc /sc1,sc2/
g /Coal,NaturalGas,Nuclear,FuelOil/
nd /A,B/
;

parameters
pDemand (y,sc,nd,p)
pWindGen (y,sc,nd,p)
pGenCapa (y,g,nd)
;

table Hourly(y,sc,nd,p,*,*) 'hourly demand'
$ondelim
$include hourlyDataInput.csv
$offdelim
;

pDemand (y,sc,nd,p) = Hourly (y,sc,nd,p,'Demand') ;
pWindGen (y,sc,nd,p) = Hourly (y,sc,nd,p,'WindGen');

table pGenCap(y,g,nd,*) 'Generation capacity per year and hub'
$ondelim
$include CapDataInput.csv
$offdelim
;

pGenCapa (y,g,nd) = pGenCap(y,g,nd,'GenCap');

display pGenCapa;


What is wrong with this? Thanks a lot.
Post Reply