Spreadsheets and databases: working with multiple data tables and arrays


Type:	Programming
Rating:	2 (easy-medium)
Description:	A database table (or a spreadsheet range) may contain in its different fields (columns) the data for several Mosel arrays and inversely, a Mosel array may correspond to several data tables (ranges). We have here examples of various different cases: using the odbc driver, formulated with SQL statements, using the excel driver, using the oci driver, using the xls driver, and using the csv driver. for three sets of examples: Reading several arrays from a single database table/spreadsheet range (multicol.mos) Outputting several arrays into a single database table/spreadsheet range (multiout.mos) Reading an array from several tables/ranges (multitab.mos)
File(s):	multicol.mos, multicol2.mos, multicol3.mos, multicol4.mos, multicol5.mos, multicol6.mos, multiout.mos, multiout2.mos, multiout3.mos, multiout4.mos, multiout5.mos, multiout6.mos, multitab.mos, multitab2.mos, multitab3.mos, multitab4.mos, multitab5.mos, multitab6.mos
Data file(s):	multicol.dat, multicdd.dat, multicol.csv, multicol.mdb, multicol.xls, multicol.xlsx, multicol.sqlite, multiout.dat, multicol.csv, multicol.mdb, multicol.xls, multicol.xlsx, multicol.sqlite, multitab.csv, multitab.dat, multitab.mdb, multitab.xls, multitab.xlsx, multitab.sqlite

multicol.mos

(!*******************************************************
   Mosel Example Problems 
   ======================

   file multicol.mos
   `````````````````
   Reading several data arrays from a single table.
   - Using 'initializations from' with odbc IO driver -
       
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2006, rev. Jan. 2014
*******************************************************!)

model "Multiple data columns"
 uses "mmetc" , "mmodbc"

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

! **** Read data in Mosel format ****
 initializations from "multicol.dat"
  [COST,DUR] as "ProdData"
 end-initializations

 writeln("Mosel format:"); writeln(COST); writeln(DUR)


! **** Read data in diskdata format ****
 initializations from "mmetc.diskdata:sparse"
  [COST1,DUR1] as "multicdd.dat"
 end-initializations

 writeln("'diskdata' format:"); writeln(COST1); writeln(DUR1)

 
! **** Reading data from databases ****
! This assumes the spreadsheet/database contains a table "ProdData" 
! in sparse format (i.e., with indices) with the columns "COST" and "DUR"

(! Read data from the Excel spreadsheet multicol.xls
!
 initializations from "mmodbc.odbc:multicol.xls"
  [COST2,DUR2] as 'ProdData'
 end-initializations
!)

! Read data from the Access database multicol.mdb
 initializations from "mmodbc.odbc:multicol.mdb"
  [COST2,DUR2] as 'ProdData'
 end-initializations

! Read data from the mysql database multicol (not provided)
(!
 initializations from "mmodbc.odbc:multicol"
  [COST2,DUR2] as 'ProdData'
 end-initializations
!)

! Read data from the SQLite database multicol.sqlite via ODBC
(!
 initializations from "mmodbc.odbc:DSN=sqlite;DATABASE=multicol.sqlite"
  [COST2,DUR2] as 'ProdData'
 end-initializations
!)
! Read data from the SQLite database multicol.sqlite using direct access
(!
 initializations from "mmodbc.odbc:multicol.sqlite"
  [COST2,DUR2] as 'ProdData'
 end-initializations
!)
 writeln("ODBC:"); writeln(COST2); writeln(DUR2)
 
end-model

multicol2.mos

(!*******************************************************
   Mosel Example Problems 
   ======================

   file multicol2.mos
   ``````````````````
   Reading several data arrays from a single table.
   - Using SQL commands -
       
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2002, rev. Sep. 2018
*******************************************************!)

model "Multiple data columns (2)"
 uses "mmetc" , "mmodbc"

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

! **** Read data in Mosel format ****
 initializations from "multicol.dat"
  [COST,DUR] as "ProdData"
 end-initializations

 writeln("Mosel format:"); writeln(COST); writeln(DUR)


! **** Read data in diskdata format ****
 diskdata(ETC_IN+ETC_SPARSE, "multicdd.dat", [COST1,DUR1])

! Alternatively: use diskdata IO driver:
(!
 initializations from "mmetc.diskdata:sparse"
  [COST1,DUR1] as "multicdd.dat"
 end-initializations
!)
 writeln("'diskdata' format:"); writeln(COST1); writeln(DUR1)

 
! **** Reading data from databases ****
! This assumes the spreadsheet/database contains a table "ProdData" 
! in sparse format (i.e., with indices) with the columns "COST" and "DUR"
 
! Read data from the Excel spreadsheet multicol.xls
!  SQLconnect('DSN=Excel Files;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselData/multicol.xls')
!  SQLconnect('multicol.xls')

! Read data from the Access database multicol.mdb
!  SQLconnect('DSN=MS Access Database;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselData/multicol.mdb')
  SQLconnect('multicol.mdb')

! Read data from the mysql database multicol (not provided)
! SQLconnect('DSN=mysql;DB=multicol')

! Read data from the SQLite database multicol.sqlite
! SQLconnect('DSN=sqlite;DATABASE=multicol.sqlite')
! SQLconnect('multicol.sqlite')

 setparam("SQLverbose",true)

 SQLexecute("select * from ProdData ", [COST2,DUR2])
 
 SQLdisconnect

 writeln("ODBC:"); writeln(COST2); writeln(DUR2)

end-model

multicol3.mos

(!*******************************************************
   Mosel Example Problems 
   ======================

   file multicol3.mos
   ``````````````````
   Reading several data arrays from a single table.
   - Using 'initializations from' with excel IO driver -
       
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2007, rev. Jul. 2010
*******************************************************!)

model "Multiple data columns (3)"

 declarations
  PRODUCTS: set of string
  MACH: range
  COST2: dynamic array(PRODUCTS,MACH) of real
  DUR2: dynamic array(PRODUCTS,MACH) of integer
 end-declarations
 
! **** Reading data from an Excel spreadheet ****
! This assumes the spreadsheet contains a table "ProdData" in
! sparse format (i.e., with indices) with the columns "COST" and "DUR"

 initializations from "mmsheet.excel:multicol.xls"
  [COST2,DUR2] as 'skiph;ProdData'
 end-initializations

 writeln("Excel:"); writeln(COST2); writeln(DUR2)
 
end-model

multicol4.mos

(!*******************************************************
   Mosel Example Problems 
   ======================

   file multicol4.mos
   ``````````````````
   Reading several data arrays from a single table.
   - Using 'initializations' and OCI statements -
       
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012
*******************************************************!)

model "Multiple data columns (OCI)"
 uses "mmoci"

 parameters
  DB="myname/mypassword@dbname"     ! Login to Oracle database (not provided)
 end-parameters

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


! **** Using SQL statements ****
! This assumes the database contains a table "ProdData" 
! in sparse format (i.e., with indices) with the columns "COST" and "DUR"

 setparam("OCIdebug",true)
 OCIlogon(DB)
 writeln("Connection number: ",getparam("OCIconnection"))
 OCIexecute("select * from ProdData ", [COST1,DUR1])
 OCIlogoff

 writeln("OCI:"); writeln(COST1); writeln(DUR1)


! **** Using 'initializations from' ****

 initializations from "mmoci.oci:debug;"+DB
  [COST2,DUR2] as 'ProdData'
 end-initializations

 writeln("initializations from:"); writeln(COST2); writeln(DUR2)

end-model

**************************************************


! Creation of data tables in an Oracle database:

 declarations
  tsucc: array ({false,true}) of string
 end-declarations

 tsucc(false):="failed"; tsucc(true):="succeeded"

 initializations from "multicol.dat"
  [COST,DUR] as "ProdData"
 end-initializations

 setparam("OCIdebug",true)
 OCIlogon(DB)
 writeln("Connection number: ",getparam("OCIconnection"))

 OCIexecute("drop table ProdData")
 OCIexecute("create table ProdData (Products varchar(10), Mach integer, Cost float, Duration integer)")
 writeln(" - Create ProdData (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into ProdData (Products, Mach, Cost, Duration) values (:1, :2, :3, :4)", [ COST,DUR ])
 writeln(" - Insert values into ProdData (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")

OCIlogoff

multicol5.mos

(!*******************************************************
   Mosel Example Problems 
   ======================

   file multicol5.mos
   ``````````````````
   Reading several data arrays from a single table.
   - Using 'initializations from' with xls IO driver -
       
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012
*******************************************************!)

model "Multiple data columns (generic spreadsheet)"
 uses "mmsheet"

 parameters
!  CSTR= 'mmsheet.xls:multicol.xls'
  CSTR= 'mmsheet.xlsx:multicol.xlsx'
 end-parameters

 declarations
  PRODUCTS: set of string
  MACH: range
  COST2: dynamic array(PRODUCTS,MACH) of real
  DUR2: dynamic array(PRODUCTS,MACH) of integer
 end-declarations
 
! **** Reading data from a spreadheet ****
! This assumes the spreadsheet contains a table "ProdData" in
! sparse format (i.e., with indices) with the columns "COST" and "DUR"

 initializations from CSTR
  [COST2,DUR2] as 'skiph;ProdData'
 end-initializations

 writeln("XLS:"); writeln(COST2); writeln(DUR2)
 
end-model

multicol6.mos

(!*******************************************************
   Mosel Example Problems 
   ======================

   file multicol6.mos
   ``````````````````
   Reading several data arrays from a single table.
   - Using 'initializations from' with csv IO driver -
       
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012
*******************************************************!)

model "Multiple data columns (CSV)"
 uses "mmsheet"

 parameters
  CSTR= 'mmsheet.csv:multicol.csv'
 end-parameters

 declarations
  PRODUCTS: set of string
  MACH: range
  COST2: dynamic array(PRODUCTS,MACH) of real
  DUR2: dynamic array(PRODUCTS,MACH) of integer
 end-declarations
 
! **** Reading data from a spreadheet ****
! This assumes the spreadsheet contains a data table in
! sparse format (i.e., with indices) with the columns "COST" and "DUR"

 initializations from CSTR
  [COST2,DUR2] as '[A3:D11]'
 end-initializations

 writeln("CSV:"); writeln(COST2); writeln(DUR2)
 
end-model

multiout.mos

(!*******************************************************
   Mosel Example Problems 
   ======================

   file multiout.mos
   `````````````````
   Output several data arrays into a single table.
   - Using 'initializations to' with odbc driver -
       
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2006, rev. Jan. 2014
*******************************************************!)

model "Output multiple data columns"
 uses "mmetc" , "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

! **** Writing data to text files ****
! Write out data in Mosel format: add data to the input file
 initializations to "multiout.dat"
  [COST,DUR] as "CombData"
 end-initializations

! Write out data in diskdata format
 initializations to "mmetc.diskdata:sparse"
  [COST,DUR] as "multiodd.dat"
 end-initializations

! **** Writing data to databases ****
 setparam("SQLverbose",true)

! 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 "mmodbc.odbc:debug;multicol.xls"
  [COST,DUR] as 'CombData'
 end-initializations
!)
! Write data to the SQLite database multicol.sqlite  
 initializations to "mmodbc.odbc:debug;multicol.sqlite"
  [COST,DUR] as 'CombData'
 end-initializations

end-model

multiout2.mos

(!*******************************************************
   Mosel Example Problems 
   ======================

   file multiout2.mos
   ``````````````````
   Output several data arrays into a single table.
   - Using SQL commands -
       
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2002, rev. Sep. 2018
*******************************************************!)

model "Output multiple data columns (2)"
 uses "mmetc" , "mmodbc"

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

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

! **** Writing data to text files ****
! Write out data in Mosel format: add data to the input file
 initializations to "multiout.dat"
  [COST,DUR] as "CombData"
 end-initializations

! Write out data in diskdata format
 diskdata(ETC_OUT+ETC_SPARSE, "multiodd.dat", [COST,DUR])

! **** Writing data to databases ****
! Write data to the Access database multicol.mdb
! SQLconnect('DSN=MS Access Database;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselData/multicol.mdb')
 SQLconnect('multicol.mdb')

 setparam("SQLverbose",true)
 setparam("SQLdebug",true)

 SQLtables(Tables)
 if findfirst(Tables, "CombData")>0 then SQLexecute("drop table CombData"); end-if
 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

! Write data to the mysql database multicol (not provided)
! SQLconnect('DSN=mysql;DB=multicol')

! Write data to the SQLite database multicol
! SQLconnect('DSN=sqlite;DATABASE=multicol')
 SQLconnect('multicol.sqlite')

 setparam("SQLverbose",true)

 Tables:=[]; SQLtables(Tables)
 if findfirst(Tables, "CombData")>0 then SQLexecute("drop table CombData"); end-if
 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

! Write data to the Excel spreadsheet multicol.xls  
! (this assumes that the range 'CombData' has been created previously):
! SQLconnect('DSN=Excel Files;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselData/multicol.xls')
(!
 SQLconnect('multicol.xls')

 setparam("SQLverbose",true)

 SQLexecute("insert into CombData (Products, Mach, Cost, Duration) values (?,?,?,?)", [COST,DUR])

 SQLdisconnect
!)
end-model

multiout3.mos

(!*******************************************************
   Mosel Example Problems 
   ======================

   file multiout3.mos
   `````````````````
   Output several data arrays into a single table.
   - Using 'initializations to' with excel IO driver -
       
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2007, rev. Dec. 2012
*******************************************************!)

model "Output multiple data columns (3)"
 uses "mmsheet"

 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

! **** Writing data to the Excel spreadsheets multicol.xls ****

! Options:
! skiph - the range 'CombData' includes a header line
! grow  - only the starting line of the range is specified
 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

multiout4.mos

(!*******************************************************
   Mosel Example Problems 
   ======================

   file multiout4.mos
   ``````````````````
   Output several data arrays into a single table.
   - Using 'initializations' and OCI statements -
       
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012
*******************************************************!)

model "Output multiple data columns (OCI)"
 uses "mmoci"

 parameters
  DB="myname/mypassword@dbname"     ! Login to Oracle database (not provided)
 end-parameters

 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


! **** Using SQL statements ****

 setparam("OCIdebug",true)
 OCIlogon(DB)
 writeln("Connection number: ", getparam("OCIconnection"))

 OCIexecute("create table CombData (Products varchar(10), Mach integer, Cost double, Duration integer)")
 OCIexecute("delete from CombData")
 OCIexecute("insert into CombData(Products, Mach, Cost, Duration) values (:1,:2,:3,:4)", [COST,DUR])

 OCIlogoff


! **** Using 'initializations to' ****
! (this assumes that the table 'CombData' has been created previously)

 initializations to "mmoci.oci:debug;"+DB
  [COST,DUR] as 'CombData'
 end-initializations


end-model

multiout5.mos

(!*******************************************************
   Mosel Example Problems 
   ======================

   file multiout5.mos
   ``````````````````
   Output several data arrays into a single table.
   - Using 'initializations to' with the xls IO driver -
       
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012
*******************************************************!)

model "Output multiple data columns (generic spreadsheet)"
 uses "mmsheet"

 parameters
  CSTR= 'mmsheet.xls:'
  DFILE= 'multicol.xls'
!  CSTR= 'mmsheet.xlsx:'
!  DFILE= 'multicol.xlsx'
 end-parameters

 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

! **** Writing data to the spreadsheet multicol.xls ****

! Options:
! skiph - the range 'CombData' includes a header line
! grow  - only the starting line of the range is specified
 initializations to CSTR + "skiph;grow;" + DFILE
  [COST,DUR] as 'CombData'
 end-initializations

! Alternative: specify the range/worksheet
 initializations to CSTR + DFILE
  [COST,DUR] as 'grow;[Sheet1$L4:O4]'
 end-initializations

end-model

multiout6.mos

(!*******************************************************
   Mosel Example Problems 
   ======================

   file multiout6.mos
   ``````````````````
   Output several data arrays into a single table.
   - Using 'initializations to' with the csv IO driver -
       
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012
*******************************************************!)

model "Output multiple data columns (CSV)"
 uses "mmsheet"

 parameters
  CSTR= 'mmsheet.csv:multiout.csv'
 end-parameters

 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

! **** Writing data to the spreadsheet multiout.csv ****
! Options:
! skiph - the range includes a header line that needs to be skipped
! grow  - only the starting line of the range is specified
 initializations to CSTR
  [COST,DUR] as 'skiph;grow;[F2:I2]'
 end-initializations

end-model

multitab.mos

(!*******************************************************
   Mosel Example Problems 
   ======================

   file multitab.mos
   `````````````````
   Reading an array from several data tables.
   Output parts of an array into several tables.
   - Using 'initializations from' with odbc I/O driver -
       
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2006, rev. May 2017
*******************************************************!)

model "Multiple data sources"
 uses "mmodbc", "mmsystem"

 declarations
  CUST: set of integer
  PERIOD: range
  INCOME,INCOME1,INCOME2,INCOME3: dynamic array(CUST,PERIOD) of real
 end-declarations

 initializations from "multitab.dat"
  INCOME as "COLDAT1"
  INCOME as "COLDAT2"
  INCOME as "COLDAT3"
 end-initializations

 writeln(INCOME)

 setparam("SQLverbose", true)

! Different ways of reading the data from a spreadsheet or database
! CNCT:= 'mmodbc.odbc:multitab.xls'

 CNCT:= 'mmodbc.odbc:multitab.mdb'

! CNCT:= 'mmodbc.odbc:multitab.sqlite'
! CNCT:= 'mmodbc.odbc:DSN=sqlite;DATABASE=multitab.sqlite'

! Method 1: Data in columns, with CUST index value included
 initializations from CNCT
  INCOME1 as 'COLDAT1'
  INCOME1 as 'COLDAT2'
  INCOME1 as 'COLDAT3'
 end-initializations

 writeln("1: ", INCOME1)
 
! Method 2: Data in columns, without CUST index value

 procedure readcol(cust:integer, table:string)
  declarations
   TEMP: array(PERIOD) of real
  end-declarations
 
  initializations from CNCT
   TEMP as table
  end-initializations
  forall(p in PERIOD) INCOME2(cust,p):=TEMP(p)
 end-procedure
 
 forall(c in CUST) readcol(c, "COLDAT"+c+"A") 
 writeln("2: ", INCOME2)
 
! Method 3: Data in rows, without CUST index value

 procedure readrow(cust:integer, table:string)
  declarations
   TEMP: array(1..2,PERIOD) of real
  end-declarations

  initializations from CNCT
   TEMP as 'noindex;'+table
  end-initializations
  forall(p in PERIOD) INCOME3(cust,p):=TEMP(2,p)
 end-procedure
 
 finalize(PERIOD)                       ! The index sets must be known+fixed
 forall(c in CUST) readrow(c, "ROWDAT"+c) 
 writeln("3: ", INCOME3)


! ******** Output an array into several tables ********

 forall(c in CUST)
  initializations to "multitabout.dat"
   evaluation of array(c1=c,p in PERIOD) INCOME1(c1,p) as "COLDAT"+c
  end-initializations

! Create the tables for data output
 fcopy('multitab.mdb','multitabout.mdb')
 CNCTOUT:= 'multitabout.mdb'
! CNCTOUT:= 'multitabout.sqlite'
 SQLconnect(CNCTOUT)
 forall(c in CUST) do
  SQLexecute("drop table COLDAT"+c)
  SQLexecute("create table COLDAT"+c+" (CUST integer,PERIOD integer,INCOME float)")
  SQLexecute("drop table COLDAT"+c+"A")
  SQLexecute("create table COLDAT"+c+"A (PERIOD integer,INCOME float)")
 end-do
 SQLdisconnect

! Method 1: Data in columns, with CUST index value included
 CNCTOUT:= 'mmodbc.odbc:'+CNCTOUT
 forall(c in CUST) do
  initializations to CNCTOUT
   evaluation of array(c1=c,p in PERIOD) INCOME1(c1,p) as "COLDAT"+c
  end-initializations
 end-do

! Method 2: Data in columns, without CUST index value
 forall(c in CUST) do
  initializations to CNCTOUT
   evaluation of array(p in PERIOD) INCOME1(c,p) as "COLDAT"+c+"A"
  end-initializations
 end-do

end-model

multitab2.mos

(!*******************************************************
   Mosel Example Problems 
   ======================

   file multitab2.mos
   ``````````````````
   Reading an array from several data tables.
   Output parts of an array into several tables.
   - Using SQL commands -
       
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2002, rev. Sep. 2018
*******************************************************!)

model "Multiple data sources (2)"
 uses "mmodbc", "mmsystem"

 declarations
  CUST: set of integer
  PERIOD: range
  INCOME,INCOME1,INCOME2,INCOME3: dynamic array(CUST,PERIOD) of real
 end-declarations

 initializations from "multitab.dat"
  INCOME as "COLDAT1"
  INCOME as "COLDAT2"
  INCOME as "COLDAT3"
 end-initializations

 writeln(INCOME)

 setparam("SQLverbose", true)

! Different ways of reading the data from a spreadsheet or database
! SQLconnect('DSN=Excel Files;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselData/multitab.xls')
! SQLconnect('multitab.xls')

! SQLconnect('DSN=MS Access Database;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselData/multitab.mdb')
 SQLconnect('multitab.mdb')

! SQLconnect('DSN=sqlite;DATABASE=multitab.sqlite')
! SQLconnect('multitab.sqlite')


! Method 1: Data in columns, with CUST index value included

 SQLexecute("select CUST,PERIOD,INCOME from COLDAT1", INCOME1)
 SQLexecute("select CUST,PERIOD,INCOME from COLDAT2", INCOME1)
 SQLexecute("select CUST,PERIOD,INCOME from COLDAT3", INCOME1)
 writeln("1: ", INCOME1)
 
! Method 2: Data in columns, without CUST index value

 procedure readcol(cust:integer, table:string)
  declarations
   TEMP: array(PERIOD) of real
  end-declarations
  
  SQLexecute("select PERIOD,INCOME from "+table, TEMP)
  forall(p in PERIOD) INCOME2(cust,p):=TEMP(p)
 end-procedure
 
 forall(c in CUST) readcol(c, "COLDAT"+c+"A") 
 writeln("2: ", INCOME2)
 
! Method 3: Data in rows, without CUST index value
 procedure readrow(cust:integer, table:string)
  declarations
   TEMP: array(1..2,PERIOD) of real
  end-declarations

  SQLexecute("select * from "+table, TEMP)
  forall(p in PERIOD) INCOME3(cust,p):=TEMP(2,p)
 end-procedure

 finalize(PERIOD)                 ! The index sets must be known+fixed
 setparam("sqlndxcol",false)      ! Data specified in dense format (no indices)
 forall(c in CUST) readrow(c, "ROWDAT"+c) 
 setparam("sqlndxcol",true)

 writeln("3: ", INCOME3)


! ******** Output an array into several tables ********

 forall(c in CUST)
  initializations to "multitabout.dat"
   evaluation of array(c1=c,p in PERIOD) INCOME1(c1,p) as "COLDAT"+c
  end-initializations

 fcopy('multitab.mdb','multitabout.mdb')
 SQLconnect('multitabout.mdb')
! SQLconnect('multitabout.sqlite')

! Method 1: Data in columns, with CUST index value included
 forall(c in CUST) do
  SQLexecute("drop table COLDAT"+c)
  SQLexecute("create table COLDAT"+c+" (CUST integer,PERIOD integer,INCOME float)")
  SQLexecute("insert into COLDAT"+c+"(CUST,PERIOD,INCOME) values (?,?,?)", 
   array(c1=c,p in PERIOD) INCOME1(c1,p))
 (!or:
  SQLexecute("insert into COLDAT"+c+"(CUST,PERIOD,INCOME) values ("+c+",?,?)", array(p in PERIOD) INCOME1(c,p))
 !)
 end-do

! Method 2: Data in columns, without CUST index value
 forall(c in CUST) do
  SQLexecute("drop table COLDAT"+c+"A")
  SQLexecute("create table COLDAT"+c+"A (PERIOD integer,INCOME float)")
  SQLexecute("insert into COLDAT"+c+"A(PERIOD,INCOME) values (?,?)", 
   array(p in PERIOD) INCOME1(c,p))
 end-do

 SQLdisconnect

end-model

multitab3.mos

(!*******************************************************
   Mosel Example Problems 
   ======================

   file multitab3.mos
   `````````````````
   Reading an array from several data tables.
   Output parts of an array into several tables.
   - Using 'initializations from' with excel I/O driver -
       
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2007, rev. May 2017
*******************************************************!)

model "Multiple data sources (3)"
 uses "mmsheet", "mmsystem"
 
 parameters
  CNCT = 'mmsheet.excel:skiph;multitab.xls'
  CNCTOUT= 'mmsheet.excel:multitabout.xls'
 end-parameters

 declarations
  CUST: set of integer
  PERIOD: range
  INCOME1,INCOME2,INCOME3: dynamic array(CUST,PERIOD) of real
 end-declarations

! Different ways of reading the data from an Excel spreadsheet
! (spreadsheet ranges include a header line -> use option 'skiph')
 
! Method 1: Data in columns, with CUST index value included
 initializations from CNCT
  INCOME1 as 'COLDAT1'
  INCOME1 as 'COLDAT2'
  INCOME1 as 'COLDAT3'
 end-initializations

 writeln("1: ", INCOME1)
 
! Method 2: Data in columns, without CUST index value

 procedure readcol(cust:integer, table:string)
  declarations
   TEMP: array(PERIOD) of real
  end-declarations
 
  initializations from CNCT
   TEMP as table
  end-initializations
  forall(p in PERIOD) INCOME2(cust,p):=TEMP(p)
 end-procedure
 
 forall(c in CUST) readcol(c, "COLDAT"+c+"A") 
 writeln("2: ", INCOME2)
 
! Method 3: Data in rows, without CUST index value

 procedure readrow(cust:integer, table:string)
  declarations
   TEMP: array(1..2,PERIOD) of real
  end-declarations

  initializations from CNCT
   TEMP as 'noindex;'+table
  end-initializations
  forall(p in PERIOD) INCOME3(cust,p):=TEMP(2,p)
 end-procedure
 
 finalize(PERIOD)                       ! The index sets must be known+fixed
 forall(c in CUST) readrow(c, "ROWDAT"+c) 
 writeln("3: ", INCOME3)


! ******** Output an array into several tables ********

 fcopy("multitab.xls","multitabout.xls")
 fopen(CNCTOUT, F_OUTPUT)

 forall(c in CUST)
  initializations to CNCTOUT
   ! Output into separate tables within one sheet, CUST index value included
    evaluation of array(c1=c,p in PERIOD) INCOME1(c1,p) as "grow;skiph;[Sheet2$R2C"+(c*4)+":R2C"+(c*4+2)+"]"

   ! Output without CUST index value
    evaluation of array(p in PERIOD) INCOME1(c,p) as "grow;skiph;[Sheet2$R15C"+(c*3)+":R15C"+(c*3+1)+"]"    
  end-initializations
 fclose(F_OUTPUT)

end-model

multitab4.mos

(!*******************************************************
   Mosel Example Problems 
   ======================

   file multitab4.mos
   ``````````````````
   Reading an array from several data tables.
   Output parts of an array into several tables.
   - Using 'initializations' and OCI statements -
       
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012, rev. May 2017
*******************************************************!)

model "Multiple data sources (OCI)"
 uses "mmoci", "mmsystem"

 parameters
  DB="myname/mypassword@dbname"     ! Login to Oracle database (not provided)
  CNCT="mmoci.oci:debug;"+DB
 end-parameters

 declarations
  CUST: set of integer
  PERIOD: range
  INCOME,INCOME1,INCOME2,INCOME3: dynamic array(CUST,PERIOD) of real
 end-declarations


! **** Using SQL statements ****

 setparam("OCIdebug",true)
 OCIlogon(DB)
 writeln("Connection number: ",getparam("OCIconnection"))

! Method 1: Data in columns, with CUST index value included

 OCIexecute("select CUST,PERIOD,INCOME from COLDAT1", INCOME1)
 OCIexecute("select CUST,PERIOD,INCOME from COLDAT2", INCOME1)
 OCIexecute("select CUST,PERIOD,INCOME from COLDAT3", INCOME1)
 writeln("1: ", INCOME1)
 
! Method 2: Data in columns, without CUST index value

 procedure readcol(cust:integer, table:string)
  declarations
   TEMP: array(PERIOD) of real
  end-declarations
  
  OCIexecute("select PERIOD,INCOME from "+table, TEMP)
  forall(p in PERIOD) INCOME2(cust,p):=TEMP(p)
 end-procedure
 
 forall(c in CUST) readcol(c, "COLDAT"+c+"A") 
 writeln("2: ", INCOME2)
 
! Method 3: Data in rows, without CUST index value
 procedure readrow(cust:integer, table:string)
  declarations
   TEMP: array(1..2,PERIOD) of real
  end-declarations

  OCIexecute("select * from "+table, TEMP)
  forall(p in PERIOD) INCOME3(cust,p):=TEMP(2,p)
 end-procedure

 finalize(PERIOD)                 ! The index sets must be known+fixed
 setparam("ocindxcol",false)      ! Data specified in dense format (no indices)
 forall(c in CUST) readrow(c, "ROWDAT"+c) 
 setparam("ocindxcol",true)

 writeln("3: ", INCOME3)

! ******** Output an array into several tables ********
(!
! Method 1: Data in columns, with CUST index value included
 forall(i in 1..3) do
  OCIexecute("drop table COLDATOUT" + i)
  OCIexecute("create table COLDATOUT"+i+" (CUST integer, PERIOD integer, INCOME float)")
  OCIexecute("insert into COLDATOUT"+c+" (CUST,PERIOD,INCOME) values (:1, :2, :3)", 
   array(c1=c,p in PERIOD) INCOME1(c1,p))
 end-do

! Method 2: Data in columns, without CUST index value
 forall(i in 1..3) do
  OCIexecute("drop table COLDATOUT" + i)
  OCIexecute("create table COLDATOUT"+i+" (CUST integer, PERIOD integer, INCOME float)")
  OCIexecute("drop table COLDATOUT" + i + "A")
  OCIexecute("create table COLDATOUT"+i+"A (CUST integer, PERIOD integer, INCOME float)")
  OCIexecute("insert into COLDATOUT"+c+"A (CUST,PERIOD,INCOME) values (:1, :2)", 
   array(p in PERIOD) INCOME1(c,p))
 end-do
!)

 OCIlogoff

! **** Using 'initializations from' ****

! Method 1: Data in columns, with CUST index value included
 initializations from CNCT
  INCOME1 as 'COLDAT1'
  INCOME1 as 'COLDAT2'
  INCOME1 as 'COLDAT3'
 end-initializations

 writeln("1: ", INCOME1)
 
! Method 2: Data in columns, without CUST index value

 procedure readcol2(cust:integer, table:string)
  declarations
   TEMP: array(PERIOD) of real
  end-declarations
 
  initializations from CNCT
   TEMP as table
  end-initializations
  forall(p in PERIOD) INCOME2(cust,p):=TEMP(p)
 end-procedure
 
 forall(c in CUST) readcol2(c, "COLDAT"+c+"A") 
 writeln("2: ", INCOME2)
 
! Method 3: Data in rows, without CUST index value

 procedure readrow2(cust:integer, table:string)
  declarations
   TEMP: array(1..2,PERIOD) of real
  end-declarations

  initializations from CNCT
   TEMP as 'noindex;'+table
  end-initializations
  forall(p in PERIOD) INCOME3(cust,p):=TEMP(2,p)
 end-procedure
 
 finalize(PERIOD)                       ! The index sets must be known+fixed
 forall(c in CUST) readrow2(c, "ROWDAT"+c) 
 writeln("3: ", INCOME3)
 
end-model

**************************************************


! Creation of data tables in an Oracle database:

 declarations
  tsucc: array ({false,true}) of string
  INCOME1T,INCOME2T,INCOME3T: dynamic array(CUST,PERIOD) of real
 end-declarations

 tsucc(false):="failed"; tsucc(true):="succeeded"

 initializations from "multitab.dat"
  INCOME1T as "COLDAT1"
  INCOME2T as "COLDAT2"
  INCOME3T as "COLDAT3"
 end-initializations

 initializations from "multitab.dat"
  INCOME as "COLDAT1"
  INCOME as "COLDAT2"
  INCOME as "COLDAT3"
 end-initializations

 setparam("OCIdebug",true)
 OCIlogon(DB)
 writeln("Connection number: ",getparam("OCIconnection"))

 forall(i in 1..3) OCIexecute("drop table COLDAT" + i)

 OCIexecute("create table COLDAT1 (CUST integer, PERIOD integer, INCOME float)")
 writeln(" - Create COLDAT1 (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into COLDAT1 (CUST, PERIOD, INCOME) values (:1, :2, :3)", INCOME1T)
 writeln(" - Insert values into COLDAT1 (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
 
 OCIexecute("create table COLDAT2 (CUST integer, PERIOD integer, INCOME float)")
 writeln(" - Create COLDAT2 (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into COLDAT2 (CUST, PERIOD, INCOME) values (:1, :2, :3)", INCOME2T)
 writeln(" - Insert values into COLDAT2 (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
 
  OCIexecute("create table COLDAT3 (CUST integer, PERIOD integer, INCOME float)")
 writeln(" - Create COLDAT3 (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into COLDAT3 (CUST, PERIOD, INCOME) values (:1, :2, :3)", INCOME3T)
 writeln(" - Insert values into COLDAT3 (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")

 forall(i in 1..3) do
   OCIexecute("drop table COLDAT" + i + "A")
   OCIexecute("create table COLDAT" + i + "A (PERIOD integer, INCOME float)")
   writeln(" - Create COLDAT" + i + "A (",tsucc(getparam("OCIsuccess")),")")
   OCIexecute("insert into COLDAT" + i + "A (PERIOD, INCOME) values (:1, :2)",
              array(p in PERIOD) INCOME(i,p))
   writeln(" - Insert values into COLDAT" + i + 
           " (",tsucc(getparam("OCIsuccess")),",",
           getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
 end-do
 
 procedure writerow(cust:integer, table:string)
   declarations
    tname: text
    tlist,tlist2: list of real
   end-declarations

   OCIexecute("drop table " + table)
   OCIexecute("create table " + table + " (F1 float, F2 float, F3 float, F4 float, F5 float)")
   writeln(" - Create " + table + "(",tsucc(getparam("OCIsuccess")),")")

   tname:= "F1"
   forall(p in PERIOD | p>1) tname += ", F" + p
   vname:= ":1"
   forall(p in PERIOD | p>1) vname += ", :" + p
   forall(p in PERIOD ) tlist+=[real(p)]
   OCIexecute("insert into " + table + "(" + tname + ") values (" + vname +")",
    tlist)
   writeln(" - Insert values into " + table +  
           " (",tsucc(getparam("OCIsuccess")),",",
           getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
   forall(p in PERIOD ) tlist2+=[INCOME(cust,p)]
   OCIexecute("insert into " + table + "(" + tname + ") values (" + vname +")",
    tlist2)
   writeln(" - Insert values into " + table +  
           " (",tsucc(getparam("OCIsuccess")),",",
           getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")

 end-procedure
 
 setparam("ocindxcol",false)      ! Data specified in dense format (no indices)
 forall(c in CUST) writerow(c, "ROWDAT" + c)
 setparam("ocindxcol",true)


 OCIlogoff

multitab5.mos

(!*******************************************************
   Mosel Example Problems 
   ======================

   file multitab5.mos
   `````````````````
   Reading an array from several data tables.
   Output parts of an array into several tables.
   - Using 'initializations from' with the xls I/O driver -
       
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012, rev. May 2017
*******************************************************!)

model "Multiple data sources (generic spreadsheet)"
 uses "mmsheet"

 parameters
!  CNCT= 'mmsheet.xls:skiph;multitab.xls'
  CNCT= 'mmsheet.xlsx:skiph;multitab.xlsx'
  CNCTOUT= 'mmsheet.xlsx:multitabout.xlsx'
 end-parameters

 declarations
  CUST: set of integer
  PERIOD: range
  INCOME1,INCOME2,INCOME3: dynamic array(CUST,PERIOD) of real
 end-declarations

! Different ways of reading the data from a spreadsheet
! (spreadsheet ranges include a header line -> use option 'skiph')

! Method 1: Data in columns, with CUST index value included
 initializations from CNCT
  INCOME1 as 'COLDAT1'
  INCOME1 as 'COLDAT2'
  INCOME1 as 'COLDAT3'
 end-initializations

 writeln("1: ", INCOME1)
 
! Method 2: Data in columns, without CUST index value

 procedure readcol(cust:integer, table:string)
  declarations
   TEMP: array(PERIOD) of real
  end-declarations
 
  initializations from CNCT
   TEMP as table
  end-initializations
  forall(p in PERIOD) INCOME2(cust,p):=TEMP(p)
 end-procedure
 
 forall(c in CUST) readcol(c, "COLDAT"+c+"A") 
 writeln("2: ", INCOME2)
 
! Method 3: Data in rows, without CUST index value

 procedure readrow(cust:integer, table:string)
  declarations
   TEMP: array(1..2,PERIOD) of real
  end-declarations

  initializations from CNCT
   TEMP as 'noindex;'+table
  end-initializations
  forall(p in PERIOD) INCOME3(cust,p):=TEMP(2,p)
 end-procedure
 
 finalize(PERIOD)                       ! The index sets must be known+fixed
 forall(c in CUST) readrow(c, "ROWDAT"+c) 
 writeln("3: ", INCOME3)

! ******** Output an array into several tables ********

 fopen(CNCTOUT, F_OUTPUT)

 forall(c in CUST)
  initializations to CNCTOUT
   ! Output into separate sheets, CUST index value included
    evaluation of array(c1=c,p in PERIOD) INCOME1(c1,p) as "grow;skiph+;[Sheet"+c+"$A2:C2](CUST,PERIOD,INCOME)"

   ! Output into separate tables within one sheet, CUST index value included
    evaluation of array(c1=c,p in PERIOD) INCOME1(c1,p) as "grow;skiph+;[Sheet1$R2C"+(10+c*4)+":R2C"+(10+c*4+2)+"](CUST,PERIOD,INCOME)"

   ! Output without CUST index value
    evaluation of array(p in PERIOD) INCOME1(c,p) as "grow;skiph+;[Sheet1$R15C"+(c*3)+":R15C"+(c*3+1)+"](PERIOD,INCOME)"    
  end-initializations
 fclose(F_OUTPUT)

end-model

multitab6.mos

(!*******************************************************
   Mosel Example Problems 
   ======================

   file multitab6.mos
   `````````````````
   Reading an array from several data tables.
   Output parts of an array into several tables.
   - Using 'initializations from' with the csv I/O driver -
       
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012, rev. May 2017
*******************************************************!)

model "Multiple data sources (CSV)"
 uses "mmsheet", "mmsystem"

 parameters
  CNCT= 'mmsheet.csv:multitab.csv'
  CNCTOUT= 'mmsheet.csv:multitabout.csv'
 end-parameters

 declarations
  CUST: set of integer
  PERIOD: range
  INCOME1,INCOME2,INCOME3: dynamic array(CUST,PERIOD) of real
 end-declarations

! **** Different ways of reading the data from a CSV spreadsheet

! Method 1: Data in columns, with CUST index value included
 initializations from CNCT
  INCOME1 as '[A3:C7]'
  INCOME1 as '[E3:G7]'
  INCOME1 as '[I3:K7]'
 end-initializations

 writeln("1: ", INCOME1)
 
! Method 2: Data in columns, without CUST index value

 procedure readcol(cust:integer, table:string)
  declarations
   TEMP: array(PERIOD) of real
  end-declarations
 
  initializations from CNCT
   TEMP as table
  end-initializations
  forall(p in PERIOD) INCOME2(cust,p):=TEMP(p)
 end-procedure
 
 forall(c in CUST)
  readcol(c, "[R11C" + (1+(c-1)*3) + ":R15C" + (2+(c-1)*3) + "]")
 writeln("2: ", INCOME2)
 
! Method 3: Data in rows, without CUST index value

 procedure readrow(cust:integer, table:string)
  declarations
   TEMP: array(1..2,PERIOD) of real
  end-declarations

  initializations from CNCT
   TEMP as 'noindex;'+table
  end-initializations
  forall(p in PERIOD) INCOME3(cust,p):=TEMP(2,p)
 end-procedure
 
 finalize(PERIOD)                       ! The index sets must be known+fixed
 forall(c in CUST) readrow(c, "[B" + (21+(c-1)*5) + ":F" + (22+(c-1)*5) + "]") 
 writeln("3: ", INCOME3)


! ******** Output an array into several tables ********

 fopen(CNCTOUT, F_OUTPUT)

 forall(c in CUST)
  initializations to CNCTOUT
   ! Method 1: CUST index value included
    evaluation of array(c1=c,p in PERIOD) INCOME1(c1,p) as "grow;skiph+;[R2C"+(c*4)+":R2C"+(c*4+2)+"](CUST,PERIOD,INCOME)"

   ! Method 2: without CUST index value
    evaluation of array(p in PERIOD) INCOME1(c,p) as "grow;skiph+;[R15C"+(c*3)+":R15C"+(c*3+1)+"](PERIOD,INCOME)"  
  end-initializations
 fclose(F_OUTPUT)


end-model

© 2001-2023 Fair Isaac Corporation. All rights reserved. This documentation is the property of Fair Isaac Corporation (“FICO”). Receipt or possession of this documentation does not convey rights to disclose, reproduce, make derivative works, use, or allow others to use it except solely for internal evaluation purposes to determine whether to purchase a license to the software described in this documentation, or as otherwise set forth in a written software license agreement between you and FICO (or a FICO affiliate). Use of this documentation and the software described in it must conform strictly to the foregoing permitted uses, and no other use is permitted.

Contents

Index

Glossary

Search Results

Spreadsheets and databases: working with multiple data tables and arrays