Reading data from Excel for model

[anna....@googlemail.com]

Hi there,

I want to build a model that takes data from an excel sheet instead of me having to type the data. 

This is my data sheet:

data;

## data for sets ##

set ITEM := 19M12 19M14 19M7 194M7.5 19M8 19M9.5;

set ORIG := CH EU JP US;

set DEST := EU JP US;

## data for parameters ##

param avail: AU EU JP US:= 

19M12 50 0 10 0

19M14 10 0 0 0

19M7 0 0 60 0

19M7.5 100 0 20 0

19M8 100 0 20 0

19M9.5 50 0 70 0;

param need: AU JP US:=

19M12 10 0 20

19M14 10 0 20

19M7 30 0 10

19M7.5 30 0 10

19M8 110 0 10

19M9.5 150 0 10;

param cost:EU JP US:=

CH 5 10 10

EU 0 9 9

JP 10 0 9

US 9 8 0;

param penalty_cost:= 1000;

sheet Now I would like to read the set of items, the avail and the need from excel tables (because I don't only want to look at 6 items, but at ca. 200. 

I am aware that the connection to Excel is explained in the AMPL book, however, it did not get clear to me how to connect this to my data sheet or model. Can you give me any advice?

Many thanks,

Anna

[AMPL Google Group]

Dear Anna,

You can find examples and additional documentation explaining how to read and write to Excel at https://ampl.com/NEW/tables.html. You will find there examples such as:

# to read:
table ImportFoods IN "ODBC" "diet.xlsx" "Foods":
FOOD <- [FOOD], cost, f_min, f_max;
# to write:
table ExportFoods OUT "ODBC" "diet.xlsx" "Foods":
FOOD <- [FOOD], Buy, Buy.rc ~ BuyRC, {j in FOOD} Buy[j]/f_max[j] ~ BuyFrac;

In the example above the name of the excel file is diet.xlsx and the table handler used is ODBC. Please let us know if you have any further questions.

--
Filipe Brandão
am...@googlegroups.com

{#HS:908272705-49993#}

--
You received this message because you are subscribed to the Google Groups "AMPL Modeling Language" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ampl+uns...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ampl/e1e45156-5982-4997-9eef-5bbd47e0d498%40googlegroups.com.