Outputting several arrays into a single table

Similarly to what we have seen in the previous section for data input we may also write out several data arrays to a single spreadsheet range/database table, provided that all arrays have the same index sets.

The following example multiout.mos reads in two data arrays from a text file in Mosel format and outputs them to a spreadsheet range and also to a database table. Both the spreadsheet range and the database table must have been created before this model is run.

model "Output multiple data columns"
 uses "mmodbc"

 declarations
  PRODUCTS: set of string
  MACH: range
  COST: dynamic array(PRODUCTS,MACH) of real
  DUR: dynamic array(PRODUCTS,MACH) of integer
 end-declarations

! Read data
 initializations from "multiout.dat"
  COST DUR
 end-initializations

! Write data to the Access database multicol.mdb
! (this assumes that the table 'CombData' has been created previously):
 initializations to "mmodbc.odbc:debug;multicol.mdb"
  [COST,DUR] as 'CombData'
 end-initializations

! Write data to the Excel spreadsheet multicol.xls
! (this assumes that the range 'CombData' has been created previously):
 initializations to "mmsheet.excel:skiph;grow;multicol.xls"
  [COST,DUR] as 'CombData'
 end-initializations

! Alternative: specify the range/worksheet
 initializations to "mmsheet.excel:multicol.xls"
  [COST,DUR] as 'grow;[Sheet1$L4:O4]'
 end-initializations

end-model 

The input data file contains the same data as has been used for the previous example, that is, different entries are defined for every array. In the resulting output tables some entries will therefore be left empty.

COST: [(prod1 1) 1.2 (prod1 3) 2.4 (prod2 3) 3 (prod4 1) 4 (prod4 4) 3.2
       (prod3 3) 5.7 (prod3 4) 2.9 (prod3 1) 3  ]

DUR: [(prod1 1) 3 (prod1 3) 2 (prod2 3) 1 (prod2 2) 2 (prod4 1) 5
      (prod4 4) 2 (prod3 3) 2 (prod3 4) 8 ] 

The SQL version of the model above may look as follows. For the database, we have added an SQL command (`create') that creates the database table before the data is written out. This command cannot be used with Excel spreadsheets: the output range must be prepared before the Mosel model is run. In this example the fields of the database table/columns of the spreadsheet range use the same names as our Mosel model entities: this is just coincidence and by no means a necessity.

! Write data to the Access database multicol.mdb
! (create the output table and then output the data)
 SQLconnect('multicol.mdb')
 setparam("SQLdebug", true)
 SQLexecute("create table CombData (Products varchar(10), Mach integer, Cost double,
 Duration integer)")
 SQLexecute("insert into CombData(Products, Mach, Cost, Duration) values (?,?,?,?)",
            [COST,DUR])
 SQLdisconnect
 

In this example the insertion statements output quadruples (denoted by the four question marks), each consisting of an index tuple, followed by the corresponding entries of the two arrays in the given order. That is, an output tuple has the form (i,j,COST(i,j),DUR(i,j)). These tuples are written into the four selected columns of the table CombData.