The provider is a library from the library repository that is responsible for translating the data source specific format into something that DataLink understands. In the AIMMS model DataLink functions are called and DataLink will use the provider to read and write in the particular format of the data source. So the provider cannot act on its own, it always needs DataLink. For installation see Installation and setup.
Providers are not used directly and so they can be used following the instruction via DataLink. Still, not all providers may behave the same. Because they all have to deal with the peculiarities of the specific format, not all features are guaranteed to work. Some providers may have some limitations. Also it may not be apparent directly how the source is translated to the schema of tables with column names. The extra information about specific providers are given below.
The XLSProvider can read and write Excel files:
- Files with extension
xlswere used by Excel until 2007.
- Files with extension
xlsxare based on Open Office XML format and are used by Excel since 2007.
Each worksheet is considered a table and the name of the sheets are the table names.
AIMMS also has an other library for dealing with excels files called AimmsXLLibrary. This can be used to address the content of a spreadsheet using spreadsheet’s notation for cells using letter-number combinations. DataLink does not use those letter-number combinations except in some error messages.
Reading and Writing¶
The first non-empty row is considered the header containing the names of the columns. The content of all cells in this row is converted to a string and matched against the column names in the data map. If a match is found we have a valid column.
Reading the data happens row by row and only those cells are read that are in valid columns. Empty rows are skipped and the whole sheet is read to the end.
- The header with the column names is written in column 1, starting at cell A1. Then all data is written row by row under the column names in header.
Reading ignores all columns that are not valid, and so it is possible to use these columns for comments and other information that should not be read.
- Table Name
- Because of a limitation in the xls files, table names can not be longer than 31 characters.
- A cell can contain a formula and Excel will show the computed value. DataLink does not support formula and will see these cells as an errors.
The CSVProvider can be used for Comma Separated Value (CVS) files with extension
.csv. These are normal text files in which a specific character call the separator is used to split each line data into column elements. The default separator is the comma and in can be changed in specifying ReadWriteAttributes.
- Data source
- The directory containing the csv files. To specify the current directory use a dot.
- Table name
- File name of the CSV file minus the extension
The permissions of the file system determine the permission to read or write and trying to do so without the proper permission results in an error.
In some languages the comma is used as decimal “period”, so a more language independent separator would be the semi colon
Reading and Writing¶
- The first row is considered to be the header. Then the file is read line by line, where each line is split into separate values using the separator. This means that strings do not have to be between quotes. If however the value contains the separator character then the values must be enclosed between quotes.
All values are converted to strings and written line by line with the separator character between them. The result can be controlled using the
Precisioncolumn attributes (see Add the Map or The New Data Map about how to specify column attributes). The width is the number of characters of the value (so it forms the column width). The precision attribute is different for strings and numerical value:
- The precision defines the max number of characters. If the actual value has more characters it gets truncated.
- The precision defines the number of decimals.
- The Calender format in AIMMS is send in an internal binary format to DataLink. The current CSVProvider cannot translate this into a string that is needed for the CSV format, so DateTime is not supported yet.
Last Updated: October, 2019