A lot of applications need to read the input data for the model from a Spreadsheet file. AIMMS supports both reading data from and writing data to spreadsheets. For retrieving data from Excel into a parameter in your AIMMS model, you can make use of the functions ExcelRetrieveParameter and ExcelRetrieveTable (see the section Excel functions in the Function Reference).

There are some differences between these two functions. The first one is that ExcelRetrieveParameter can be used to read in the data for scalar, 1, and 2 dimensional data from Excel. With the ExcelRetrieveTable, you can also read in higher dimensional data. The second difference is that the ExcelRetrieveParameter function assumes the data in Excel is in the same order as the elements in your sets in AIMMS. This means that if your set in AIMMS has the elements in the order {a, c, b}, the function will assume that the first item it reads from Excel corresponds to element a, the second item corresponds to element c, and the third item corresponds to element b. This behavior can lead to inconsistencies if you are not aware about this.

The ExcelRetrieveTable function tackles this last problem by not only reading the data, but also reading the information about the elements the data corresponds to. To be able to do this, the ExcelRetrieveTable function requires some additional arguments about ranges. In the rest of this article, we will explain the different arguments for this function.

When using the ExcelRetrieveTable function, you must provide the following arguments:

ExcelRetrieveTable(
	ExcelWorkbook           :  , 
	Parameter               :  , 
	DataRange               :  , 
	RowsRange               :  , 
	ColumnsRange            :  , 
	Sheet                   :  , 
	AutomaticallyExtendSets :  )

The ExcelWorkbook and Sheet arguments tell AIMMS which sheet and workbook to use. The parameter argument tells AIMMS to which identifier the data from Excel should be read.

The three ranges (Data, Rows, and Columns) that are required can be visualized as follows:

Range explanation

Note that in the example above, the three ranges are consecutive in Excel (i.e. they are next to each other). This does not need to be the case, i.e. there might be empty columns between the row range and the data range, and empty rows between the column range and the data range. You only need to ensure that the following conditions hold:

• dimension of parameter in AIMMS;
• Number of rows in row range = Number of rows in data range;
• Number of columns in column range = Number of columns in data range.

During the execution of the call, AIMMS will actually verify that the above conditions indeed hold and return with an error if this is not the case.

If all the conditions hold, then ExcelRetrieveTable must match the columns of the row range and the rows of the column range to the indices for the parameter denoted by the parameter argument. The way this matching done is fixed and means that the order of the indices in your AIMMS model must match the order of the indices in the following way: AIMMS will assume the first index of the AIMMS parameter corresponds to the first column in the row area, the second index to the second column, etc. After the columns of the row range, the next index corresponds with the first row of the column range. The total mapping follows the following order:

This mapping is displayed in the example below:

Small example for ranges Excel

• Row range: A8:D18
• Column range: E4:K7
• Data range: E8:K18

The final argument (AutomaticallyExtendSets) is a binary argument that instructs AIMMS to extend the sets corresponding to the indices with values from the row and column range if it has value 1. If the value is 0 and elements exist in the row or column range that do not exist in the corresponding AIMMS Set, the ExcelRetrieveTable will result in an error.

To write multi-dimensional data from AIMMS to Excel, you can use the function ExcelAssignTable. This function uses the same three ranges in the same way as the ExcelRetrieveTable function does.

Additional information: please note that with AIMMS 3.12 FR1 and up, there is also support for OpenOffice Calc. Because this means that the functionality is generic instead of specific to Excel only, the naming has changed to Spreadsheet::RetrieveTable (i.e. the Excel prefix is replaced by a Spreadsheet:: prefix for all Excel functions). Please see the Function Reference within your AIMMS installation for more details about these spreadsheet functions.

Related posts:

1. Determine consecutive range in Excel
2. Quick Win on AIMMS Data Page
3. Inspecting SQL statements created by AIMMS
4. Getting value of a dynamic identifier