Importing Data From Excel Topic is solved

questions about GAMS' tools
Post Reply
h.ehtesham
User
User
Posts: 5
Joined: 2 weeks ago

Importing Data From Excel

Post by h.ehtesham » 2 weeks ago

Hello ,
Thank you for your time

I am trying to import data from excel using the following code :

$CALL GDXXRW SAM.xlsx Par=SAM0 rng=A3:FM170
$GDXIN SAM.gdx
$LOAD SAM0

I recieve the Error 510 :" No such file or directory " .
I clearly see the file in my directory . Any advises ?

User avatar
bussieck
Moderator
Moderator
Posts: 749
Joined: 5 years ago

Re: Importing Data From Excel

Post by bussieck » 2 weeks ago

The error message is pretty clear. One of the expected files isn't there. Does the message come from gdxxrw or $GDXIN? Attched your entire log to get help. Also, always check that an external call (here gdxxrw) worked (otherwise it does not create a GDX file). Check the errorLevel status after the call. In more recent versions of GAMS this can be nicely done with "$call.checkErrorLevel gdxxrw ...".

-Michael

h.ehtesham
User
User
Posts: 5
Joined: 2 weeks ago

Re: Importing Data From Excel

Post by h.ehtesham » 2 weeks ago

Dear Micheal ,
Here is the Log :
---------------------------------------
*** GDXIN failed C:\Users\Sibetalaee\Documents\gamsdir\projdir\SAM.gdx
*** Msg: No such file or directory
--- sambal_2013Africa.gms(48) 3 Mb 1 Error
*** Error 510 in C:\Users\Sibetalaee\Desktop\GAMS\iran\opensourceCGE\sambal\sambal\GAMS_code & files\sambal_2013Africa.gms
Unable to open gdx file for $GDXIN
---------------------------------------
There was n`t Sam.gdx file in the directory , my guess is that gdxxrw didnt work

Thanks in advance

User avatar
bussieck
Moderator
Moderator
Posts: 749
Joined: 5 years ago

Re: Importing Data From Excel

Post by bussieck » 2 weeks ago

Good guess. Whyy did you not send the output of gdxxrw?

-Michael

h.ehtesham
User
User
Posts: 5
Joined: 2 weeks ago

Re: Importing Data From Excel

Post by h.ehtesham » 2 weeks ago

Dear Micheal ,
Sorry .I am not experienced with GAMS .
--------------------------------------------------------------------------------
Input file : C:\Users\Sibetalaee\Documents\gamsdir\projdir\SAM.xls
Output file: C:\Users\Sibetalaee\Documents\gamsdir\projdir\SAM.gdx
**** There were 11 duplicate entries for symbol SAM0
1: L,J
2: L,J
3: L,J
4: L,J
5: L,J
6: L,J
7: L,J
8: L,J
9: L,J
10: L,J
Remaining entries skipped
Output gdx file deleted
------------------------------------------------------------------------------------
I understood there is something unusual with xls file . would you please tell me what this means ?
Hossein ,

User avatar
bussieck
Moderator
Moderator
Posts: 749
Joined: 5 years ago

Re: Importing Data From Excel

Post by bussieck » 2 weeks ago

The SAM data is not in a shape that is can be processed by gdxxrw. I suggest you study gdxxrw and its requirement here https://www.gams.com/latest/docs/T_GDXXRW.html. If you are stuck you can post your Excel file, the gdxxrw and the symbols declaration for the data you want to get from Excel. You could also just post your entire model.

-Michael

h.ehtesham
User
User
Posts: 5
Joined: 2 weeks ago

Re: Importing Data From Excel

Post by h.ehtesham » 2 weeks ago

Thank you Michael ,
It is Social Accounting Matrix and The excel file is large . I have posted a few rows and columns ( the rest is similar ) :

-------------------
L L K K AG AG AG AG AG AG AG
USK SK CAP LAND HRP HUP HRR HUR FIRM GVT
L USK
L SK
K CAP
K LAND
AG HRP 5915 5078 526 1132
AG HUP 7300 4697 1054 139
AG HRR 872 1242 526 3602
AG HUR 1210 1010 6221 100 1900
AG FIRM 4741 488 10 10
AG GVT 40 100 122 81

--------------------
****The gams code :

SETS
I MATRIX ACCOUNTS /A4*A40 /

INT(I) All accounts except total;

ALIAS(I,J);
ALIAS(INT,JNT);
INT(I) = YES;
INT('TOT') = NO;

PARAMETER
SAM0(I,J) initial matrix ;

$CALL GDXXRW SAM-V2_EM.xls Par=SAM0 rng=A4:AK39
$GDXIN SAM-V2_EM.gdx
$LOAD SAM0

----------
Thanks again ,
Hossein

User avatar
bussieck
Moderator
Moderator
Posts: 749
Joined: 5 years ago

Re: Importing Data From Excel

Post by bussieck » 2 weeks ago

Did you follow my advice and read actually the documentation for gdxxrw? Then it should be clear to you that you have to instruct gdxxrw to read a 4 dimensional parameter with two dimensions in the row (rdim=2) and two dimensions on the columns (cdim=2). That's how your SAM data is laid out in the spreadsheet (at least it looks like this). You SAM0 parameter in GAMS has only 2 dimensions and the index is all A4*A40 none of these lbales appear in the data. How is this going to work??? There is more that does not make sense: INT is a subset of I which consists of A4*A40 how can you then take out 'TOT' which is not part of I. Instead of struggling with gdxxrw and get millions of records start small and get the basics write (and have all the data explicitly with data statements in GAMS).

Good luck.

-Michael

h.ehtesham
User
User
Posts: 5
Joined: 2 weeks ago

Re: Importing Data From Excel

Post by h.ehtesham » 1 week ago

Thanks a lot Michael

Post Reply