Importing big Excel file to GAMS

Problems with syntax of GAMS
Post Reply
Abich1414.
User
User
Posts: 7
Joined: 1 year ago

Importing big Excel file to GAMS

Post by Abich1414. »

Dear GAMS world Family, I was trying to import the file from excel to GAMS. But the value of the parameters are not displayed in the GDX file. Could you help me please where the problem is and how to fix it.
Thanks
I used the following code.
$call GDXXRW AVE.xlsx par=AVEt,AVEnt rng=AVE_technical_GTAP10!A1..J233988
$gdxIn AVE.gdx
$load AVEt
$load AVEnt
display AVEt,AVEnt;
AVE.xlsx
(14.69 MiB) Downloaded 120 times
AVE.xlsx
(14.69 MiB) Downloaded 120 times
Last edited by Abich1414. 1 year ago, edited 2 times in total.
User avatar
bussieck
Moderator
Moderator
Posts: 1033
Joined: 7 years ago

Re: Importing big Excel file to GAMS

Post by bussieck »

Looking at error messages a tools writes does help:

Code: Select all

GDXXRW           39.2.0 e0c5072f Jun 2, 2022           VS8 x86 32bit/MS Window
Input file : d:\Users\mbussieck\Downloads\AVE.xlsx
Output file: d:\Users\mbussieck\Downloads\AVE.gdx
**** Bad identifier = AVEt,AVEnt
Total time = 2891 Ms
You want to pick two columns (AVEt and AVEnt) from the sheet together with the first three columns (I guess) to form to GAMS parameters AVEt and AVEnt. That's not how gdxxrw works. There is decent documentation how it does work: https://www.gams.com/latest/docs/T_GDXXRW.html

I guess what you want to do works with the following command:

Code: Select all

$call.checkErrorLevel gdxxrw AVE.xlsx par=AVEt rng=AVE_technical_GTAP10!A2 rdim=3 cdim=0 par=AVEnt rng=AVE_technical_GTAP10!A2 rdim=3 cdim=0 IgnoreColumns=D
-Michael
Abich1414.
User
User
Posts: 7
Joined: 1 year ago

Re: Importing big Excel file to GAMS

Post by Abich1414. »

Dear Micheal I appreciate your kind response and I am able to get the GDX file now. But the values of AVE and AVEnt has become the same in the gdx file(it seems it uploaded the AVE value for both the AVE and AVEnt and didn't loaded the the AVEnt value).
Could you please check it again.
User avatar
bussieck
Moderator
Moderator
Posts: 1033
Joined: 7 years ago

Re: Importing big Excel file to GAMS

Post by bussieck »

Can't reproduce this. After running the gdxxrw command that I provided I looked at the beginning of the data and that looks what I see in the Excel file:

Code: Select all

C:\Users\mbuss\Downloads>gdxxrw AVE.xlsx par=AVEt rng=AVE_technical_GTAP10!A2 rdim=3 cdim=0 par=AVEnt rng=AVE_technical_GTAP10!A2 rdim=3 cdim=0 IgnoreColumns=D trace=3

GDXXRW           39.2.1 98a2c774 Jun 21, 2022          VS8 x86 32bit/MS Window
Excel version 16.0
Input file : C:\Users\mbuss\Downloads\AVE.xlsx
Output file: C:\Users\mbuss\Downloads\AVE.gdx
Type Symbol       Dim      Sheet                Data          RowHeader     ColHeader
Loading sheet AVE_technical_GTAP10
Reading data from Excel; Rng = A1:O233988
Reading Slice: A1:O233988
Par  AVEt           3      AVE_technical_GTAP10 D2:D233988    A2:C233988    --:--
Par  AVEnt          3      AVE_technical_GTAP10 E2:E233988    A2:C233988    --:--
Total time = 4375 Ms

C:\Users\mbuss\Downloads>gdxdump AVE.gdx symb=AVEt | head

Parameter AVEt(*,*,*) /
'ARE'.'AFG'.'CHM' 0.1165234,
'ARE'.'AFG'.'EEQ' 0.0920641,
'ARE'.'AFG'.'LEA' 0.4309801,
'ARE'.'AFG'.'LUM' 0.4776225,
'ARE'.'AFG'.'OFD' 0.025807,
'ARE'.'AFG'.'OMF' 0.1229551,
'ARE'.'AFG'.'OXT' 0.4525888,
'ARE'.'AFG'.'RPP' 0.6572396,

C:\Users\mbuss\Downloads>gdxdump AVE.gdx symb=AVEnt | head

Parameter AVEnt(*,*,*) /
'ARE'.'AFG'.'CMT' 8.535598,
'ARE'.'AFG'.'EEQ' 1.898174,
'ARE'.'AFG'.'ELE' 0.8846899,
'ARE'.'AFG'.'OCR' 3.076413,
'ARE'.'AFG'.'OFD' 10.45421,
'ARE'.'AFG'.'OXT' 1.78E-5,
'ARE'.'AFG'.'PPP' 2.710696,
'ARE'.'AFG'.'P_C' 0.9303445,
-Michael
Abich1414.
User
User
Posts: 7
Joined: 1 year ago

Re: Importing big Excel file to GAMS

Post by Abich1414. »

Dear Micheal, the problem happened when I tried to add one more parameter(trade). I ran the following command but the AVEt and AVEnt value become the same while the trade value didn't loaded and just took the value of the AVEnt. I have attached the GDX file for reference.
Thanks a lot for your kind help and hope this is my last question on the group.
par=AVEt rng=AVE_technical_GTAP10!A2 rdim=3 cdim=0 par=AVEnt rng=AVE_technical_GTAP10!A2 rdim=3 cdim=0 par=Trade rng=AVE_technical_GTAP10!A2 rdim=3 cdim=0 IgnoreColumns=D trace=3

Parameter AVEt,AVEnt,trade ;
$gdxIn AVE.gdx
$Load AVEt
$load AVEnt
AVE.gdx
(1.4 MiB) Downloaded 96 times
$load trade
$gdxIn
DISPLAY AVEt,AVEnt,r,i,trade;
User avatar
bussieck
Moderator
Moderator
Posts: 1033
Joined: 7 years ago

Re: Importing big Excel file to GAMS

Post by bussieck »

You obviously forgot IgnoreColumns for the second parameter. And perhaps you need to ignore more than one column for the third parameter. -Michael
Abich1414.
User
User
Posts: 7
Joined: 1 year ago

Re: Importing big Excel file to GAMS

Post by Abich1414. »

Thank you very much Micheal it worked now.
Post Reply