MADS includes features to import or export data when running calculations for a given Plan. In the current version the only external format being supported is the ODF spreadsheet. ODF (Open Document Format) is an open-source file format for representing electronic documents such as spreadsheets, charts, presentations and word processing documents, published as an ISO/IEC international standard. ODF is used by a growing number of software products, in particular the open-source and free OpenOffice suite of applications. ODF spreadsheet file names use the ‘.ods’ extension.
In order to import or export data, it is required to specify the path name of an ODF spreadsheet file, using the Filespec command in the project text file. This can be done either in the Headers section or within a Script . The syntax is:
fileSpec ID = ‘fileSpec’ ;
Example : fileSpec F1 = “c:/Users/msimeon/Documents/test2.ods”;
If the path name does not correspond to an existing file, then import would fail, as a data source is obviously required for data import. In the case of data export, a message-window will ask the user if an ODF spreadsheet file should be created with that path name.
Data export operations would also fail if the external file, while perfectly valid, is opened by an other application and thus cannot be saved.
There are three types of export commands:
Timeseries Export commands can be specified as part of a Script . The syntax is :
export ID into ‘rangeName’ in ‘worksheetName’ in FileSpecID;
ID is a qualified reference to a commodity, herd of variable, such as Rice~consQ or TotalIncome. The rangeName specification is a string with the name of a range of cells in the work-sheet where the data is to be exported. The work-sheet name specification is optional, its default name being ‘Sheet1’. For the operation to succeed the following conditions must be set:
Table Export commands can be specified as part of a Script . The syntax is :
export table ID into ‘worksheetName’ in FileSpecID;
ID is a reference to a table. There will be only that table in the specified work-sheet; if it pre-exists it will be overwritten, if not it will be created. For the operation to succeed the file specified by its specification ID must be an existing ODF spreadsheet file (or it could get created by Mads) and not be opened by an other application (see above).
The IRR function that can be used as part of an Expression within a Script includes an optional section to specify the export of its output to an ODF spreadsheet file. The syntax is :
( export into ‘worksheetName’ in FileSpecID )
The rules are the same as for the Table Export Command above: if the specified work-sheet pre-exists it will be overwritten, if not it will be created. For the operation to succeed the file specified by its specification ID must be an existing ODF spreadsheet file (or it could get created by Mads) and not be opened by an other application.
The data import function reads a series of values from a named cell range in an external ODF spreadsheet. The function returns a time series of values, starting at year 1. If there are less values in the read range than years in the time series, the last value is copied to all remaining years. If there are more values than years, excess values are ignored. The syntax is :
time series = read (‘rangeName’ in ‘worksheetName’ from FileSpecID)
The work-sheet name specification is optional, its default name being ‘Sheet1’. For the operation to succeed the following conditions must be set:
The read function can be used either as part of an Expression within a Script , or when a time series parameter is required in the definition of a Plan or Herd. In the case of a Plan the read function can be used to specify the level of a commodity, herd or inner plan within that plan. In the case of a Herd , the read function can be used to specify any of the herd parameters that require time series of values.
The templates produced using the Ctrl-Space key combination show [ 0 0 ] whenever a time series of values is required; the proper values would then be entered between the brackets. To read data from an external file, the read function template ( read (ID in ‘table’? from F2) ) can be triggered by positioning the cursor before the ‘[ 0 0 ]’ block, or erasing it, and using Ctrl-Space again.