How to import excel file in GAMS?
sara sohail
deepti singhal
--
You received this message because you are subscribed to the Google Groups "gamsworld" group.
To post to this group, send email to gams...@googlegroups.com.
To unsubscribe from this group, send email to gamsworld+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/gamsworld?hl=en.
Deepti Chadha (Singhal)
sara sohail
sara sohail
sara sohail
ali
If you want send me(7arbaha...@gmail.com) an E-mail so i give you
a small project that may help you to learn how it works.
Lena
$include C:\directory your file has been saved \files name.inc
Savitsky Andre
$onecho > task.txt
set= M rng= nodes!u6 rdim=1
set= province rng= nodes!X12 rdim=1
set= Season rng= nodes!V2 rdim=1
par= Distribution rng= nodes!X11 rdim=1 cdim=1
par= Distribution_surplus rng= nodes!X19 rdim=1 cdim=1
set= SeasonToMonth rng= nodes!U6 rdim=2
set= N rng= nodes!b6 rdim=1
set= River rng= nodes!m6 rdim=1
set= Lake rng= nodes!m37 rdim=1
set= Canal rng= nodes!q7 rdim=1
set= Out rng= nodes!m32 rdim=1
set= NN rng= nodes!e6 rdim=2
set= N_Canal rng= nodes!p7 rdim=2
set= Canal_prov rng= nodes!q7 rdim=2
par= Inflow rng= water_data!c147 rdim=1 cdim=1
par= Correction rng= nodes!e6 rdim=2
par= Observation rng= water_data!c66 rdim=2 cdim=1
par= Demand rng= water_data!c5 rdim=1 cdim=1
par= canal_capacity rng= water_data!r6 rdim=1
par= Start_Vol rng= water_data!c137 rdim=1
set= Coeff rng= water_data!h136 cdim=1
Par= Coeff_Lake rng= water_data!g136 rdim=1 cdim=1
par= Up_Rule rng= water_data!c114 rdim=1 cdim=1
par= Lo_Rule rng= water_data!c128 rdim=1 cdim=1
par= Side_INFLOW rng= water_data!c165 rdim=1 cdim=1
par= Timestep rng= nodes!Y6 dim=0
par= conversion rng= nodes!y3 dim=0
par= TimeLag rng= nodes!i6 rdim=2
$offecho
*
* OUTTASK0 file for output organization
*
$onecho > outtask0.txt
text="Table of Content" rng=TOC!a1
var= intake rng=water_output!c6 rdim=1 cdim=1 textid=intake rng=toc!a12 linkid=intake
text=back rng=water_output!c4 link=toc!a12 textid=intake rng=water_output!d4
par= demand rng=water_output!c70 rdim=1 cdim=1 textid=demand rng=toc!a13 linkid=demand
text=back rng=water_output!c68 link=toc!a13 textid=demand rng=water_output!d68
par= demand_total rng=water_output1!c70 rdim=1 cdim=1 textid=demand_total rng=toc!a13 linkid=demand_total
text=back rng=water_output1!c68 link=toc!a13 textid=demand_total rng=water_output1!d68
par= province_demand rng=water_output!c130 rdim=1 cdim=1 textid=water_output_info rng=toc!a13 linkid=province_demand
text=back rng=water_output!c128 link=toc!a13 textid=province_demand rng=water_output!d128
par= province_demand_surplus rng=water_output!c140 rdim=1 cdim=1 textid=water_output_info rng=toc!a13 linkid=province_demand_surplus
text=back rng=water_output!c138 link=toc!a13 textid=province_demand_surplus rng=water_output!d138
par= province_intakes rng=water_output!c150 rdim=1 cdim=1 textid=water_output_info rng=toc!a13 linkid=province_intakes
text=back rng=water_output!c148 link=toc!a13 textid=province_intakes rng=water_output!d148
par= province_intakes_surplus rng=water_output!c160 rdim=1 cdim=1 textid=water_output_info rng=toc!a13 linkid=province_intakes_surplus
text=back rng=water_output!c158 link=toc!a13 textid=province_intakes_surplus rng=water_output!d158
var= power_Generation rng=water_output!c170 rdim=1 cdim=1 textid=water_output_info rng=toc!a13 linkid=province_intakes
text=back rng=water_output!c168 link=toc!a13 textid=power_Generation rng=water_output!d168
par= LevelInLakesAboveTurbine rng=water_output!c190 rdim=1 cdim=1 textid=water_output_info rng=toc!a13 linkid=province_intakes
text=back rng=water_output!c188 link=toc!a13 textid=LevelInLakesAboveTurbine rng=water_output!d188
par= Observed rng=balance_checking!c8 rdim=2 cdim=1 textid=Balance_checking rng=toc!a14 linkid=Observed
text=back rng=balance_checking!c6 link=toc!a14 textid=Observed rng=balance_checking!d6
par= Computed rng=balance_checking!c30 rdim=2 cdim=1 textid=Balance_checking rng=toc!a14 linkid=Observed
text=back rng=balance_checking!c28 link=toc!a14 textid=Computed rng=balance_checking!d28
var= Volume rng=balance_checking!c50 rdim=1 cdim=1 textid=Balance_checking rng=toc!a14 linkid=Observed
text=back rng=balance_checking!c48 link=toc!a14 textid=Volume rng=balance_checking!d48
par= Start_Vol rng=balance_checking!b51 rdim=1 textid=Balance_checking rng=toc!a14 linkid=Observed
text=Start_Vol rng=balance_checking!c50
var= flow rng=balance_checking!c70 rdim=2 cdim=1 textid=Balance_checking rng=toc!a14 linkid=Observed
text=back rng=balance_checking!c68 link=toc!a14 textid=Flow rng=balance_checking!d68
var= ArtWater rng=art_water!c8 rdim=1 cdim=1 textid=Artwater rng=toc!a15 linkid=ArtWater
text=back rng=art_water!c6 link=toc!a14 textid=ArtWater rng=art_water!d6
var= OBJ rng=TOC!e3 text=object_func rng=TOC!d3
$offecho
$call gdxxrw.exe c:\water_only\indus_water_balance_month.xls se=0 o=test1.gdx @task.txt trace=3
$gdxin test1.gdx
*-------------------------------------------------------------------------------
SET m timestep ;
$loaddc m
****
****
****
****
****
****
****
****
****
****
****
****
/;
solve indus_water_only using NLP minimazing obj;
***
***
***
******************************************
* output organization
* not forget to create new project in working folder
* file water_output.xls will be organize automatically
* close xls if open
execute 'xlstalk -s c:/water_only/indus_water_balance_month.xls';
execute 'gdxxrw.exe alex.gdx o=c:/water_only/indus_water_balance_month.xls @outtask0.txt trace=3';
* open xls and show
execute 'xlstalk -O c:/water_only/indus_water_balance_month.xls';
ELCIN KABELOGLU
Here there is a mini example to show how to import data from excel to gams.
Firstly you have to have new excel file in gamsdir folder. In the example it is deneme.xlsx
Your data should be inside this file. Then you can copy the the codes below. Hope it works
Elcin,
set
positions/1*73/
make/model1,model2,model3/
color/color1,color2,color3,color4,color5/;
// sets are defined
parameter firstposassignn(positions,make,color);
*data
$call "gdxxrw dene.xlsx par=firstposassignn rng=sayfa1!a2:c5 rdim=3"
$gdxin dene.gdx
$load firstposassignn
display firstposassignn
sara sohail
Savitsky Andre
Dear Friend,
I import data from excel to gams using below code.
$CALL GDXXRW.EXE ap.xls par=cf rng=A1:E5
Parameter cf(*,*);
$GDXIN ap.gdx
$LOAD cf
$GDXIN
display cf;
the model run truelyy, It produce gdx files and show the solutions. "cf" parametesr mentioned above are the coefficients of the objective functions. the gams ignores these coefficients.my objective function is :
one .. z =e= sum((i,k,l,j), cf(i,k,l,j)*x(i,k,l,j));
but in the output gams does not show the coefficients of X.
let's help me.
thank you.
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+...@googlegroups.com.
To post to this group, send email to gams...@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
nadia moshahedi
--
You received this message because you are subscribed to a topic in the Google Groups "gamsworld" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/gamsworld/EoZOdpjQERI/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to gamsworld+...@googlegroups.com.
A.R. Bahari
Try this by the attachments:
First of all locate the *.xls* file in the following path:
E:\Data\MatrixInputs.XLS
Have a look at the sheet named "index" and in rows, 5,6,7 and 17 of my cod to discover how importing data from excel is performed.
The 5 last rows export data to Excel
Close the excel file when GAMS is runing.
nadia moshahedi
thank youuuuuuuuu... It seems you are expert in gams. it works :D
Savitsky Andre
***********************************************************************************
set= i rng= Node!b1 cdim=1
set= j rng= Node!A2 rdim=1
par= nn rng= Node!a1 rdim=1 cdim=1
$call gdxxrw.exe "nadii.xls" se=0 o="nadii.gdx" @task.txt trace=3
$gdxin nadii.gdx
SETS i ;
$loaddc i
SETS j ;
$loaddc j
Parameter nn(j,i) ;
$loaddc nn
John Ryan
Possibly this may be helpful and I should mention there is no software to load to make the gdxxrw functionality work. Simply the commands and process noted below.
Here is an example of loading items from excel using the gdxxrw capability. In this example I set a global model name which is also the name of the excel spreadsheet. You could ignore that and simply write your excel model name in the area $call “gdxxrw excelmodel.xlsx.
In order to load the date you need to define the sets or parameters first and then you identify each element and point it to where the data is located. In this example the first item read is SlsPosition, given the list I am reading from has duplicates, therefore I have used the “dset” command vs. just “set” as the dset will eliminate duplicates. You then define the set “SlsPosition”, where it is at “Rng=worksheet name and range and then the row and column dimensions. There are defaults here so I only specified the row dimension, however depending on your structure you may need to specify both (see McCarl’s guide for further details). The single quotation “ at the beginning and end are critical and all of the entire string must be kept on one line in the gams model, you cannot do a return half way through the line. If there are so many elements it is to long you need to duplicate the $call command and $gdxin and $load for the new line. This is not desirable if it is a big excel file because it will have to open and close the spreadsheet twice and if it is a big file this can add a fair bit of time to the process of reading the data.
Next is the “$gdxin excelmodel.gdx” line and last you have to load the data by set or parameter with the “$Load” command.
If the data is a parameter, instead of “dset” or “set” use “par” as shown below.
Once you get one file to work this process is easy to duplicate. You will also find over time that excel really is not a preferred way because it is pretty weak if you have a lot of data. When you get there the use of “include” text files is a far superior and easier way to get data into the model as they work with a one line simple command such as “$include SlsData.inc” and all the information below would be imported. You define the sets and parameters in the include file but you do not have to bother with all the load or other commands.
Hope this was of some use,
GAMS model example from an optimal estimate of Sales Headcount model
------------------------------------------------------------
$Setglobal ModelName SalesHeadcountModel
set SlsPosition,SlsLevel,FileNum,Name,JobTitle,JobNum,DeptName,DeptNum,SlsZip;
parameter SlsCompCalc(FileNum,SlsFactors);
$call "gdxxrw %modelname%.xlsx dset SlsPosition Rng=ModelInput!AC2:AC100 rdim=1 dset SlsLevel Rng=ModelInput!AD2:AD100 rdim=1 dset=FileNum Rng=ModelInput!A2:A100 rdim=1 dset=Name Rng=ModelInput!B2:B100 rdim=1 dset=JobTitle Rng=ModelInput!AB2:AB100 rdim=1 dset=JobNum Rng=ModelInput!H2:H100 rdim=1 dset=DeptName Rng=ModelInput!AF2:AF100 rdim=1 dset=DeptNum Rng=ModelInput!AE2:AE100 rdim=1 set=DeptNum2DeptName Rng=ModelInput!AE2:AF100 rdim=2 dset=SlsZip Rng=ModelInput!Q2:Q100 rdim=1 par=SlsCompCalc Rng=ModelInput!S1:V100 rdim=1 cdim=1”
$gdxin %modelname%.gdx
$load SlsPosition
$load SlsLevel
$load FileNum
$load Name
$load JobTitle
$load JobNum
$load DeptName
$load DeptNum
$load SlsZip
$load SlsCompCalc
-------------------------------------------------------------------------
Regards,
John
Ines TR
--
You received this message because you are subscribed to a topic in the Google Groups "gamsworld" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/gamsworld/EoZOdpjQERI/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to gamsworld+...@googlegroups.com.
To post to this group, send email to gams...@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
saosan
John Ryan
If you want to load data from an excel spreadsheet, please find attached one method that works. This is from an actual model that reads the data from columns in the spreadsheet. The key structure is first to define the set and parameter names that the model will be reading from the excel file, then to identify the location of the data through the $call command, then create the gdx file where the data will be stored through the $gdxin command and lastly import the variable and parameter data into the model via the $load command.
Define the sets and parameters to be read from excel
$call "gdxxrw %modelname%.xlsx
$gdxin %modelname%.gdx
$load
First step, identify the set and parameters you will be using in your model and reading from the file. Next, you will note in the string attached there is %modelname% as in the model I defined the model name so I would not have to rewrite it. You should insert the name of your spreadsheet here so it would read $call “gdxxrw mymodelname.xlsx “. You then need to define the data to be read, is it a set or parameter, in this case my first data to be read was SlsPosition, and I used dset because there was a risk of duplicates in the data and the dset command removes duplicates, otherwise you should simply use set or parameter as the designation. Next is the name of the data that you will be using in your model, then where is it in the excel file, in my case the Rng= identifies in spreadsheet mymodelname.xlsx, tab in the spreadhsheet named ModelInput!, in column AC2:AC100 is all the data to be imported as set SlsPosition while removing any duplicates. Lastly you need to define the dimensions of the data, both row and columns, and in this case the data is 1 dimensional so I set rdim=1. You should review McCarl’s on this as it will provide you guidance on the defaults and how to specifiy the rdim and cdim values. This last area can give you an unable to read error if you get the dimensions wrong. In fact any error on range or dimensions etc.. will give you that error command.
The last word of advice is if your data set is large and it takes a while to open the spreadsheet you need to write these commands all on one line, you may not hit enter and continue writing on the next line. This is cumbersome as I have provided only a portion of the data read in this example and it makes the model a little hard to read but then you only open the file once and you can speed up the import process.
Set SlsPosition,SlsLevel,FileNum,Name,JobTitle,JobNum,DeptName,DeptNum,SlsZip;
Parameter SlsCompCalc(FileNum,SlsFactors);
$call "gdxxrw %modelname%.xlsx dset SlsPosition Rng=ModelInput!AC2:AC100 rdim=1 dset SlsLevel Rng=ModelInput!AD2:AD100 rdim=1 dset=FileNum Rng=ModelInput!A2:A100 rdim=1 dset=Name Rng=ModelInput!B2:B100 rdim=1 dset=JobTitle Rng=ModelInput!AB2:AB100 rdim=1 dset=JobNum Rng=ModelInput!H2:H100 rdim=1 dset=DeptName Rng=ModelInput!AF2:AF100 rdim=1 dset=DeptNum Rng=ModelInput!AE2:AE100 rdim=1 set=DeptNum2DeptName Rng=ModelInput!AE2:AF100 rdim=2 dset=SlsZip Rng=ModelInput!Q2:Q100 rdim=1 par=SlsCompCalc Rng=ModelInput!S1:V100 rdim=1 cdim=1"
$gdxin %modelname%.gdx
$load SlsPosition
$load SlsLevel
$load FileNum
$load Name
$load JobTitle
$load JobNum
$load DeptName
$load DeptNum
$load SlsZip
$load SlsCompCalc
With all that being said, reading from excel works pretty good but truthfully is quite cumbersome and is pretty poor if you have a large excel file. In our models we tend to read everything in via include files using the $include command, where you all you do is $ include filename and in the .inc file itself you specify the set or parameter and the data associated with it. Below is an example of the include file as you will see it is exactly as you would have the data if you wrote it into the model and in this approach there is no need to define the set or parameter in the model as it is defined in the include file. It is also wise to use quotation marks around data types that have spaces or other characters in them to ensure the data is read and the spaces ignored, otherwise they could create a reading issue. This method reads much faster than an excel spreadsheet, almost instantly, and it avoids any excel limitation on rows or columns as excel is capped at around 1 million rows and something like 20000 columns. Our models almost always use this approach as it is far more efficient than excel.
Include File example of data in the file
set SalesID/
118509
117856
119324
119332
/;
set ID2EmpName(SalesID,EmpName)/
118509 . "Joe Blow"
117856 . "John Smith"
119324 . "Jerry White"
119332 . "Rod Stewart"
set EmpZip(ZipCodes)/
33027
36064
70810
/;
set Emp2Zip(SalesID,EmpZip)/
118509 . 33027
117856 . 36064
119324 . 70810
119332 . 70810
/;
parameter SalesQuota(SalesID)/
118509 603119
117856 867162
119324 346750
119332 880844
/;
Regards,
John A. Ryan john...@orm-tech.com
From: gams...@googlegroups.com [mailto:gams...@googlegroups.com] On Behalf Of Ines TR
Sent: Saturday, July 06, 2013 8:50 AM
To: gams...@googlegroups.com
Visit this group at http://groups.google.com/group/gamsworld.
saosan
Paul Van der Eijk
decimal comma or period is not an issue. When you write text files
the period in a number .
I would like to ask if there is difference between exel and gams in real numbers for example 1,156. In excel i write this like 1,156 but in gams 1.156. Can i import this data from excel to gams without changing the point?thanks a lot
--
You received this message because you are subscribed to the Google Groups "gamsworld" group.
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+...@googlegroups.com.To post to this group, send email to gams...@googlegroups.com.Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/groups/opt_out.
--
Paul van der Eijk
GAMS Development Corporation
Tel : (202) 342-0180 Fax : (202) 342-0181
Email: pvand...@gams.com
Web : http://www.gams.com
saosan
saosan
Renger van Nieuwkoop
sent from my iPad
What is more i would like to ask in which file i must have my excel so gams to read the data. Thanks again
--
saosan
Renger van Nieuwkoop
Sent from my iPhone
--
saosan
Paul Van der Eijk
par=Load rng=LData!A27:I51 Cdim=1 Rdim=1
--
You received this message because you are subscribed to the Google Groups "gamsworld" group.
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+...@googlegroups.com.To post to this group, send email to gams...@googlegroups.com.Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/groups/opt_out.
Renger van Nieuwkoop
sent from my iPad
You are right but i ask something specific.
--
saosan
saosan
Owais Nooruddin
Ok i manage to read my data, but how i can change the accuracy? For instance imports only three digit after comma
--
You received this message because you are subscribed to the Google Groups "gamsworld" group.
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+...@googlegroups.com.
To post to this group, send email to gams...@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/groups/opt_out.
saosan
Mario González Sauri
Michael Bussieck
I think the log says is very clearly:
Output file: C:\Users\Wario84\Documents\gamsdir\projdir\INPUT.gdx **** There were 11 duplicate entries for symbol PROFIT 1: 18,18 ...
Output gdx file deleted Total time = 2511 Ms --- ASSIGMENT01.gms(20) 3 Mb *** GDXIN failed C:\Users\Wario84\Documents\gamsdir\projdir\INPUT.gdx
...
$call gdxxrw ....
$if errorlevel 1 $abort gdxxrw failed!
$gdxin input
...
Now to why gdxxrw failed. In your spreadsheet you only had numbers. GAMS data is not positional but relational, meaning that we always need the keys that go with the data. I suggest you look again at part V of the gdxutils.pdf document (http://gams.com/dd/docs/tools/gdxutils.pdf). I corrected this part of your program. You find the XLS file and the GAMS file here:
http://www.gams.com/upload/files/201309130237__2Mario.zip
Hope this helps,
Michael Bussieck - GAMSWorld Coordinator