Introductory example

Topics covered in this section:

The standard Mosel syntax for reading and writing data uses initializations blocks to access external files, such as

 declarations
  A: array(set of integer) of real   ! Array of unknown size (=dynamic)
  B: array(1..7) of string           ! Array of known size (=static)
 end-declarations

 initializations from "mydata.dat"
  A
  B as "MyB"
 end-initializations

where the datafile mydata.dat may have the following contents:

A: [(3) 2  (1) 4.2  (6) 9  (10) 7.5  (-1) 3]
MyB: ["Mon" "Tue" "Wed" "Thu" "Fri" "Sat" "Sun"] 

To obtain access to file types other than text files in Mosel format we merely need to modify the filename string, prefixing the name of the data source by the I/O driver we want to use followed by a colon. For instance, to read a text file in comma separated format we may use the driver prefix "mmetc.diskdata:". I/O drivers may be seen as filters that decode data from some other format, transforming it into the format used by Mosel—or the other way round. For further detail on the concept of I/O drivers, the reader is referred to the Xpress Whitepaper Generalized file handling in Mosel.

ODBC

To access spreadsheets or databases through initializations blocks using an ODBC connection we need to prefix the name of the data source (ODBC connection string as described in Section ODBC connection strings in Mosel) by the name of the ODBC I/O driver followed by a colon, that is, "mmodbc.odbc:".

Data input using odbc

As a first example we shall now see how to read data from an MS Access database into a Mosel array. Let us suppose that in a database called data.mdb you have created the following table MyDataTable with 3 fields holding the following data values:

Index_i	Index_j	Value
1	1	12.5
2	3	5.6
10	9	-7.1
3	2	1

We may then use the following model duo.mos to read in the array A4 from the database and print it out.

model "Duo input (1)"
 uses "mmodbc"

 declarations
  A4: dynamic array(range,range) of real
 end-declarations

! Use an initializations block with the odbc driver to read data
 initializations from "mmodbc.odbc:data.mdb"
  A4 as 'MyDataTable'
 end-initializations

! Print out the data we have read
 writeln('A4 is: ', A4)

end-model 

If we want to read the data from another database, say the SQLite database data.sqlite, the only change we need to make is to change the filename string to "mmodbc.odbc:data.sqlite". For a MySQL database data we would have to use the long form of the connection string: "mmodbc.odbc:DSN=mysql;DB=data". Any database we use with the model printed above needs to contain a table called `MyDataTable' with three fields, the first two (for the indices) of type integer, and the third of type double.

Data output using odbc

Outputting data from a Mosel model through the odbc I/O driver again only requires few changes to the model. Consider the following example (file duo_out.mos)—notice that the index ranges are -1, 0, 1 and 5, 6, 7 and not the standard 1, 2, 3:

model "Duo output (1)"
 uses "mmodbc"

 declarations
  A: array(-1..1,5..7) of real
 end-declarations

 A :: [ 2,  4,  6,
       12, 14, 16,
       22, 24, 26]

! Use an initializations block with the odbc driver for writing data
 initializations to "mmodbc.odbc:data.mdb"
  A as "MyOutTable1"
 end-initializations

end-model 

When we wish to write data to a database we need to prepare a suitable data table to receive the data: our table needs to be named `MyOutTable1' with fields that correspond to the data array we want to write. In our case, the first two (index) fields must be of type integer and the third field of type double.

In terms of database functionality, when writing out data with initializations to, Mosel performs an ``insert'', no replacement or update. If the data table contains already some data, for instance from previous model runs, the new output will be appended to the existing data. In the case of a database table, the insertion will fail if a key field has been defined and you are trying to write the same data entries a second time. The deletion of any existing data in the table(s) used for output must be done manually directly in the database or spreadsheet, or with the corresponding SQL commands in the Mosel model (the latter option only applies to databases). With this objective, the odbc I/O driver may be used in combination with other mmodbc functionality, for instance to execute specific SQL queries (see Section ODBC).

Spreadsheets: Excel and CSV

MS Excel spreadsheets can be accessed directly from a Mosel model with the help of the excel or xls/xlsx I/O drivers. The use of these drivers is similar to what we have seen above for the odbc driver.

Yet another method for accessing spreadsheet data consists in using the CSV driver csv with spreadsheets that have been saved in CSV format. All the dedicated spreadsheet drivers are defined by the module msheet. The main differences between the functionality and usage of the various spreadsheet drivers are summarized in the following table.

Table 1: Comparison of spreadsheet I/O drivers

	excel	xls/xlsx	csv
File type	physical file	physical file	extended file
Supported platforms	Windows	Windows, Linux, macOS	all Xpress platforms
Requirements	Excel + open interactive session	none, can be used remotely	none, can be used remotely
File creation for output	no	yes	yes
Output writing mechanism	on-screen display without saving if application running, otherwise data saved into file	data saved into file	data saved into file
Named ranges	yes	yes	no
Multiple worksheets	yes	yes	no
VBA macros	yes	no	no

The diskdata driver defined in the module mmetc also provides functionality for reading and writing CSV format text files. For the items listed in Table Comparison of spreadsheet I/O drivers it has the same charateristics as the csv driver. The requirements on the layout of the data file by diskdata are somewhat more restrictive than those made by the csv I/O driver—most importantly, this driver expects a single data table per file. The diskdata driver works in a linewise fashion (always reading all data rows of a file), as opposed to the I/O drivers of the mmsheet module that load the entire data file into memory when first accessing it and then allow the user to select specific ranges. Its use may be preferrable to csv with respect to memory use and performance when reading large data files, particularly on platforms where loading large files is time-consuming.

Data input using the excel or xls I/O drivers

We shall work with a spreadsheet that has the following contents:

	A	B	C	D	E
1
2		Index_i	Index_j	Value
3		1	1	12.5
4		2	3	5.6
5		10	9	-7.1
6		3	2	1
7

Assuming that we have named 'MyDataTable2' the cell range B3:D6 (that is, selecting just the data, excluding the header row) we can then read in these data with the following model duoexc.mos.

model "Duo input (Excel)"
 uses "mmsheet"

 declarations
  A4: dynamic array(range,range) of real
 end-declarations

! Use the excel driver for reading the data
 initializations from "mmsheet.excel:data.xls"
  A4 as 'MyDataTable2'
 end-initializations

! Print out the data we have read
 writeln('A4 is: ', A4)

end-model 

It is also possible to read the data from a range 'MyDataTable' that inludes the header row (that is, selecting the area B2:D6). In this case we need to specify the driver option skiph to skip the header line of the selected data range:

 initializations from "mmsheet.excel:data.xls"
  A4 as 'skiph;MyDataTable'
 end-initializations 

mmsheet

Yet another possibility is to use directly the worksheet and cell references instead of defining a named range (NB: the first sheet of the workbook is selected if no worksheet name is specified and the used cells of the selected sheet are assumed if no cell range selection is provided):

 initializations from "mmsheet.excel:data.xls"
  A4 as '[Sheet1$B3:D6]'
 end-initializations 

Working with named ranges has the advantage over this explicit form that modifications to the spreadsheet layout repositioning the data range will not make it necessary to modify the model.

Instead of the excel driver that can only be used with an existing MS Excel installation, we can switch to the generic xls driver by modifying the file name to

 "mmsheet.xls:data.xls" 

All else, including the driver options and the usage of named ranges, remains unchanged. Note: with a spreadsheet saved in XLSX format (files with the extension .xlsx) we would have to use the driver xlsx.

Data output using excel or xls

The following model duoexc_out.mos writes out the array A into the spreadsheet range F3:H3 that we have called 'MyOutTable3'. These cells denote the first row of the rectangular area into which we wish to write.

model "Duo output (Excel)"
 uses "mmsheet"

 declarations
  A: array(-1..1,5..7) of real
 end-declarations

 A :: [ 2,  4,  6,
       12, 14, 16,
       22, 24, 26]

! Use an initializations block with the excel driver for writing data
 initializations to "mmsheet.excel:data.xls"
  A as "grow;MyOutTable3"
 end-initializations

end-model 

In this model we have used the option grow of the excel driver to indicate that the actual output area may grow (= add more rows, the number of selected columns must be sufficient) beyond the specified output range as required by the data. Alternatively, we may also specify the complete output range such as in

 initializations to "mmsheet.excel:data.xls"
  A as '[Sheet1$F3:H11]'
 end-initializations 

or more dynamically:

 initializations to "mmsheet.excel:data.xls"
  A as '[Sheet1$F3:H' + (3+A.size-1) + ']'
 end-initializations 

If the output range has been defined to include the header row (ODBC-compatible format) we need to use again the option skiph.

 initializations to "mmsheet.excel:data.xls"
  A as 'skiph;grow;MyOutTable1'
 end-initializations 

When using the excel or xls/xlsx drivers the definition of the output range in the spreadsheet remains unchanged even if the actual output area exceeds its length. As a consequence, the output from a second model run will start at exactly the same place as the first, overwriting any previous results in the same location (but not deleting any lines if the output from the second run uses fewer rows than the first).

A specific feature of the excel driver is that the Excel spreadsheet file may remain open while writing to it from Mosel. In this case the data written to the spreadsheet does not get saved, enabling the user thus to experiment with model runs without causing any unwanted lasting effects to the output file. However, when using the xls/xlsx drivers, output data is saved directly into the spreadsheet file—the output file needs to be closed if the application used for displaying it locks write access to the file by other programs.

With the excel driver, the output file must exist prior to writing to it from Mosel. The xls/xlsx drivers will create a new spreadsheet file of the corresponding format if the specified file is not found, in which case it is obviously not possible to work with predefined ranges, but the option skiph+ can be used to output a header line, for example as in the following version that writes output into the columns F to H of the first sheet of a new spreadsheet file 'anewfile.xls':

 initializations to "mmsheet.xls:anewfile.xls"
  A as 'skiph+;[1$F:H](Index1,Index2,Value_of_A)'
 end-initializations 

Data input using the csv I/O driver

Now assume that the spreadsheet from Section Data input using odbc has been saved in CSV format into the file data.csv. We can then read in these data with the following model duosheet.mos.

model "Duo input (CSV)"
 uses "mmsheet"

 declarations
  A6: dynamic array(range,range) of real
 end-declarations

! Use the csv driver for reading the data
 initializations from "mmsheet.csv:data.csv"
  A6 as '[B3:D6]'
 end-initializations

! Print out the data we have read
 writeln('A6 is: ', A6)

end-model 

The CSV format does not support the definition of named ranges and there is no notion of 'worksheet' (when saving an Excel spreadsheet in CSV format the user selects the sheet to be saved). We therefore need to address cell ranges explicitly by indicating their position using the (letter,number) notation as shown above or alternatively, with RC (row-column) notation:

 initializations from "mmsheet.csv:data.csv"
  A6 as '[R3C2:R6C4]'
 end-initializations 

Driver options such as skiph to skip the range header line apply as before

 initializations from "mmsheet.csv:data.xls"
  A4 as 'skiph;[B2:D6]'
 end-initializations 

As for the spreadsheet drivers, it is also possible to use an empty range definition, in which case all contents of the CSV file is considered as the selected area.

 initializations from "mmsheet.csv:data.csv"
   A6 as '[]'
 end-initializations 

If we want to read all the content of columns B to D, skipping the first line, we can use the following shorthand notation (NB: this notation without row numbers, just like the row-column notation works with all mmsheet drivers):

 initializations from "mmsheet.csv:data.xls"
  A4 as 'skiph;[B:D]'
 end-initializations 

The main advantage of CSV over Excel format is its greater portability (CSV format is supported on all Xpress platforms) and the possibility to combine the csv driver freely it with other drivers, such as shmem or rmt that are not compatible with the other spreadsheet drivers.

Data output using csv

The following model duosheet_out.mos writes out the array A into the spreadsheet range F3:H3. These cells denote the first row of the rectangular area into which we wish to write.

model "Duo output (CSV)"
 uses "mmsheet"

 declarations
  A: array(-1..1,5..7) of real
 end-declarations

 A :: [ 2,  4,  6,
       12, 14, 16,
       22, 24, 26]

! Use an initializations block with the csv driver for writing data
 initializations to "mmsheet.csv:data.csv"
  A as "grow;[F3:H3]"
 end-initializations

end-model 

In this model we have used the option grow of the excel driver to indicate that the actual output area may grow (= add more rows, the number of selected columns must be sufficient) beyond the specified output range as required by the data. Alternatively, we may also specify the complete output range such as in

 initializations to "mmsheet.csv:data.csv"
  A as '[F3:H11]'
 end-initializations 

Yet another option that is available with the csv driver is the possibility to output the names of column headers via the option skiph+ (note also that just like xls/xlsx, the csv driver will create the output file if it does not exist). The following version of our output example writes output into the columns F to H of a newly created file 'anewfile.csv' where the first row of each column contains the indicated header text (note that if the file and column headers exist already, the header text is expected to match the existing).

 initializations to "mmsheet.csv:anewfile.csv"
  A as 'skiph+;[F:H](Index1,Index2,Value_of_A)'
 end-initializations 

Accessing simple CSV format files via diskdata

The diskdata I/O driver can be used to read or write simple CSV format files. This driver works with a single data table per file and whilst it is possible to make a selection among the columns (selecting columns from left to right only) the driver will always read all rows from the specified file. The diskdata driver operates in a line-wise fashion instead of loading the entire file into memory, which might be an advantage in situations where the available memory is limited. The following model duodd.mos implements the simple example we have seen in the previous sections with the diskdata I/O driver and equally using the diskdata subroutine that supports a more limited set of configuration options than the driver.

model "Duo input (diskdata)"
 uses "mmsheet"

 declarations
  A7,A8: dynamic array(range,range) of real
 end-declarations

! Use the diskdata driver for reading the data
 initializations from "mmetc.diskdata:"
  A7 as 'csv,datadd.csv'
 end-initializations

! Print out the data we have read
 writeln('A7 is: ', A7)

! Use the diskdata subroutine for reading the data
 diskdata(ETC_IN+ETC_CSV, 'datadd.csv', A8)
end-model 

With the diskdata I/O driver it is possible to select columns by column numbers or by column headers when reading data:

initializations from "mmetc.diskdata:"
  A7 as 'csv(Index_i,Index_j,Value),skiph,datadd.csv'
  A8 as 'csv(1,2,3),datadd.csv'
 end-initializations 

The 'diskdata' functionality can equally be used for writing out basic CSV-format files. With the I/O driver it is possible to configure a field separator or decimal separator character, but it does not support column selection of writing of header as this is the case for the spreadsheet drivers of msheet.

model "Duo output (diskdata)"
 uses "mmetc"

 declarations
  A: array(-1..1,5..7) of real
 end-declarations

 A :: [ 2,  4,  6,
       12, 14, 16,
       22, 24, 26]

! Using the diskdata driver
 initializations to "mmetc.diskdata:"
  A as 'csv,fsep=|;anewfile.csv'
 end-initializations

! Using the diskdata subroutine
 diskdata(ETC_OUT+ETC_CSV, 'anothernewfile.csv', A)
end-model 

Working with spreadsheet ranges

In the preceding sections we have seen various forms of how to specify or refer to data ranges in a spreadsheet file. Table Definition of spreadsheet ranges (where applicable with the corresponding formulation for the diskdata I/O driver) provides an overview of the different possibilities supported by the I/O drivers of mmsheet and where applicable also the corresponding form that can be used with the diskadata driver from the module mmetc.

Table 2: Definition of spreadsheet ranges (where applicable with the corresponding formulation for the diskdata I/O driver)

		excel, xls/xlsx	csv	diskdata
Sheet reference
by name	'[Mysheet$]'	yes	no	no
by counter	'[2$]'	yes	no	no
Named ranges	'Myrange'	yes	no	no
Explicit ranges
entire (first) sheet	'[]'	yes	yes	'csv,myfile.csv'
column range	'[B:D]'	yes	yes	no
rectangular area	'[B3:D6]'	yes	yes	no
row-column notation	'[R3C2:R6C4]'	yes	yes	no
Column selection
by column numbers	'[A:Z](#2,#5,#9)'	yes	yes	'csv(2,5,9),myfile.csv'
(unordered)	'[A:Z](#5,#9,#2)'	yes	yes	no
multiple occurrence	'[A:Z](#2,#2,#9)'	yes	yes	no
by column headers	'skiph;[A:Z](Col1,Col3)'	yes	yes	'csv(Col1,Col3),skiph,myfile.csv'
(unordered)	'skiph;[A:Z](Col3,Col1)'	yes	yes	no

The spreadsheet I/O drivers, including csv, allow users to retrieve size information about the defined ranges by specifying the option 'rangesize' (the forms marked with (*) in the code snippet below can be used with the csv driver)—note that the excel driver always returns all rows, not a row count for the populated area.

declarations
  l,l2,l3,l4,l5,l6,l7,l8,l9: list of integer   ! [height,width] of ranges
end-declarations

initialisations from 'mmsheet.xlsx:datarsz.xlsx'
  l as 'rangesize;[]'                   ! (*) Sizes for populated area in first sheet
  l2 as 'rangesize;[2$]'                ! Sizes for populated area in second sheet
  l3 as 'rangesize;[data$]'             ! Sizes for populated area in sheet 'data'
  l4 as 'rangesize;[a:c]'               ! (*) Sizes for populated area in columns A-C
  l5 as 'rangesize;[A:Z](#2,#7,#5)'     ! (*) Sizes for populated area in columns B-G
  l6 as 'rangesize;myrange'             ! Size of area defined by named range 'myrange'
  l7 as 'rangesize;myrange(#4,#2)'      ! Size of area between col.s 2-4 of 'myrange'
  l8 as 'rangesize;[R1C4:R9C8](#1,#2)'  ! (*) Size of area from selected col.s in range
  l9 as 'rangesize;[D1:H9](#1,#2)'      ! (*) Size of area from selected col.s in range
end-initializations

writeln_("Data rows in spreadsheet range:", l(1), ", number of data columns:", l(2))

Oracle

When accessing Oracle databases using initializations blocks we need to use the OCI I/O driver name, "mmoci.oci", followed by the database logon information, resulting in an extended file name such as "mmoci.oci:myname/mypassword@dbname".

The introductory examples in this section are documented in full length. However, given the similarity of the ODBC and OCI interfaces, most of the examples in the 'Examples' section of this whitepaper are presented only in their ODBC version without repeating every time the modifications to the driver name/database connection string that are required to obtain their OCI version. Nevertheless, many examples are available with an Oracle version in the Examples Database on the Xpress website.

Data input using oci

Let us assume we are working with a database dbname that contains a table `MyDataTable' with three fields, the first two (for the indices) of type integer, and the third of type float, filled with the data displayed in Section Data input using odbc.

We may then use the following model duooci.mos to read in the array A4 from the database and print it out. Only the module name in the uses statement and the extended filename for the database connection differ from what we have seen previously for an ODBC connection.

model "Duo input OCI (1)"
 uses "mmoci"

 declarations
  A4: dynamic array(range,range) of real
 end-declarations

! Use an initializations block with the odbc driver to read data
 initializations from "mmoci.oci:myname/mypassword@dbname"
  A4 as 'MyDataTable'
 end-initializations

! Print out the data we have read
 writeln('A4 is: ', A4)

end-model 

Data output using oci

Outputting data from a Mosel model through the oci I/O driver again only requires few changes to the model version for ODBC. Consider the following example (file duooci_out.mos):

model "Duo output OCI (1)"
 uses "mmoci"

 declarations
  A: array(-1..1,5..7) of real
 end-declarations

 A :: [ 2,  4,  6,
       12, 14, 16,
       22, 24, 26]

! Use an initializations block with the oci driver for writing data
 initializations to "mmoci.oci:myname/mypassword@dbname"
  A as "MyOutTable1"
 end-initializations

end-model 

The array A is written out to a data table named `MyOutTable1' in the database dbname. This table must have been created before executing the Mosel model, with fields that correspond to the data array we want to write. That is, a total of three fields where the first two (index) fields have the type integer and the third field is of type float.

In terms of database functionality, when writing out data with initializations to, Mosel performs an ``insert'', no replacement or update. If the data table already contains some data, for instance from previous model runs, the new output will be appended to the existing data. This means that the insertion will fail if key fields have been defined and you are trying to write the same data entries a second time. The deletion of any existing data in the table(s) used for output must be done manually directly in the database prior to the model run, or by adding the corresponding SQL commands to the Mosel model. For the latter, it is possible to use the oci I/O driver in combination with other mmoci functionality, such as calling specific SQL queries (see Section Oracle).