importing table inside a for

[claudio....@tiscali.it]

Hi,

I am trying to import a table in AMPL from Excel inside a for loop. This is because I need to solve a model repeatedly after changing the value of the parameter g_out.

Here is an extract of the code, reporting an error.

param g_out{i in Istituzionale, w in WO,t in 1..T, s in SC_out} default 0; 
set ScenCard= {60, 100, 200, 300, 400};

for {nscen in ScenCard}{

let SC_out:= 1..nscen;
for {s in SC_out}{ 
let pr_out[s]:=1/card(SC_out);
}
if nscen=60 then{
table TabScenStab60 IN "ODBC" "../scenario generation/scenari tip1 stability.xlsx": [j ~ Nodo, s ~ Scenario], , {t in 1..T} <g_out[j,"I",t,s] ~ (t)>;
read table TabScenStab60;
}
else if nscen=100 then{
table TabScenStab100 IN "ODBC" "../scenario generation/scenari tip1 stability.xlsx": [j ~ Nodo, s ~ Scenario], , {t in 1..T} <g_out[j,"I",t,s] ~ (t)>;
read table TabScenStab100;
}
        problem out;
        solve out;
}

I get the following error:

Abandoning compound command to accept declaration of TabScenStab60.
context:  table TabScenStab60 IN "ODBC" "../scenario generation/scenari tip1 stability.xlsx": [j ~ Nodo, s ~ Scenario], , {t in 1..T} <g_out[j,"I",t,s] ~  >>> (t)>; <<< 

How can I work this around?

Thanks and best regards.

Claudio

 

[Robert Fourer]

Move the statements beginning

table TabScenStab60 ...
table TabScenStab100 ...

earlier in the file, so that they come *before* the for loop. These statements define TabScenStab60 and TabScenStab100 and so they cannot be executed conditionally inside the loop.

Your definitions of TabScenStab60 and TabScenStab100 appear to be the same, however, which means that "read table TabScenStab60;" and "read table TabScenStab100;" are going to read the same values. To get help with this you will need to explain what is supposed to be different at each pass through the for loop. For example, are you trying to read from a different file each time?

Bob Fourer
am...@googlegroups.com

=======

[claudio....@tiscali.it]

Thanks for the reply Robert.

In each for loop I read a different table (in the same excel file), that will change the values of the parameter g. Also the set SC on which g is indexed changes in each iteration.

Best.

Claudio

[Robert Fourer]

You can define an indexed collection of tables, for example using a statement like this before the loop:

table TabScenStab {nscen in ScenCard} IN "ODBC"
"../scenario generation/scenari tip1 stability.xlsx" ("Tab"&nscen):

[j ~ Nodo, s ~ Scenario], , {t in 1..T} <g_out[j,"I",t,s] ~ (t)>;

and then reading one table in each pass through the loop, with the statement

read table TabScenStab[nscen];

The string expression ("Tab"&nscen) will evaluate to Tab60 for table TabScenStab[60], to Tab100 for TabScenStab[100], etc. So in your spreadsheet the data for TabScenStab[60] should be in a range named Tab60, the data for TabScenStab[100] should be in a range named Tab100, and so forth. Because SC_out is changed in each pass through the loop, these data ranges will be of different sizes.