Looping over file names

Archive of Gamsworld Google Group
Archiver
User
User
Posts: 7876
Joined: 7 years ago

Re: Looping over file names

Post by Archiver »


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 296 times
Model_03.gms
(9.85 KiB) Downloaded 300 times
run.gms
(170 Bytes) Downloaded 292 times
Archiver
User
User
Posts: 7876
Joined: 7 years ago

RE: Looping over file names

Post by Archiver »


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.
Archiver
User
User
Posts: 7876
Joined: 7 years ago

Re: Looping over file names

Post by Archiver »


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 296 times
Archiver
User
User
Posts: 7876
Joined: 7 years ago

AW: Looping over file names

Post by Archiver »


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.
Archiver
User
User
Posts: 7876
Joined: 7 years ago

Re: Looping over file names

Post by Archiver »


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.
Archiver
User
User
Posts: 7876
Joined: 7 years ago

Re: Looping over file names

Post by Archiver »


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.
Archiver
User
User
Posts: 7876
Joined: 7 years ago

RE: Looping over file names

Post by Archiver »


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.
Archiver
User
User
Posts: 7876
Joined: 7 years ago

Re: Looping over file names

Post by Archiver »


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 274 times
Archiver
User
User
Posts: 7876
Joined: 7 years ago

RE: Looping over file names

Post by Archiver »


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.
Archiver
User
User
Posts: 7876
Joined: 7 years ago

Re: Looping over file names

Post by Archiver »


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.
Post Reply