Importing Data From Excel

Problems with syntax of GAMS
Post Reply
sebaslo01
User
User
Posts: 4
Joined: 10 months ago

Importing Data From Excel

Post by sebaslo01 »

Good morning friends,
I hope you can help me, I'm really stuck.
I want gams to read the parameters automatically but I get the error:

"Processor information: 1 socket(s), 4 core(s), and 8 thread(s) available
GAMS 43.3.0 Copyright (C) 1987-2023 GAMS Development. All rights reserved
--- Starting compilation
--- Excel.gms(17) 3 Mb[FIL:"C:\Users\Nuevo Usuario\Documents\gamsdir\projdir\Excel.gms",17,0]
--- $echo File C:\Users\Nuevo Usuario\Documents\gamsdir\projdir\intext.txt[FIL:"C:\Users\Nuevo Usuario\Documents\gamsdir\projdir\intext.txt",0,0]
--- Excel.gms(27) 3 Mb[FIL:"C:\Users\Nuevo Usuario\Documents\gamsdir\projdir\Excel.gms",27,0]
--- call GDXXRW.exe I=ensayo.xlsx O=ensayo.GDX trace=3 @intext.txt

GDXXRW 43.3.0 9e10b068 May 18, 2023 WEI x86 64bit/MS Window
Excel version 16.0
Input file : C:\Users\Nuevo Usuario\Documents\gamsdir\projdir\ensayo.xlsx
Output file: C:\Users\Nuevo Usuario\Documents\gamsdir\projdir\ensayo.GDX
**** Symbol: i Bad dimension: 1;
Total time = 672 Ms
--- Excel.gms(28) 3 Mb[FIL:"C:\Users\Nuevo Usuario\Documents\gamsdir\projdir\Excel.gms",28,0]
*** GDXIN failed C:\Users\Nuevo Usuario\Documents\gamsdir\projdir\ensayo.gdx
*** Msg: No such file or directory
--- Excel.gms(28) 3 Mb 1 Error[FIL:"C:\Users\Nuevo Usuario\Documents\gamsdir\projdir\Excel.gms",28,0]
*** Error 510 in C:\Users\Nuevo Usuario\Documents\gamsdir\projdir\Excel.gms[ERR:"C:\Users\Nuevo Usuario\Documents\gamsdir\projdir\Excel.gms",28,17][LST:24]
Unable to open gdx file for $GDXIN
--- Excel.gms(29) 3 Mb 2 Errors[FIL:"C:\Users\Nuevo Usuario\Documents\gamsdir\projdir\Excel.gms",29,0]
*** Error 502 in C:\Users\Nuevo Usuario\Documents\gamsdir\projdir\Excel.gms[ERR:"C:\Users\Nuevo Usuario\Documents\gamsdir\projdir\Excel.gms",29,5][LST:27]
GDXIN file not open - ignore rest of line
--- Excel.gms(32) 3 Mb 2 Errors[FIL:"C:\Users\Nuevo Usuario\Documents\gamsdir\projdir\Excel.gms",32,0]
*** Status: Compilation error(s)[LST:58]
--- Job Excel.gms Stop 05/26/23 14:21:46 elapsed 0:00:00.827
"

ensayo.xlsx
(13.51 KiB) Downloaded 46 times
and I really don't know what to do with this. I attach model and excel data.

If you can help me I would appreciate it. [/b]
Excel.gms
(751 Bytes) Downloaded 56 times
User avatar
bussieck
Moderator
Moderator
Posts: 1033
Joined: 7 years ago

Re: Importing Data From Excel

Post by bussieck »

Not sure where you picked upo the idea to terminate the gdxxrw instructions with a semicolon (dset=i rng=Datos!B5 rDIM=1;). With that gdxxrw tries to interprete 1; as a number and fails. Reading parameters requires reading and index and the numbers. With the instructions "par=Qfvmax rng=Datos!e5:e16 rDim=1" you only read the numbers and gdxxrw does not kow to associate them to the set i from Excel column B. I usually do this by reading the entire table in one swoop and then splitting this up in GAMS:

Code: Select all

Set i Dindh;
Scalar
  Dindia DEMANDA TOTAL DIARIA
Parameters
  hdata(i,*) hourly data from spreadsheet
Parameters  
  Qfvmax(i) Potencia fotoltaica horaria
  Drl(i)    Demanda recarga lenta
  Drr(i)   Demanda recarga Rapida
  Pmaxh(i)  Potencia maxima
  Pdq(i)   Precio energia en red
  Pdv(i)   Precio de venta
;

$onecho > intext.txt
dset=i rng=Datos!B5 rDIM=1
par=Dindia rng=Datos!f31 rDim=0
par=hdata rng=Datos!b4:M16 rDim=1 cDim=1
$offEcho
$call.checkErrorLevel GDXXRW.exe I=ensayo.xlsx O=ensayo.GDX  trace=3  @intext.txt
$gdxin ensayo.gdx
$load  i Dindia hdata
$GDXIN

Qfvmax(i)= hdata(i,'Producción  [kWh]');
Drl(i)   = hdata(i,'Demanda RL');
Drr(i)   = hdata(i,'Demanda RR');
Pmaxh(i) = hdata(i,'Potencia max.');
Pdq(i)   = hdata(i,'Precio compra');
Pdv(i)   = hdata(i,'Precio venta ');
Make sure that you have the GAMS file latin1 encoded (not UTF-8) because of the label "Producción [kWh]" (or use a header that has ASCII characters only) because gdxxrw writes GDX files with labels encoded in latin1.

-Michael
sebaslo01
User
User
Posts: 4
Joined: 10 months ago

Re: Importing Data From Excel

Post by sebaslo01 »

Good morning Michael.

Thank you very much for your help, I don't know what's wrong with the forum but I thought nobody had answered me.

I attach the code in case someone in the forum requires something similar and it may help.

Code: Select all

Set i Dindh;
Scalar
  Dindia DEMANDA TOTAL DIARIA
Parameters
  hdata(i,*) hourly data from spreadsheet
Parameters  
  Qfvmax(i) Potencia fotoltaica horaria
  Drl(i)    Demanda recarga lenta
  Drr(i)   Demanda recarga Rapida
  Pmaxh(i)  Potencia maxima
  Pdq(i)   Precio energia en red
  Pdv(i)   Precio de venta
;

$onecho > intext.txt
dset=i rng=Datos!B5 rDIM=1
par=Dindia rng=Datos!f31 rDim=0
par=hdata rng=Datos!b4:M16 rDim=1 cDim=1
$offEcho
$call.checkErrorLevel GDXXRW.exe I=ensayo.xlsx O=ensayo.GDX  trace=3  @intext.txt
$gdxin ensayo.gdx
$load  i Dindia hdata
$GDXIN

Qfvmax(i)= hdata(i,'Produc');
Drl(i)   = hdata(i,'Demanda RL');
Drr(i)   = hdata(i,'Demanda RR');
Pmaxh(i) = hdata(i,'Potencia max.');
Pdq(i)   = hdata(i,'Precio compra');
Pdv(i)   = hdata(i,'Precio venta ');
Variable
OF Objetive Function
Dindh(i) Demanda Industrial Horaria
Qredh(i) Potencia absorbida red
Qexc(i)  Energia excendete
;
POSITIVE VARIABLES
Dindh(i)
Qredh(i)
Qexc(i)
;

POSITIVE VARIABLES
Dindh(i)
Qredh(i)
Qexc(i)
;

Binary Variable
Ired(i) RED 
Iexc(i) EXCEDENTES;

Equations Obj,cons1(i),cons2(i),cons3,cons4(i),cons5(i),cons6(i);

Obj.. OF =e= sum(i,(Qredh(i)*Pdq(i)))-sum(i,Qexc(i)*Pdv(i));

cons1(i).. Dindh(i)+Drl(i)+Drr(i) =l= Pmaxh(i);
Cons2(i) .. Qfvmax(i)+Qredh(i)-Dindh(i)-Drl(i)-Drr(i)-Qexc(i)=e=0;
Cons3 .. sum(i,Dindh(i)) =e= Dindia;

cons4(i).. Qredh(i)=l=Ired(i)*pmaxh(i);
cons5(i).. Qexc(i) =l= (Iexc(i)*Qfvmax(i));
cons6(i).. Iexc(i)+Ired(i) =l= 1;

MODEL Sdiario /all/;
Solve Sdiario using mip minimizing OF;
On the other hand, I am very interested in learning Gams for my professional development, I don't know if you can give me some courses or guides that could be useful for my professional development.

Finally, thanks again.
Sebastian Garcia.
User avatar
bussieck
Moderator
Moderator
Posts: 1033
Joined: 7 years ago

Re: Importing Data From Excel

Post by bussieck »

Sebastian,

On professional development. I can recommend Bruce McCarl's GAMS classes. He starts one next week. See https://www.gams.com/courses/.

-Michael
sebaslo01
User
User
Posts: 4
Joined: 10 months ago

Re: Importing Data From Excel

Post by sebaslo01 »

Good morning Michael.

Reviewing the courses look very good, but they are beyond my economic possibilities, I am from Colombia.

If you can give me other recommendations I would appreciate it.

Thank you very much.
Greetings, Sebastian.
Post Reply