Reading multidimensional data in matrix from one sheet in Excel

Support
1

I would like to import data from excel files. For example, I have 2 excel files here. In the first file are the lengths per AgeClass (AgeClass & MeanLength). In the second excel file are the weights per AgeClass per Year (so this time two indices → Year & AgeClass).

image

image

Here is my code:

load amplxl.dll;

set Year;
set AgeClass;
param MeanLength {AgeClass};
param Weight {Year, AgeClass};

table length IN "amplxl" "Export_Length.xlsx" "Tabelle1": 
   AgeClass <- [AgeClass], MeanLength;
read table length;

table weight IN "amplxl" "Export_Weight.xlsx" "Sheet 1": 
   [Year, AgeClass], Weight;
read table weight;

The first import (excel file with length) works fine. When importing the second file, something does not work. Firstly, the set Year is not filled. If I add Year <- [Year] in the code, a synatx error occurs. Another problem could be that due to the structure of the excel table for Year and AgeClass there are no unique values (there are two indeces → together therefore unique).

Do you know what I need to change? Thank you so much!

2

It is not possible to read a one-dimensional set (Year) and two-dimensional data (Weight) using the same table statement. So, you will need to read one more table. It can contain just one column, with Years in the first cell, and then 2000, 2001, etc. in the cells below that:

image

(There are no data columns.) The table statement would be something like this:

table years IN "amplxl" "Export_Years.xlsx" "Years": 
   Year <- [Year];

Alternatively, if you will always have a set of consecutive years, and the set of age classes will always be consecutive integers beginning with 0, then you can define the sets by just three numbers:

param firstYear;
param lastYear > firstYear;
set Year = firstYear .. lastYear;

param nAgeClass;
set AgeClass = 0..nAgeClass;

You can read these numbers from one table:

image

and the table statement will be like this:

table constants IN "amplxl" "Export_Const.xlsx" "Consts":
   [], firstYear, lastYear, nAgeClass;

(Also, note that you don’t need to have a separate .xlsx file for each table. You can have one file with different sheets named Consts, Tablele1, etc.)

3

Cheers - thank you! That works perfect!