Looping over file names
Re: Looping over file names
The input and output via excel is working for one instance if I don't use %instance% and call gams via run.gms. I just don't understand how to run gams from another gams file and add a number or text via %instance% like here in the example.
The excel data has no data information.
You don't have to run the model for me, I just want to understand the line the trick with %instance%
Am Dienstag, 5. Mai 2015 17:49:15 UTC+2 schrieb Renger van Nieuwkoop:
Or better: send me a zip-file with all your stuff so I can check what is going on.
Renger
From: gams...@googlegroups.com [mailto:gams...@googlegroups.com] On Behalf Of Alexander Strenge
Sent: Dienstag, 5. Mai 2015 17:26
To: gams...@googlegroups.com
Subject: Re: Looping over file names
Hi Renger,
attached you will find my code.
I want to run this model with several input files and create several output files.
The input is described in EXCEL and should be read from one sheet: line 10 for instance = 1 and from line 20 for instance = 2 and so on.
The same should happen for the Output. It should be written to one sheet: line 10 for instance = 1 and to line 20 for instance = 2 and so on.
I want to work with "run.gms" and "makerun.gms" files like described here.
Model in GAMS:
*LCC MODEL
sets
i work-brakedown structure
subi1(i) all R&D processes
subi2(i) all manufacturin processes
subi3(i) all usage processes
subi4(i) all EOL processes
;
alias (i,j);
parameters
* Parameters
balance(i) balance for an activity i if alternative o is chosen
capacity(i,j) max amount of products or pieces for activity j if alternative o is chosen
************************** PHASE: Research & Development *********************************************************************
******* VARIABLE COST TYPES **************************************************************************************************
c_RD_v1(j) variable cost type 1 for activity j if alternative o is chosen in phase "Research & Development"
c_RD_v2(j) variable cost type 2 for activity j if alternative o is chosen in phase "Research & Development"
c_RD_v3(j) variable cost type 3 for activity j if alternative o is chosen in phase "Research & Development"
c_RD_v4(j) variable cost type 4 for activity j if alternative o is chosen in phase "Research & Development"
c_RD_v5(j) variable cost type 5 for activity j if alternative o is chosen in phase "Research & Development"
c_RD_v6(j) variable cost type 6 for activity j if alternative o is chosen in phase "Research & Development"
******* FIX COST TYPES ******************************************************************************************************
c_RD_f1(j) fix cost type 1 for activity j if alternative o is chosen in phase "Research & Development"
c_RD_f2(j) fix cost type 2 for activity j if alternative o is chosen in phase "Research & Development"
c_RD_f3(j) fix cost type 3 for activity j if alternative o is chosen in phase "Research & Development"
c_RD_f4(j) fix cost type 4 for activity j if alternative o is chosen in phase "Research & Development"
c_RD_f5(j) fix cost type 5 for activity j if alternative o is chosen in phase "Research & Development"
c_RD_f6(j) fix cost type 6 for activity j if alternative o is chosen in phase "Research & Development"
************************** PHASE: MANUFACTURING *********************************************************************
******* VARIABLE COST TYPES **************************************************************************************************
c_M_v1(j) variable cost type 1 for activity j if alternative o is chosen in phase "Manufacturing"
c_M_v2(j) variable cost type 2 for activity j if alternative o is chosen in phase "Manufacturing"
c_M_v3(j) variable cost type 3 for activity j if alternative o is chosen in phase "Manufacturing"
c_M_v4(j) variable cost type 4 for activity j if alternative o is chosen in phase "Manufacturing"
c_M_v5(j) variable cost type 5 for activity j if alternative o is chosen in phase "Manufacturing"
c_M_v6(j) variable cost type 6 for activity j if alternative o is chosen in phase "Manufacturing"
******* FIX COST TYPES ******************************************************************************************************
c_M_f1(j) fix cost type 1 for activity j if alternative o is chosen in phase "Manufacturing"
c_M_f2(j) fix cost type 2 for activity j if alternative o is chosen in phase "Manufacturing"
c_M_f3(j) fix cost type 3 for activity j if alternative o is chosen in phase "Manufacturing"
c_M_f4(j) fix cost type 4 for activity j if alternative o is chosen in phase "Manufacturing"
c_M_f5(j) fix cost type 5 for activity j if alternative o is chosen in phase "Manufacturing"
c_M_f6(j) fix cost type 6 for activity j if alternative o is chosen in phase "Manufacturing"
************************** PHASE: USAGE *********************************************************************
******* VARIABLE COST TYPES **************************************************************************************************
c_U_v1(j) variable cost type 1 for activity j if alternative o is chosen in phase "Usage"
c_U_v2(j) variable cost type 2 for activity j if alternative o is chosen in phase "Usage"
c_U_v3(j) variable cost type 3 for activity j if alternative o is chosen in phase "Usage"
c_U_v4(j) variable cost type 4 for activity j if alternative o is chosen in phase "Usage"
c_U_v5(j) variable cost type 5 for activity j if alternative o is chosen in phase "Usage"
c_U_v6(j) variable cost type 6 for activity j if alternative o is chosen in phase "Usage"
******* FIX COST TYPES ******************************************************************************************************
c_U_f1(j) fix cost type 1 for activity j if alternative o is chosen in phase "Usage"
c_U_f2(j) fix cost type 2 for activity j if alternative o is chosen in phase "Usage"
c_U_f3(j) fix cost type 3 for activity j if alternative o is chosen in phase "Usage"
c_U_f4(j) fix cost type 4 for activity j if alternative o is chosen in phase "Usage"
c_U_f5(j) fix cost type 5 for activity j if alternative o is chosen in phase "Usage"
c_U_f6(j) fix cost type 6 for activity j if alternative o is chosen in phase "Usage"
************************** PHASE: END-OF-LIFE *********************************************************************
******* VARIABLE COST TYPES **************************************************************************************************
c_E_v1(j) variable cost type 1 for activity j if alternative o is chosen in phase "End-of-Life"
c_E_v2(j) variable cost type 2 for activity j if alternative o is chosen in phase "End-of-Life"
c_E_v3(j) variable cost type 3 for activity j if alternative o is chosen in phase "End-of-Life"
c_E_v4(j) variable cost type 4 for activity j if alternative o is chosen in phase "End-of-Life"
c_E_v5(j) variable cost type 5 for activity j if alternative o is chosen in phase "End-of-Life"
c_E_v6(j) variable cost type 6 for activity j if alternative o is chosen in phase "End-of-Life"
******* FIX COST TYPES ******************************************************************************************************
c_E_f1(j) fix cost type 1 for activity j if alternative o is chosen in phase "End-of-Life"
c_E_f2(j) fix cost type 2 for activity j if alternative o is chosen in phase "End-of-Life"
c_E_f3(j) fix cost type 3 for activity j if alternative o is chosen in phase "End-of-Life"
c_E_f4(j) fix cost type 4 for activity j if alternative o is chosen in phase "End-of-Life"
c_E_f5(j) fix cost type 5 for activity j if alternative o is chosen in phase "End-of-Life"
c_E_f6(j) fix cost type 6 for activity j if alternative o is chosen in phase "End-of-Life"
;
********************************************************************************
********************************************************************************
********************************************************************************
scalar
lotsize
;
lotsize = 200;
*Loading data from Excel'
$Call 'gdxxrw.exe i=RunTest_01.XLS cmerge=1 se=0 o=LCCModel_%instance%.gdx UpdLinks=3 index=GamsInput!B%instance%0'
$gdxin LCCModel_%instance%.gdx
$load i
$loadM i wrote:
> Hi,
>
> I've written a GAMS program using the xls2gms function to upload data
> and the execute_unload function to save the final result. However, I
> need to run the same GAMS code for 50 different countries, which means
> I have 50 different xls input files.
>
> Is there a way to loop over file names so that I can change the input
> file and the output file each time (e.g. Input_file_CountryX.xls,
> Results_CountryX.gdx) ?
>
> A simplified version of my code is below.
>
> Thanks for your help-
> Alison
>
> -----------------------
> *Read data from external spreadsheet
> $call =xls2gms i=Input_file_Country1.xls r1=Sets!set_i
> o1=industries.set r2=Sets!set_o o2=occupations.set r3=Wages!matrixB
> o3=matrixB.inc r4=Sets!MatrixA o4=Average.inc
>
> set i /
> $include industries.set
> /;
> set o /
> $include occupations.set
> /;
> **************
> *Define B from external spreadsheet
> Table B(o,i) degree of belief
> $include matrixB.inc
> ;
> Parameter avg(i) average by industry/
> $include Average.inc
> /;
> **************
>
> Equations, Objective Function, etc.
>
> **************
> *Run the model to get results
> Model WAGES /objective/;
> Solve WAGES using dnlp minimizing find_min;
> Display w.l, w.m;
> **************
> *Export results to Excel by creating a .gdx file
> execute_unload "Results_Country1.gdx" w.L w.M
> execute 'gdxxrw.exe Results_Country1.gdx var=w.L'
--
- Attachments
-
- RunTest_02.xls
- (316.5 KiB) Downloaded 299 times
-
- Model_03.gms
- (9.85 KiB) Downloaded 307 times
-
- run.gms
- (170 Bytes) Downloaded 295 times
RE: Looping over file names
HI Alex
The trick with %instance% is that Gams replaces %instance% in your code with the value of instance in the call ($call gams mymodel –instance=1), which works fine if I look at the output.
If you want to test your model you could add
$if not setglobal instance $set instance 1
to the model_03.gms-file. Then you could test the model, running model_03.gms directly without run.gms.
As you said, the excel file does not contain data, so I can’t check what is going on. If you want, I can check if you send me at least the gdx files (they shouldn’t be that big) for your test model.
Cheers
Renger
From: gamsworld@googlegroups.com [mailto:gamsworld@googlegroups.com] On Behalf Of Alexander Strenge
Sent: Dienstag, 5. Mai 2015 18:39
To: gamsworld@googlegroups.com
Subject: Re: Looping over file names
The input and output via excel is working for one instance if I don't use %instance% and call gams via run.gms. I just don't understand how to run gams from another gams file and add a number or text via %instance% like here in the example.
The excel data has no data information.
You don't have to run the model for me, I just want to understand the line the trick with %instance%
Am Dienstag, 5. Mai 2015 17:49:15 UTC+2 schrieb Renger van Nieuwkoop:
Or better: send me a zip-file with all your stuff so I can check what is going on.
Renger
From: gams...@googlegroups.com [mailto:gams...@googlegroups.com] On Behalf Of Alexander Strenge
Sent: Dienstag, 5. Mai 2015 17:26
To: gams...@googlegroups.com
Subject: Re: Looping over file names
Hi Renger,
attached you will find my code.
I want to run this model with several input files and create several output files.
The input is described in EXCEL and should be read from one sheet: line 10 for instance = 1 and from line 20 for instance = 2 and so on.
The same should happen for the Output. It should be written to one sheet: line 10 for instance = 1 and to line 20 for instance = 2 and so on.
I want to work with "run.gms" and "makerun.gms" files like described here.
Model in GAMS:
*LCC MODEL
sets
i work-brakedown structure
subi1(i) all R&D processes
subi2(i) all manufacturin processes
subi3(i) all usage processes
subi4(i) all EOL processes
;
alias (i,j);
parameters
* Parameters
balance(i) balance for an activity i if alternative o is chosen
capacity(i,j) max amount of products or pieces for activity j if alternative o is chosen
************************** PHASE: Research & Development *********************************************************************
******* VARIABLE COST TYPES **************************************************************************************************
c_RD_v1(j) variable cost type 1 for activity j if alternative o is chosen in phase "Research & Development"
c_RD_v2(j) variable cost type 2 for activity j if alternative o is chosen in phase "Research & Development"
c_RD_v3(j) variable cost type 3 for activity j if alternative o is chosen in phase "Research & Development"
c_RD_v4(j) variable cost type 4 for activity j if alternative o is chosen in phase "Research & Development"
c_RD_v5(j) variable cost type 5 for activity j if alternative o is chosen in phase "Research & Development"
c_RD_v6(j) variable cost type 6 for activity j if alternative o is chosen in phase "Research & Development"
******* FIX COST TYPES ******************************************************************************************************
c_RD_f1(j) fix cost type 1 for activity j if alternative o is chosen in phase "Research & Development"
c_RD_f2(j) fix cost type 2 for activity j if alternative o is chosen in phase "Research & Development"
c_RD_f3(j) fix cost type 3 for activity j if alternative o is chosen in phase "Research & Development"
c_RD_f4(j) fix cost type 4 for activity j if alternative o is chosen in phase "Research & Development"
c_RD_f5(j) fix cost type 5 for activity j if alternative o is chosen in phase "Research & Development"
c_RD_f6(j) fix cost type 6 for activity j if alternative o is chosen in phase "Research & Development"
************************** PHASE: MANUFACTURING *********************************************************************
******* VARIABLE COST TYPES **************************************************************************************************
c_M_v1(j) variable cost type 1 for activity j if alternative o is chosen in phase "Manufacturing"
c_M_v2(j) variable cost type 2 for activity j if alternative o is chosen in phase "Manufacturing"
c_M_v3(j) variable cost type 3 for activity j if alternative o is chosen in phase "Manufacturing"
c_M_v4(j) variable cost type 4 for activity j if alternative o is chosen in phase "Manufacturing"
c_M_v5(j) variable cost type 5 for activity j if alternative o is chosen in phase "Manufacturing"
c_M_v6(j) variable cost type 6 for activity j if alternative o is chosen in phase "Manufacturing"
******* FIX COST TYPES ******************************************************************************************************
c_M_f1(j) fix cost type 1 for activity j if alternative o is chosen in phase "Manufacturing"
c_M_f2(j) fix cost type 2 for activity j if alternative o is chosen in phase "Manufacturing"
c_M_f3(j) fix cost type 3 for activity j if alternative o is chosen in phase "Manufacturing"
c_M_f4(j) fix cost type 4 for activity j if alternative o is chosen in phase "Manufacturing"
c_M_f5(j) fix cost type 5 for activity j if alternative o is chosen in phase "Manufacturing"
c_M_f6(j) fix cost type 6 for activity j if alternative o is chosen in phase "Manufacturing"
************************** PHASE: USAGE *********************************************************************
******* VARIABLE COST TYPES **************************************************************************************************
c_U_v1(j) variable cost type 1 for activity j if alternative o is chosen in phase "Usage"
c_U_v2(j) variable cost type 2 for activity j if alternative o is chosen in phase "Usage"
c_U_v3(j) variable cost type 3 for activity j if alternative o is chosen in phase "Usage"
c_U_v4(j) variable cost type 4 for activity j if alternative o is chosen in phase "Usage"
c_U_v5(j) variable cost type 5 for activity j if alternative o is chosen in phase "Usage"
c_U_v6(j) variable cost type 6 for activity j if alternative o is chosen in phase "Usage"
******* FIX COST TYPES ******************************************************************************************************
c_U_f1(j) fix cost type 1 for activity j if alternative o is chosen in phase "Usage"
c_U_f2(j) fix cost type 2 for activity j if alternative o is chosen in phase "Usage"
c_U_f3(j) fix cost type 3 for activity j if alternative o is chosen in phase "Usage"
c_U_f4(j) fix cost type 4 for activity j if alternative o is chosen in phase "Usage"
c_U_f5(j) fix cost type 5 for activity j if alternative o is chosen in phase "Usage"
c_U_f6(j) fix cost type 6 for activity j if alternative o is chosen in phase "Usage"
************************** PHASE: END-OF-LIFE *********************************************************************
******* VARIABLE COST TYPES **************************************************************************************************
c_E_v1(j) variable cost type 1 for activity j if alternative o is chosen in phase "End-of-Life"
c_E_v2(j) variable cost type 2 for activity j if alternative o is chosen in phase "End-of-Life"
c_E_v3(j) variable cost type 3 for activity j if alternative o is chosen in phase "End-of-Life"
c_E_v4(j) variable cost type 4 for activity j if alternative o is chosen in phase "End-of-Life"
c_E_v5(j) variable cost type 5 for activity j if alternative o is chosen in phase "End-of-Life"
c_E_v6(j) variable cost type 6 for activity j if alternative o is chosen in phase "End-of-Life"
******* FIX COST TYPES ******************************************************************************************************
c_E_f1(j) fix cost type 1 for activity j if alternative o is chosen in phase "End-of-Life"
c_E_f2(j) fix cost type 2 for activity j if alternative o is chosen in phase "End-of-Life"
c_E_f3(j) fix cost type 3 for activity j if alternative o is chosen in phase "End-of-Life"
c_E_f4(j) fix cost type 4 for activity j if alternative o is chosen in phase "End-of-Life"
c_E_f5(j) fix cost type 5 for activity j if alternative o is chosen in phase "End-of-Life"
c_E_f6(j) fix cost type 6 for activity j if alternative o is chosen in phase "End-of-Life"
;
********************************************************************************
********************************************************************************
********************************************************************************
scalar
lotsize
;
lotsize = 200;
*Loading data from Excel'
$Call 'gdxxrw.exe i=RunTest_01.XLS cmerge=1 se=0 o=LCCModel_%instance%.gdx UpdLinks=3 index=GamsInput!B%instance%0'
$gdxin LCCModel_%instance%.gdx
$load i
$loadM i wrote:
> Hi,
>
> I've written a GAMS program using the xls2gms function to upload data
> and the execute_unload function to save the final result. However, I
> need to run the same GAMS code for 50 different countries, which means
> I have 50 different xls input files.
>
> Is there a way to loop over file names so that I can change the input
> file and the output file each time (e.g. Input_file_CountryX.xls,
> Results_CountryX.gdx) ?
>
> A simplified version of my code is below.
>
> Thanks for your help-
> Alison
>
> -----------------------
> *Read data from external spreadsheet
> $call =xls2gms i=Input_file_Country1.xls r1=Sets!set_i
> o1=industries.set r2=Sets!set_o o2=occupations.set r3=Wages!matrixB
> o3=matrixB.inc r4=Sets!MatrixA o4=Average.inc
>
> set i /
> $include industries.set
> /;
> set o /
> $include occupations.set
> /;
> **************
> *Define B from external spreadsheet
> Table B(o,i) degree of belief
> $include matrixB.inc
> ;
> Parameter avg(i) average by industry/
> $include Average.inc
> /;
> **************
>
> Equations, Objective Function, etc.
>
> **************
> *Run the model to get results
> Model WAGES /objective/;
> Solve WAGES using dnlp minimizing find_min;
> Display w.l, w.m;
> **************
> *Export results to Excel by creating a .gdx file
> execute_unload "Results_Country1.gdx" w.L w.M
> execute 'gdxxrw.exe Results_Country1.gdx var=w.L'
--
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/d/optout.
--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.
--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.
Re: Looping over file names
Hi Renger,
thank you.
I know that the input gdx file is created but the model will not be solved and the (output) excel file is not changed. Do I have to type "execute" instead of "call"?
I sent you a working gams and excel file for one instance without the %instance% and run.gms file.
Alex
Am Dienstag, 24. Februar 2009 21:23:41 UTC+1 schrieb Alison:
Hi,
I've written a GAMS program using the xls2gms function to upload data
and the execute_unload function to save the final result. However, I
need to run the same GAMS code for 50 different countries, which means
I have 50 different xls input files.
Is there a way to loop over file names so that I can change the input
file and the output file each time (e.g. Input_file_CountryX.xls,
Results_CountryX.gdx) ?
A simplified version of my code is below.
Thanks for your help-
Alison
-----------------------
*Read data from external spreadsheet
$call =xls2gms i=Input_file_Country1.xls r1=Sets!set_i
o1=industries.set r2=Sets!set_o o2=occupations.set r3=Wages!matrixB
o3=matrixB.inc r4=Sets!MatrixA o4=Average.inc
set i /
$include industries.set
/;
set o /
$include occupations.set
/;
**************
*Define B from external spreadsheet
Table B(o,i) degree of belief
$include matrixB.inc
;
Parameter avg(i) average by industry/
$include Average.inc
/;
**************
Equations, Objective Function, etc.
**************
*Run the model to get results
Model WAGES /objective/;
Solve WAGES using dnlp minimizing find_min;
Display w.l, w.m;
**************
*Export results to Excel by creating a .gdx file
execute_unload "Results_Country1.gdx" w.L w.M
execute 'gdxxrw.exe Results_Country1.gdx var=w.L'
--
- Attachments
-
- GAMS.zip
- (1.87 KiB) Downloaded 300 times
AW: Looping over file names
The gdx file is missing
Cheers
Renger
Von: gamsworld@googlegroups.com [mailto:gamsworld@googlegroups.com] Im Auftrag von Alexander Strenge
Gesendet: Wednesday, May 6, 2015 4:28 PM
An: gamsworld@googlegroups.com
Betreff: Re: Looping over file names
Hi Renger,
thank you.
I know that the input gdx file is created but the model will not be solved and the (output) excel file is not changed. Do I have to type "execute" instead of "call"?
I sent you a working gams and excel file for one instance without the %instance% and run.gms file.
Alex
Am Dienstag, 24. Februar 2009 21:23:41 UTC+1 schrieb Alison:
Hi,
I've written a GAMS program using the xls2gms function to upload data
and the execute_unload function to save the final result. However, I
need to run the same GAMS code for 50 different countries, which means
I have 50 different xls input files.
Is there a way to loop over file names so that I can change the input
file and the output file each time (e.g. Input_file_CountryX.xls,
Results_CountryX.gdx) ?
A simplified version of my code is below.
Thanks for your help-
Alison
-----------------------
*Read data from external spreadsheet
$call =xls2gms i=Input_file_Country1.xls r1=Sets!set_i
o1=industries.set r2=Sets!set_o o2=occupations.set r3=Wages!matrixB
o3=matrixB.inc r4=Sets!MatrixA o4=Average.inc
set i /
$include industries.set
/;
set o /
$include occupations.set
/;
**************
*Define B from external spreadsheet
Table B(o,i) degree of belief
$include matrixB.inc
;
Parameter avg(i) average by industry/
$include Average.inc
/;
**************
Equations, Objective Function, etc.
**************
*Run the model to get results
Model WAGES /objective/;
Solve WAGES using dnlp minimizing find_min;
Display w.l, w.m;
**************
*Export results to Excel by creating a .gdx file
execute_unload "Results_Country1.gdx" w.L w.M
execute 'gdxxrw.exe Results_Country1.gdx var=w.L'
--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.
--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.
Re: Looping over file names
You don't need the gdx file. The excel file is containing data now.
Am Mittwoch, 6. Mai 2015 16:31:18 UTC+2 schrieb Renger van Nieuwkoop:
The gdx file is missing
Cheers
Renger
Von: gams...@googlegroups.com [mailto:gams...@googlegroups.com] Im Auftrag von Alexander Strenge
Gesendet: Wednesday, May 6, 2015 4:28 PM
An: gams...@googlegroups.com
Betreff: Re: Looping over file names
Hi Renger,
thank you.
I know that the input gdx file is created but the model will not be solved and the (output) excel file is not changed. Do I have to type "execute" instead of "call"?
I sent you a working gams and excel file for one instance without the %instance% and run.gms file.
Alex
Am Dienstag, 24. Februar 2009 21:23:41 UTC+1 schrieb Alison:
Hi,
I've written a GAMS program using the xls2gms function to upload data
and the execute_unload function to save the final result. However, I
need to run the same GAMS code for 50 different countries, which means
I have 50 different xls input files.
Is there a way to loop over file names so that I can change the input
file and the output file each time (e.g. Input_file_CountryX.xls,
Results_CountryX.gdx) ?
A simplified version of my code is below.
Thanks for your help-
Alison
-----------------------
*Read data from external spreadsheet
$call =xls2gms i=Input_file_Country1.xls r1=Sets!set_i
o1=industries.set r2=Sets!set_o o2=occupations.set r3=Wages!matrixB
o3=matrixB.inc r4=Sets!MatrixA o4=Average.inc
set i /
$include industries.set
/;
set o /
$include occupations.set
/;
**************
*Define B from external spreadsheet
Table B(o,i) degree of belief
$include matrixB.inc
;
Parameter avg(i) average by industry/
$include Average.inc
/;
**************
Equations, Objective Function, etc.
**************
*Run the model to get results
Model WAGES /objective/;
Solve WAGES using dnlp minimizing find_min;
Display w.l, w.m;
**************
*Export results to Excel by creating a .gdx file
execute_unload "Results_Country1.gdx" w.L w.M
execute 'gdxxrw.exe Results_Country1.gdx var=w.L'
--
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/d/optout.
--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.
Re: Looping over file names
You just need the "Model_05.gms" and "RunTest_2D_1.XLS" in one folder and it should work with reading and also writing to excel
Am Mittwoch, 6. Mai 2015 16:31:18 UTC+2 schrieb Renger van Nieuwkoop:
The gdx file is missing
Cheers
Renger
Von: gams...@googlegroups.com [mailto:gams...@googlegroups.com] Im Auftrag von Alexander Strenge
Gesendet: Wednesday, May 6, 2015 4:28 PM
An: gams...@googlegroups.com
Betreff: Re: Looping over file names
Hi Renger,
thank you.
I know that the input gdx file is created but the model will not be solved and the (output) excel file is not changed. Do I have to type "execute" instead of "call"?
I sent you a working gams and excel file for one instance without the %instance% and run.gms file.
Alex
Am Dienstag, 24. Februar 2009 21:23:41 UTC+1 schrieb Alison:
Hi,
I've written a GAMS program using the xls2gms function to upload data
and the execute_unload function to save the final result. However, I
need to run the same GAMS code for 50 different countries, which means
I have 50 different xls input files.
Is there a way to loop over file names so that I can change the input
file and the output file each time (e.g. Input_file_CountryX.xls,
Results_CountryX.gdx) ?
A simplified version of my code is below.
Thanks for your help-
Alison
-----------------------
*Read data from external spreadsheet
$call =xls2gms i=Input_file_Country1.xls r1=Sets!set_i
o1=industries.set r2=Sets!set_o o2=occupations.set r3=Wages!matrixB
o3=matrixB.inc r4=Sets!MatrixA o4=Average.inc
set i /
$include industries.set
/;
set o /
$include occupations.set
/;
**************
*Define B from external spreadsheet
Table B(o,i) degree of belief
$include matrixB.inc
;
Parameter avg(i) average by industry/
$include Average.inc
/;
**************
Equations, Objective Function, etc.
**************
*Run the model to get results
Model WAGES /objective/;
Solve WAGES using dnlp minimizing find_min;
Display w.l, w.m;
**************
*Export results to Excel by creating a .gdx file
execute_unload "Results_Country1.gdx" w.L w.M
execute 'gdxxrw.exe Results_Country1.gdx var=w.L'
--
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/d/optout.
--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.
RE: Looping over file names
There is only one file: the model_05.gms file
Renger
From: gamsworld@googlegroups.com [mailto:gamsworld@googlegroups.com] On Behalf Of Alexander Strenge
Sent: Mittwoch, 6. Mai 2015 17:48
To: gamsworld@googlegroups.com
Subject: Re: Looping over file names
You just need the "Model_05.gms" and "RunTest_2D_1.XLS" in one folder and it should work with reading and also writing to excel
Am Mittwoch, 6. Mai 2015 16:31:18 UTC+2 schrieb Renger van Nieuwkoop:
The gdx file is missing
Cheers
Renger
Von: gams...@googlegroups.com [mailto:gams...@googlegroups.com] Im Auftrag von Alexander Strenge
Gesendet: Wednesday, May 6, 2015 4:28 PM
An: gams...@googlegroups.com
Betreff: Re: Looping over file names
Hi Renger,
thank you.
I know that the input gdx file is created but the model will not be solved and the (output) excel file is not changed. Do I have to type "execute" instead of "call"?
I sent you a working gams and excel file for one instance without the %instance% and run.gms file.
Alex
Am Dienstag, 24. Februar 2009 21:23:41 UTC+1 schrieb Alison:
Hi,
I've written a GAMS program using the xls2gms function to upload data
and the execute_unload function to save the final result. However, I
need to run the same GAMS code for 50 different countries, which means
I have 50 different xls input files.
Is there a way to loop over file names so that I can change the input
file and the output file each time (e.g. Input_file_CountryX.xls,
Results_CountryX.gdx) ?
A simplified version of my code is below.
Thanks for your help-
Alison
-----------------------
*Read data from external spreadsheet
$call =xls2gms i=Input_file_Country1.xls r1=Sets!set_i
o1=industries.set r2=Sets!set_o o2=occupations.set r3=Wages!matrixB
o3=matrixB.inc r4=Sets!MatrixA o4=Average.inc
set i /
$include industries.set
/;
set o /
$include occupations.set
/;
**************
*Define B from external spreadsheet
Table B(o,i) degree of belief
$include matrixB.inc
;
Parameter avg(i) average by industry/
$include Average.inc
/;
**************
Equations, Objective Function, etc.
**************
*Run the model to get results
Model WAGES /objective/;
Solve WAGES using dnlp minimizing find_min;
Display w.l, w.m;
**************
*Export results to Excel by creating a .gdx file
execute_unload "Results_Country1.gdx" w.L w.M
execute 'gdxxrw.exe Results_Country1.gdx var=w.L'
--
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/d/optout.
--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.
--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.
Re: Looping over file names
Not my day... Sorry! And thank you again for your help.
Alexander
Am Mittwoch, 6. Mai 2015 17:49:42 UTC+2 schrieb Renger van Nieuwkoop:
There is only one file: the model_05.gms file
Renger
From: gams...@googlegroups.com [mailto:gams...@googlegroups.com] On Behalf Of Alexander Strenge
Sent: Mittwoch, 6. Mai 2015 17:48
To: gams...@googlegroups.com
Subject: Re: Looping over file names
You just need the "Model_05.gms" and "RunTest_2D_1.XLS" in one folder and it should work with reading and also writing to excel
Am Mittwoch, 6. Mai 2015 16:31:18 UTC+2 schrieb Renger van Nieuwkoop:
The gdx file is missing
Cheers
Renger
Von: gams...@googlegroups.com [mailto:gams...@googlegroups.com] Im Auftrag von Alexander Strenge
Gesendet: Wednesday, May 6, 2015 4:28 PM
An: gams...@googlegroups.com
Betreff: Re: Looping over file names
Hi Renger,
thank you.
I know that the input gdx file is created but the model will not be solved and the (output) excel file is not changed. Do I have to type "execute" instead of "call"?
I sent you a working gams and excel file for one instance without the %instance% and run.gms file.
Alex
Am Dienstag, 24. Februar 2009 21:23:41 UTC+1 schrieb Alison:
Hi,
I've written a GAMS program using the xls2gms function to upload data
and the execute_unload function to save the final result. However, I
need to run the same GAMS code for 50 different countries, which means
I have 50 different xls input files.
Is there a way to loop over file names so that I can change the input
file and the output file each time (e.g. Input_file_CountryX.xls,
Results_CountryX.gdx) ?
A simplified version of my code is below.
Thanks for your help-
Alison
-----------------------
*Read data from external spreadsheet
$call =xls2gms i=Input_file_Country1.xls r1=Sets!set_i
o1=industries.set r2=Sets!set_o o2=occupations.set r3=Wages!matrixB
o3=matrixB.inc r4=Sets!MatrixA o4=Average.inc
set i /
$include industries.set
/;
set o /
$include occupations.set
/;
**************
*Define B from external spreadsheet
Table B(o,i) degree of belief
$include matrixB.inc
;
Parameter avg(i) average by industry/
$include Average.inc
/;
**************
Equations, Objective Function, etc.
**************
*Run the model to get results
Model WAGES /objective/;
Solve WAGES using dnlp minimizing find_min;
Display w.l, w.m;
**************
*Export results to Excel by creating a .gdx file
execute_unload "Results_Country1.gdx" w.L w.M
execute 'gdxxrw.exe Results_Country1.gdx var=w.L'
--
- Attachments
-
- GAMS2.zip
- (257.5 KiB) Downloaded 280 times
RE: Looping over file names
Hi Alex
Runs fine on my computer. Here the output in my dos window.
What might happen is that you have the excel file open (see in the gdxutils.pdf for closing it using XLStalk.exe).
I deleted the results in the excel file and when I run the model, the sheet is filled with data. I adjusted the file bringing back “instance†and it also solves fine.
Iteration: 1 Dual objective = 2000.035000
LP status(1): optimal
Cplex Time: 0.02sec (det. 0.51 ticks)
Optimal solution found.
Objective : 7200.160000
--- Restarting execution
--- Reading solution for model LCC_MODEL
--- GDX Point d:\Inbox\alex\LCC_MODEL_p.gdx
GDXXRW 24.4.1 r50296 Released Dec 20, 2014 VS8 x86 32bit/MS Windows
Input file : d:\Inbox\alex\GAMSOutput.gdx
Output file: d:\Inbox\alex\RunTest_2D_1.XLS
Substituting Index
Total time = 3171 Ms
*** Status: Normal completion
--- Job Model_05.gms Stop 05/06/15 17:58:46 elapsed 0:00:05.608
If you add trace = 3 to the gdx-line I get a more usable output for debugging.
GDXXRW 24.4.1 r50296 Released Dec 20, 2014 VS8 x86 32bit/MS Windows
Excel version 15.0
Input file : d:\Inbox\alex\GAMSOutput.gdx
Output file: d:\Inbox\alex\RunTest_2D_1.XLS
Substituting Index
Loading sheet GAMSOutput
Reading data from Excel: Width = 13 Height = 22
var=z.l Rng=GAMSResults!A4:B5
var=f.l Rng=GAMSResults!A6:BN100 rdim=1 cdim=1
Squeeze value : 0.00000000000000E+0000
Squeeze value : 0.00000000000000E+0000
Type Symbol Dim Sheet Data RowHeader ColHeader
Var z 0 GAMSResults A4:A4 --:-- --:--
Clear range: A4:A4
Write range: A4:A4 CellCount = 1
Var f 2 GAMSResults B7:BN100 A7:A100 B6:BN6
Clear range: A6:BN100
Write range: A7:A100 CellCount = 94
Write range: B6:BN6 CellCount = 65
Write range: B7:N19 CellCount = 169
Total time = 3234 Ms
*** Status: Normal completion
Hope this helps
Renger
From: gamsworld@googlegroups.com [mailto:gamsworld@googlegroups.com] On Behalf Of Alexander Strenge
Sent: Mittwoch, 6. Mai 2015 17:57
To: gamsworld@googlegroups.com
Subject: Re: Looping over file names
Not my day... Sorry! And thank you again for your help.
Alexander
Am Mittwoch, 6. Mai 2015 17:49:42 UTC+2 schrieb Renger van Nieuwkoop:
There is only one file: the model_05.gms file
Renger
From: gams...@googlegroups.com [mailto:gams...@googlegroups.com] On Behalf Of Alexander Strenge
Sent: Mittwoch, 6. Mai 2015 17:48
To: gams...@googlegroups.com
Subject: Re: Looping over file names
You just need the "Model_05.gms" and "RunTest_2D_1.XLS" in one folder and it should work with reading and also writing to excel
Am Mittwoch, 6. Mai 2015 16:31:18 UTC+2 schrieb Renger van Nieuwkoop:
The gdx file is missing
Cheers
Renger
Von: gams...@googlegroups.com [mailto:gams...@googlegroups.com] Im Auftrag von Alexander Strenge
Gesendet: Wednesday, May 6, 2015 4:28 PM
An: gams...@googlegroups.com
Betreff: Re: Looping over file names
Hi Renger,
thank you.
I know that the input gdx file is created but the model will not be solved and the (output) excel file is not changed. Do I have to type "execute" instead of "call"?
I sent you a working gams and excel file for one instance without the %instance% and run.gms file.
Alex
Am Dienstag, 24. Februar 2009 21:23:41 UTC+1 schrieb Alison:
Hi,
I've written a GAMS program using the xls2gms function to upload data
and the execute_unload function to save the final result. However, I
need to run the same GAMS code for 50 different countries, which means
I have 50 different xls input files.
Is there a way to loop over file names so that I can change the input
file and the output file each time (e.g. Input_file_CountryX.xls,
Results_CountryX.gdx) ?
A simplified version of my code is below.
Thanks for your help-
Alison
-----------------------
*Read data from external spreadsheet
$call =xls2gms i=Input_file_Country1.xls r1=Sets!set_i
o1=industries.set r2=Sets!set_o o2=occupations.set r3=Wages!matrixB
o3=matrixB.inc r4=Sets!MatrixA o4=Average.inc
set i /
$include industries.set
/;
set o /
$include occupations.set
/;
**************
*Define B from external spreadsheet
Table B(o,i) degree of belief
$include matrixB.inc
;
Parameter avg(i) average by industry/
$include Average.inc
/;
**************
Equations, Objective Function, etc.
**************
*Run the model to get results
Model WAGES /objective/;
Solve WAGES using dnlp minimizing find_min;
Display w.l, w.m;
**************
*Export results to Excel by creating a .gdx file
execute_unload "Results_Country1.gdx" w.L w.M
execute 'gdxxrw.exe Results_Country1.gdx var=w.L'
--
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/d/optout.
--
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/d/optout.
--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.
--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.
Re: Looping over file names
I guess you misunderstood me.
I know that the model is running fine. That was not the issue.
The issue is that my model is not solving if I use the %instance% approach.
Could you maybe send me the adjusted file back?
"I adjusted the file bringing back “instance†and it also solves fine."
Alex
Am Mittwoch, 6. Mai 2015 18:13:00 UTC+2 schrieb Renger van Nieuwkoop:
Hi Alex
Runs fine on my computer. Here the output in my dos window.
What might happen is that you have the excel file open (see in the gdxutils.pdf for closing it using XLStalk.exe).
I deleted the results in the excel file and when I run the model, the sheet is filled with data. I adjusted the file bringing back “instance†and it also solves fine.
Iteration: 1 Dual objective = 2000.035000
LP status(1): optimal
Cplex Time: 0.02sec (det. 0.51 ticks)
Optimal solution found.
Objective : 7200.160000
--- Restarting execution
--- Reading solution for model LCC_MODEL
--- GDX Point d:\Inbox\alex\LCC_MODEL_p.gdx
GDXXRW 24.4.1 r50296 Released Dec 20, 2014 VS8 x86 32bit/MS Windows
Input file : d:\Inbox\alex\GAMSOutput.gdx
Output file: d:\Inbox\alex\RunTest_2D_1.XLS
Substituting Index
Total time = 3171 Ms
*** Status: Normal completion
--- Job Model_05.gms Stop 05/06/15 17:58:46 elapsed 0:00:05.608
If you add trace = 3 to the gdx-line I get a more usable output for debugging.
GDXXRW 24.4.1 r50296 Released Dec 20, 2014 VS8 x86 32bit/MS Windows
Excel version 15.0
Input file : d:\Inbox\alex\GAMSOutput.gdx
Output file: d:\Inbox\alex\RunTest_2D_1.XLS
Substituting Index
Loading sheet GAMSOutput
Reading data from Excel: Width = 13 Height = 22
var=z.l Rng=GAMSResults!A4:B5
var=f.l Rng=GAMSResults!A6:BN100 rdim=1 cdim=1
Squeeze value : 0.00000000000000E+0000
Squeeze value : 0.00000000000000E+0000
Type Symbol Dim Sheet Data RowHeader ColHeader
Var z 0 GAMSResults A4:A4 --:-- --:--
Clear range: A4:A4
Write range: A4:A4 CellCount = 1
Var f 2 GAMSResults B7:BN100 A7:A100 B6:BN6
Clear range: A6:BN100
Write range: A7:A100 CellCount = 94
Write range: B6:BN6 CellCount = 65
Write range: B7:N19 CellCount = 169
Total time = 3234 Ms
*** Status: Normal completion
Hope this helps
Renger
From: gams...@googlegroups.com [mailto:gams...@googlegroups.com] On Behalf Of Alexander Strenge
Sent: Mittwoch, 6. Mai 2015 17:57
To: gams...@googlegroups.com
Subject: Re: Looping over file names
Not my day... Sorry! And thank you again for your help.
Alexander
Am Mittwoch, 6. Mai 2015 17:49:42 UTC+2 schrieb Renger van Nieuwkoop:
There is only one file: the model_05.gms file
Renger
From: gams...@googlegroups.com [mailto:gams...@googlegroups.com] On Behalf Of Alexander Strenge
Sent: Mittwoch, 6. Mai 2015 17:48
To: gams...@googlegroups.com
Subject: Re: Looping over file names
You just need the "Model_05.gms" and "RunTest_2D_1.XLS" in one folder and it should work with reading and also writing to excel
Am Mittwoch, 6. Mai 2015 16:31:18 UTC+2 schrieb Renger van Nieuwkoop:
The gdx file is missing
Cheers
Renger
Von: gams...@googlegroups.com [mailto:gams...@googlegroups.com] Im Auftrag von Alexander Strenge
Gesendet: Wednesday, May 6, 2015 4:28 PM
An: gams...@googlegroups.com
Betreff: Re: Looping over file names
Hi Renger,
thank you.
I know that the input gdx file is created but the model will not be solved and the (output) excel file is not changed. Do I have to type "execute" instead of "call"?
I sent you a working gams and excel file for one instance without the %instance% and run.gms file.
Alex
Am Dienstag, 24. Februar 2009 21:23:41 UTC+1 schrieb Alison:
Hi,
I've written a GAMS program using the xls2gms function to upload data
and the execute_unload function to save the final result. However, I
need to run the same GAMS code for 50 different countries, which means
I have 50 different xls input files.
Is there a way to loop over file names so that I can change the input
file and the output file each time (e.g. Input_file_CountryX.xls,
Results_CountryX.gdx) ?
A simplified version of my code is below.
Thanks for your help-
Alison
-----------------------
*Read data from external spreadsheet
$call =xls2gms i=Input_file_Country1.xls r1=Sets!set_i
o1=industries.set r2=Sets!set_o o2=occupations.set r3=Wages!matrixB
o3=matrixB.inc r4=Sets!MatrixA o4=Average.inc
set i /
$include industries.set
/;
set o /
$include occupations.set
/;
**************
*Define B from external spreadsheet
Table B(o,i) degree of belief
$include matrixB.inc
;
Parameter avg(i) average by industry/
$include Average.inc
/;
**************
Equations, Objective Function, etc.
**************
*Run the model to get results
Model WAGES /objective/;
Solve WAGES using dnlp minimizing find_min;
Display w.l, w.m;
**************
*Export results to Excel by creating a .gdx file
execute_unload "Results_Country1.gdx" w.L w.M
execute 'gdxxrw.exe Results_Country1.gdx var=w.L'
--
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/d/optout.
--
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/d/optout.
--
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/d/optout.
--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.