CSV Input Using SQL2GMS

Problems with syntax of GAMS
Post Reply
barsssk
User
User
Posts: 5
Joined: 5 months ago

CSV Input Using SQL2GMS

Post by barsssk » 5 months ago

Hello everyone. I have used SQL2GMS tool to import data from SQL, Oracle or XLS. Now I need to import csv with this tool because row limit of xls is not enough. Here is a simple example from my code to import xls:

Code: Select all

$onecho > %commandfile1%
c=DRIVER=Microsoft Excel Driver (*.xls);dbq=%filename%StoreDistances.xls;
Q1=select SOURCE,TARGET,DISTANCE from [Sheet1$]
a1=Distance
X=KoliOptDistances.gdx
$offecho
$call =sql2gms @%commandfile1%
I modified this code for csv according to sql2gms documetation https://www.gams.com/latest/docs/T_SQL2GMS.html.

Code: Select all

$onecho > %commandfile1%
c=Driver={Microsoft Text Driver (*.txt; *.csv)};dbq=%filename%PerformanceGamsSourceConstraints.csv;
Q1=select disticnt PRODUCTCODE from [PerformanceGamsSourceConstraints$]
s1=product
X=GamsSource.gdx
$offecho
$call =sql2gms @%commandfile1%
The code above gives the error: "Error: [Microsoft][ODBC Text Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides".
I have this version of GAMS for the licanse purposes. So I cannot update the gams.
DXIO.DLL version:GDX Library Jul 4, 2012 23.9.5 WIN 36376.36401 VS8 x86/MS Windows
ADO version: 6.3

Also note that, I only want to import the data with sql syntax, not to import it as table or one parameter in a single csv by using csv2gms tool.

Thanks in advance.

barsssk
User
User
Posts: 5
Joined: 5 months ago

Re: CSV Input Using SQL2GMS

Post by barsssk » 5 months ago

I found the solution :) For those having the same problem, here it is:

Code: Select all

$onecho > %commandfile1%
c=Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=E:MyPath\MyData\;
Q1=select distinct PRODUCTCODE from [PerformanceGamsSourceConstraints.csv]
s1=product
X=GamsTest.gdx

$offecho
$call =sql2gms @%commandfile1%

Jarenka
User
User
Posts: 6
Joined: 2 weeks ago

Re: CSV Input Using SQL2GMS

Post by Jarenka » 2 weeks ago

Dear,
I have a problem in reading in all variables from spreadsheet, using SQL2GMS. As you can see below. It can only read 'qaeg' and 'yloraeg' from below code, but not the rest: 'var1','var2','var3' and 'var4'.

Code: Select all

* specifying axes (sets) and indexes (elements):
Set P    "Production_Place"        /P01 * P08/;
Set R    "Residence_Place"         /R01 * R08/;
Set S    "Market_Place"            /S01 * S08/;
Set Jm   "Sectors"                 /Jm01 * Jm10/;
Set Jn   "Sectors"                 /Jn01 * Jn10/;
Set Ga   "Factor_Age"              /Ga01 * Ga05/;
Set I    "Commodities"             /I01 * I17/;
Set Kc   "Privat_Consumption"      /Kc01 * Kc12/;
Set Kg   "Govern_Consumption"      /Kg01 * Kg04/;
Set Ki   "Investments"             /Ki01 * Ki03/;
Set Ge   "Factor_Education"        /Ge01 * Ge03/;
Set Gg   "Factor_Gender"           /Gg01,Gg02/;
Set H    "Family_Type"             /H01,H02/;
Set IEnv "Energy_Products"         /FOSS,IKEN,VEDV/;
Set Env  "Environmental_Emission"  /CH4,CO,N2O,CO2/;
Set k    "variables"               /qaeg,yloraeg,var1,var2,var3,var4/;

*create a 1st txt file;
$onecho > employment.txt
c=DRIVER=Microsoft Excel Driver (*.xls);dbq=C:\Users\is\Documents\gamsdir\projdir\Data\qaeg.xls;
q1=SELECT P,Jm,Ga,Ge,Gg,'qaeg',qaeg FROM [qaeg$] \
  UNION SELECT P,Jm,Ga,Ge,Gg,'yloraeg',yloraeg FROM [qaeg$]
x=qaeg_yloraeg.gdx
$offecho
$call =sql2gms @employment.txt

parameter d(P,Jm,Ga,Ge,Gg,k) ;
$gdxin qaeg_yloraeg.gdx
$load d=p
I guess that the problem is in this part:

Code: Select all

q1=SELECT P,Jm,Ga,Ge,Gg,'qaeg',qaeg FROM [qaeg$] \
  UNION SELECT P,Jm,Ga,Ge,Gg,'yloraeg',yloraeg FROM [qaeg$]
Can you help me in modifying this?

User avatar
Renger
Posts: 150
Joined: 1 year ago

Re: CSV Input Using SQL2GMS

Post by Renger » 2 weeks ago

Hi Jarenka

It is hard to see what is not working as you did not attach the excel file.
Why don't you use gdxxrw to read the data from excel?
Cheers
Renger

Jarenka
User
User
Posts: 6
Joined: 2 weeks ago

Re: CSV Input Using SQL2GMS

Post by Jarenka » 2 weeks ago

Hi Renger,

Thank you for your answer!

Yes, you right.
I have given up SQL procedure and moved to this:

Code: Select all

$libinclude xlimport inter_consumpP C:\Users\is\Documents\gamsdir\projdir\Data\DATA_til_GAMS.xlsx bNpjifD!a2:d1362
It succeeded in reading in sheets, but I have found another problem. When GAMS reads in each parameter following by reading it from excel file it overwrites the previous data set.
What I would like to receive is to have each parameter (with its dimensions) saved in GAMS memory separately.

Right now I can only see 'xllink.gdx' that contains the last parameter only: "primary_incomes".

My code is the following:

Code: Select all

* specifying axes (sets) and indexes (elements):
Set P    "Production_Place"        /P01 * P08/;
Set R    "Residence_Place"         /R01 * R08/;
Set S    "Market_Place"            /S01 * S08/;
Set Jm   "Sectors"                 /Jm01 * Jm10/;
Set Jn   "Sectors"                 /Jn01 * Jn10/;
Set Ga   "Factor_Age"              /Ga01 * Ga05/;
Set I    "Commodities"             /I01 * I17/;
Set Kc   "Privat_Consumption"      /Kc01 * Kc12/;
Set Kg   "Govern_Consumption"      /Kg01 * Kg04/;
Set Ki   "Investments"             /Ki01 * Ki03/;
Set Ge   "Factor_Education"        /Ge01 * Ge03/;
Set Gg   "Factor_Gender"           /Gg01,Gg02/;
Set H    "Family_Type"             /H01,H02/;
Set IEnv "Energy_Products"         /FOSS,IKEN,VEDV/;
Set Env  "Environmental_Emission"  /CH4,CO,N2O,CO2/;
Set bNP   "variables"              /bNpjifD/;
Set xN    "variables"              /xNpjfD/;
set bNPS  "variables"              /bNpsifD/;
set yfNf  "variables"              /yfNpjfD/;
set yfN   "variables"              /yfNpjD/;
set yklsu "variables"              /yklsMprgD,ykluMprgD/;

*$include "C:\Users\is\Documents\gamsdir\projdir\Data\xxxJoergen\test.gms"

parameter inter_consumpP(P,Jn,I,bNP)
          production(P,Jn,xN)
          inter_consumpPS(P,S,I,bNPS)
          gvaf(P,Jn,yfNf)
          gva(P,Jn,yfN)
          primary_incomes(P,R,Ga,Ge,Gg,yklsu);

$libinclude xlimport inter_consumpP C:\Users\is\Documents\gamsdir\projdir\Data\DATA_til_GAMS.xlsx bNpjifD!a2:d1362
$libinclude xlimport production C:\Users\is\Documents\gamsdir\projdir\Data\DATA_til_GAMS.xlsx xNpjfD!a2:c82
$libinclude xlimport inter_consumpPS C:\Users\is\Documents\gamsdir\projdir\Data\DATA_til_GAMS.xlsx bNpsifD!a2:d1089
$libinclude xlimport gvaf C:\Users\is\Documents\gamsdir\projdir\Data\DATA_til_GAMS.xlsx yfNpjfD!e2:g82
$libinclude xlimport gva C:\Users\is\Documents\gamsdir\projdir\Data\DATA_til_GAMS.xlsx yfNpjD!a2:c82
$libinclude xlimport primary_incomes C:\Users\is\Documents\gamsdir\projdir\Data\DATA_til_GAMS.xlsx yklsuMprgD!a2:g1922
I am totally new to GAMS.
Attachments
DATA_til_GAMS.xlsx
(791.67 KiB) Downloaded 3 times

User avatar
Renger
Posts: 150
Joined: 1 year ago

Re: CSV Input Using SQL2GMS

Post by Renger » 2 weeks ago

Hi Jarenka
You loaded all the data correctly (use a display for the parameters as in the code below).
Try to implement the excel loading and unloading using gdxxrw. XLIMport was developed by Tom Rutherford before we had all the great gdxxrw tools. You can do a lot with gdxxrw, so I would advise to study gdx and gdxxrw in the manuals.
Here is the code using gdxxrw:

Code: Select all

* Using a text file for gxxrw
* Using a on-the-fly written text file

$onecho >taskin.txt
par=inter_consumpP rng=bNpjifD!a2:d1362 rdim=3 cdim=1
par=production rng=xNpjfD!a2:c82 rdim=2 cdim=1
par=inter_consumpPS rng=bNpsifD!a2:d1089 rdim=3 cdim=1
par=gvaf rng=yfNpjfD!e2:g82 rdim=2 cdim=1
par=gva rng= yfNpjD!a2:c82 rdim=2 cdim=1 
par=primary_incomes rng=yklsuMprgD!a2:g1922 rdim=5 cdim=1
$offecho

$gdxin DATA_til_GAMS.gdx
$load inter_consumpP, production, inter_consumpPS, gvaf, gva, primary_incomes
$call gdxxrw.exe DATA_til_GAMS.xlsx @taskin.txt trace=3

display inter_consumpP, production, inter_consumpPS, gvaf, gva, primary_incomes;
Note that you also can load all your sets using gdxxrw without having to define the elements in your gams file.
Hope this helps
Cheers
Renger

Jarenka
User
User
Posts: 6
Joined: 2 weeks ago

Re: CSV Input Using SQL2GMS

Post by Jarenka » 2 weeks ago

Dear Renger,
gdxxrw has helped in reading in all the spread sheets into gdx file.
The problem now starts when I want to load, for example, first parameter: "LOAD bNP". It says:
"Load dimensions are different".
And I have been trying to figure out what I have been doing wrong. I have changed a little content in *txt file, but is seems does not help.

My code for the reading in, foe example, first spread sheet is the following:

Code: Select all

* specifying axes (sets) and indexes (elements):
set P    "Production_Place"        /P01 * P08/
    R    "Residence_Place"         /R01 * R08/
    S    "Market_Place"            /S01 * S08/
    Jm   "Sectors"                 /Jm01 * Jm10/
    Jn   "Sectors"                 /Jn01 * Jn10/
    Ga   "Factor_Age"              /Ga01 * Ga05/
    I    "Commodities"             /I01 * I17/
    Kc   "Privat_Consumption"      /Kc01 * Kc12/
    Kg   "Govern_Consumption"      /Kg01 * Kg04/
    Ki   "Investments"             /Ki01 * Ki03/
    Ge   "Factor_Education"        /Ge01 * Ge03/
    Gg   "Factor_Gender"           /Gg01,Gg02/
    H    "Family_Type"             /H01,H02/
    IEnv "Energy_Products"         /FOSS,IKEN,VEDV/
    Env  "Environmental_Emission"  /CH4,CO,N2O,CO2/;
set bNP      /bNpjifD/;

$onecho >taskin.txt
par=bNP    rng=bNpjifD!A2 rdim=3
           Dset=P rng=bNpjifD!A2 Rdim=1
           Dset=Jn rng=bNpjifD!B2 Rdim=1
           Dset=I rng=bNpjifD!C2 Rdim=1
$offecho

$call gdxxrw.exe C:\Users\is\Documents\gamsdir\projdir\Data\DATA_til_GAMS.xls @taskin.txt trace=3

$GDXIN DATA_til_GAMS.gdx
$LOAD bNP

display bNP;
The data file is the same.

User avatar
Renger
Posts: 150
Joined: 1 year ago

Re: CSV Input Using SQL2GMS

Post by Renger » 2 weeks ago

Hi Jarenka

In your example you write Rdim=1 for bnp. The dimensions are however Rdim=3 Cdim=1 (there are three indices in the row and one in the columns). Take a good look at the documentation of gdxxrw. Here it says:
Cdim = Integer

Column dimension: the number of rows in the data range that will be used to define the labels for the columns. The first Cdim rows of the data range will be used for labels.

Rdim = Integer

Row dimension: the number of columns in the data range that will be used to define the labels for the rows. The first Rdim columns of the data range will be used for the labels.
Cheers
Renger

Jarenka
User
User
Posts: 6
Joined: 2 weeks ago

Re: CSV Input Using SQL2GMS

Post by Jarenka » 1 week ago

Finally, I did it! Thank you!

I would like to see the data in *.lst file. And I am using "display" option for that.

Why instead of values I receive "YES" ? How can I change it?

User avatar
Renger
Posts: 150
Joined: 1 year ago

Re: CSV Input Using SQL2GMS

Post by Renger » 1 week ago

Display is the way to go.
YES will be shown if you display a (dynamic) set, but not with data.
Cheers
Renger

Post Reply