Beginner Question: Using csv as an input

GAMS
1

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:

$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:

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:

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
2

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:

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

3

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:

parameter
	param(y) 	
	
parameter
param(y)
$ondelim
$include param.csv
$offdelim

With the param.csv file looking like:

y,param
2015,0.5
2020,0.6
4

Hi Leo

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

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

display param;

with param.csv

2015,0.5
2020,0.6

Cheers
Renger

5

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:

csv2gdx param.csv output=param.gdx ID=param useheader=T trace=3 index=(1) values=(2)
6

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 here</a](https://www.gams.com/latest/docs/UG_DataExchange_ASCII.html">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.

7

Hi,

I ran the following and it works:

$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.html#GAMS_TOOLS_SUPPORTED_PLATFORMS


Fred

8

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

9

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.

10

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.

11

Hi Renger,

Thanks a lot, I got it. You have been so helpful.

I am sorry if I bother you, but you have a small example of writting results to a CSV file?

Kind regards. :slight_smile:

12

I am trying to read the following CSV file (hourlyDataInput3.csv):

p,Demand,WindGen
1,17.64,4.52
2,21.13,3.33
3,22.48,5.74
4,22.01,4.81

The code is:

  • definitions
    sets
    p /1*4/
    ;

parameters
pDemand (p)
pWindGen (p)
;

table Hourly(,) “Demand and Wind Generation per year”
$ondelim
$include hourlyDataInput3.csv
$offdelim
;

pDemand(p) = Hourly (p,‘Demand’);
pWindGen(p) = Hourly (p,‘WindGen’);

display pDemand, pWindGen;


However, I have mistakes. Could you help me to fix this. Thanks in advance. :slight_smile:

13

Hi
This runs fine on my computer. Did you include the correct csv file? What is the error you get?
Cheers
Renger

14

Hi Renger,

You are rigth, I found the problem. I had the input files in a different directory.

I am sorry to bother again. Now, I want o read a CSV file that does not have any header. I mean, it is bidimensional (i,j) and it has just the values for each i,j

The file (Cost.csv) is like:
1,1,1,1,1
0,1,0,1,0
1,1,1,1,1

And my program is like this:

sets
i generation units /13/
j time periods /15/
;

table Cdata(i,j) cost for unit i at period j
$ondelim
$include Cost.csv
$offdelim
;

display Cdata;


However, I am having some errors about duplicated values.

By the way, do you have a short example of writing to CSV files?


Thanks a lot :smiley:

15

Hi
You need the labels for the table (e.g. i and j) otherwise Gams assumes the first column and row as set elements (therefore the duplicates).
If you want to write a csv file you can do stuff like this

file myfile /output.csv/;
put myfile;
loop(i, 
   loop(j, 
      put i.tl  ... 
     etc.

Have a closer look at the put documentation.
Cheers
Renger

16

Hi,

Thank you. Is there any other way to read the file? I mean, without the labels?

Kind regards. :slight_smile:

17

Not that I am aware of.
Renger

18

Thanks a lot Renger!!! :smiley: