Reading a CSV file

This is a difficult CSV file to read using using the GAMS tool GDXXRW:

"BARID","BASIN","CAND","PASS1","PASS2","PASS3","USHAB","COST","DSID" 4980,"StJoseph",1,0,0,0,1563.44,0,-1 2217,"StJoseph",1,0,0,0,440.3,100000,4980 2307,"StJoseph",1,1,1,1,1577.67,0,4980 4992,"StJoseph",1,0,0,0,12.16,0,4980 …..

We can however read this with SQL2GMS using the following query:

Q1=select barid,dsid,'pass1',pass1 from %csvfile% where barid is not null \    union \    select barid,dsid,'pass2',pass2 from %csvfile% where barid is not null \    union \    select barid,dsid,'pass3',pass3 from %csvfile% where barid is not null \    union \    select barid,dsid,'cost',cost from %csvfile% where barid is not null

The empty lines are handled by the check for NULLs. Notice that the parameter will look like:

The advantage of SQL is that we have some flexibility in reorganizing and operating on the data before it is passed on to the GDX DLL. In some cases that can be very useful.

In virtually all of the recent projects I have been involved with, I used SQL2GMS to get (sometimes large) data into GAMS. Not all GAMS modelers are familiar with SQL but given the large amounts of data available in databases I would say that it is a worthwhile time investment to get some basic database knowledge.