GAMS World Forum

Posted: **Thu Feb 15, 2018 9:51 pm**

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

Posted: **Fri Feb 16, 2018 8:32 am**

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

Posted: **Fri Feb 16, 2018 10:04 pm**

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

Posted: **Mon Feb 19, 2018 10:26 am**

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

Posted: **Mon Feb 19, 2018 10:25 pm**

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)

Posted: **Mon Oct 28, 2019 9:35 pm**

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.

Posted: **Tue Oct 29, 2019 7:25 am**

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

Posted: **Wed Oct 30, 2019 6:42 pm**

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. ¯\_(ツ)_/¯

Posted: **Wed Oct 30, 2019 8:27 pm**

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.

Posted: **Tue Feb 25, 2020 12:33 am**

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.

GAMS World Forum

Beginner Question: Using csv as an input

Beginner Question: Using csv as an input

Re: Beginner Question: Using csv as an input

Re: Beginner Question: Using csv as an input

Re: Beginner Question: Using csv as an input

Re: Beginner Question: Using csv as an input

Re: Beginner Question: Using csv as an input

Re: Beginner Question: Using csv as an input

Re: Beginner Question: Using csv as an input

Re: Beginner Question: Using csv as an input

Re: Beginner Question: Using csv as an input