Reading 3-dimensional arrays
|
|
|
| Type: | Programming |
| Rating: | 2 (easy-medium) |
| Description: | This example shows how to read a 3-dimensional array from an excel file (threedimarr.mos) and from a generic spreadsheet (threedimarr5.mos) where the last index is specifed 'across the columns', resulting in a pivot-style table format where the first N-1 columns contain index values (here: 2 index columns). Model versions (2) and (4) show the same example for databases, and model version (6) works with data in CSV format. Note that standard use of the 'partndx' option for spreadsheets assumes that the last index is a set of type 'range', the example partidxstr.mos defines a utility routine 'makelabel' that makes it possible to work with a 'set of string' for the last index. |
| File(s): | threedimarr.mos, threedimarr2.mos, threedimarr3.mos, threedimarr4.mos, threedimarr5.mos, threedimarr6.mos, partidxstr.mos |
| Data file(s): | threedim.csv, threedim.xls, threedim.xlsx, threedim.mdb, threedim.sqlite, partidxdata.csv, partidxdata.xls, partidxdata.xlsx |
|
|
|
| threedimarr.mos |
(!*******************************************************
Mosel Example Problems
======================
file threedimarr.mos
````````````````````
Reading 3-dimensional arrays.
- Using 'initializations from' with the odbc driver -
(c) 2010 Fair Isaac Corporation
author: S. Heipcke, July 2010, rev. Jan. 2014
*******************************************************!)
model ThreeDimArr
uses "mmodbc"
parameters
CSTR = "threedim.mdb" ! Use Access database `threedim.mdb'
! CSTR = 'threedim.sqlite' ! Use SQLite database `threedim.sqlite' directly
! Use SQLite database `threedim.sqlite' via ODBC
! CSTR = 'DSN=sqlite;DATABASE=threedim.sqlite'
end-parameters
declarations
I: range
J: set of string
K = 1..5 ! The last index set must be defined in the model
A: dynamic array(I,J,K) of real
end-declarations
initializations from 'mmodbc.odbc:debug;noindex;' + CSTR
A as 'Tab_23'
end-initializations
writeln("A: ")
forall(i in I, j in J, k in K | exists(A(i,j,k)))
writeln("A(", i, ",", j, ",", k, "): ", A(i,j,k))
end-model
|
| threedimarr2.mos |
(!*******************************************************
Mosel Example Problems
======================
file threedimarr2.mos
`````````````````````
Reading 3-dimensional arrays.
- Using SQL commands -
(c) 2014 Fair Isaac Corporation
author: S. Heipcke, Jan. 2014, rev. Aug. 2023
*******************************************************!)
model "ThreeDimArr (SQL)"
uses "mmodbc", "mmsystem"
parameters
! CSTR = "threedim.xls" ! Use Excel spreadsheet `threedim.xls'
CSTR = "threedim.mdb" ! Use Access database `threedim.mdb'
! CSTR = 'threedim.sqlite' ! Use SQLite database `threedim.sqlite' directly
! Use SQLite database `threedim.sqlite' via ODBC
! CSTR = 'DSN=sqlite;DATABASE=threedim.sqlite'
end-parameters
declarations
I: range
J: set of string
K = 1..5 ! The last index set must be defined in the model
A: dynamic array(I,J,K) of real
Idx3: text
end-declarations
SQLconnect(CSTR)
if not getparam("SQLsuccess"): setioerr("Database connection failed")
setparam("SQLndxcol", false) ! Partially indexed data
forall(k in K) Idx3+= (", Index3_"+k )
SQLexecute("select Index1, Index2" + Idx3 + " from Tab_23", A)
SQLdisconnect
writeln("A: ")
forall(i in I, j in J, k in K | exists(A(i,j,k)))
writeln("A(", i, ",", j, ",", k, "): ", A(i,j,k))
end-model
|
| threedimarr3.mos |
(!*******************************************************
Mosel Example Problems
======================
file threedimarr3.mos
`````````````````````
Reading 3-dimensional arrays.
- Using 'initializations from' with Excel -
(c) 2010 Fair Isaac Corporation
author: S. Heipcke, July 2010, rev. Apr. 2014
*******************************************************!)
model "ThreeDimArr (Excel)"
uses "mmsheet", "mmodbc"
declarations
I: range
J: set of string
K = 1..5 ! The last index set must be defined in the model
A,B: dynamic array(I,J,K) of real
end-declarations
initializations from 'mmsheet.excel:partndx;threedim.xls'
A as 'Tab_23'
end-initializations
writeln("A: ")
forall(i in I, j in J, k in K | exists(A(i,j,k)))
writeln("A(", i, ",", j, ",", k, "): ", A(i,j,k))
(!
initializations from 'mmodbc.odbc:debug;partndx;threedim.xls'
B as 'Tab23odbc'
end-initializations
writeln("B: ")
forall(i in I, j in J, k in K | exists(B(i,j,k)))
writeln("B(", i, ",", j, ",", k, "): ", B(i,j,k))
!)
end-model
|
| threedimarr4.mos |
(!*******************************************************
Mosel Example Problems
======================
file threedimarr4.mos
`````````````````````
Reading 3-dimensional arrays.
- Using OCI statements -
(c) 2014 Fair Isaac Corporation
author: S. Heipcke, Jan. 2014, rev. Aug. 2023
*******************************************************!)
model "ThreeDimArr (OCI)"
uses "mmoci", "mmsystem"
parameters
DB="myname/mypassword@dbname" ! Login to Oracle database (not provided)
CNCT="mmoci.oci:debug;"+DB
end-parameters
declarations
I: range
J: set of string
K = 1..5 ! The last index set must be defined in the model
A: dynamic array(I,J,K) of real
Idx3: text
end-declarations
setparam("OCIdebug",true)
OCIlogon(DB)
if not getparam("OCIsuccess"): setioerr("Database connection failed")
setparam("OCIndxcol", false) ! Partially indexed data
forall(k in K) Idx3+= (", Index3_"+k )
OCIexecute("select Index1, Index2" + Idx3 + " from Tab_23", A)
OCIlogoff
writeln("A: ")
forall(i in I, j in J, k in K | exists(A(i,j,k)))
writeln("A(", i, ",", j, ",", k, "): ", A(i,j,k))
end-model
|
| threedimarr5.mos |
(!*******************************************************
Mosel Example Problems
======================
file threedimarr5.mos
`````````````````````
Reading 3-dimensional arrays.
- Using 'initializations from' with the xls/xlsx driver -
(c) 2012 Fair Isaac Corporation
author: S. Heipcke, Dec. 2012, rev. Apr. 2014
*******************************************************!)
model "ThreeDimArr (generic spreadsheet)"
uses "mmsheet"
parameters
CSTR1= 'mmsheet.xls:threedim.xls'
CSTR2= 'mmsheet.xlsx:threedim.xlsx'
end-parameters
declarations
I: range
J: set of string
K = 1..5 ! The last index set must be defined in the model
A,B,C: dynamic array(I,J,K) of real
end-declarations
initializations from CSTR1
A as 'partndx;Tab_23'
end-initializations
writeln("A: ")
forall(i in I, j in J, k in K | exists(A(i,j,k)))
writeln("A(", i, ",", j, ",", k, "): ", A(i,j,k))
initializations from CSTR2
B as 'partndx;Tab_23'
end-initializations
writeln("B: ")
forall(i in I, j in J, k in K | exists(B(i,j,k)))
writeln("B(", i, ",", j, ",", k, "): ", B(i,j,k))
! Using explicit cell addresses instead of a named range:
initializations from CSTR1
C as 'partndx;[Sheet1$A3:G8]'
end-initializations
writeln("C: ")
forall(i in I, j in J, k in K | exists(C(i,j,k)))
writeln("C(", i, ",", j, ",", k, "): ", C(i,j,k))
end-model
|
| threedimarr6.mos |
(!*******************************************************
Mosel Example Problems
======================
file threedimarr6.mos
`````````````````````
Reading 3-dimensional arrays.
- Using 'initializations from' with the csv driver -
(c) 2012 Fair Isaac Corporation
author: S. Heipcke, Dec. 2012, rev. Apr. 2014
*******************************************************!)
model "ThreeDimArr (CSV)"
uses "mmsheet"
parameters
CSTR= 'mmsheet.csv:threedim.csv'
end-parameters
declarations
I: range
J: set of string
K = 1..5 ! The last index set must be defined in the model
A: dynamic array(I,J,K) of real
end-declarations
initializations from CSTR
A as 'partndx;[A3:G8]'
end-initializations
writeln("A: ")
forall(i in I, j in J, k in K | exists(A(i,j,k)))
writeln("A(", i, ",", j, ",", k, "): ", A(i,j,k))
end-model
|
| partidxstr.mos |
(!******************************************************
Mosel Example Problems
======================
file partidxstr.mos
```````````````````
Reading partial-index table format with string column header
for the last index for spreadsheet files in XLS, XLSX or CSV format
The subroutine 'makelabel' provides a generic way of creating
the required header definition for the 'partndx' format.
(c) 2024 Fair Isaac Corporation
author: S.Heipcke,Y.Colombani, Jan. 2024
*******************************************************!)
model partidxstr
uses "mmsystem", "mmreflect", "mmsheet"
(!@doc.
@descr Constructing the header selection string for reading a partially indexed array
@param fname Spreadsheet file name (XLS, XLSX, or CSV format)
@param rangename Name of a sheet or range (first sheet if not specified)
@param u Array to be populated from the specified input
@info The data array 'u' serves to determine the number of indices. If the array has N dimensions, it is assumed that the values of the first N-1 indices are contained in the N-1 initial columns of the data range and all other columns correspond to index values for the last dimension of the input array.
!)
public function makelabel(fname:text,rangename:string,u:any):string
declarations
colnames:dynamic array(1..1,1..1000) of string ! Due to the 'dynamic' flag this will work even if the actually selected area exceeds 1000 columns
label:text
allcols:set of string
colorder:array(string) of integer
cr: range
end-declarations
initialisations from fname
colnames as "noindex;"+rangename
end-initialisations
if not u is array then
writeln("Third argument of function 'makelabel' must be an array.")
return
end-if
with nbdim=u.array.nbdim, ndx=u.array.index(nbdim).string do
allcols:=union(i in nbdim..colnames.size){string(colnames(1,i))}
if not allcols <= ndx then ndx+=allcols; end-if
cr:=1..colnames.size
forall(i in cr) colorder(colnames(1,i)):=i
label:="skiph;partndx;"+rangename+"(#1"
forall(c in 2..nbdim-1) label+=",#"+c
forall(c in ndx|c in allcols) label+=",#"+colorder(c)
end-do
returned:=string(label+')')
end-function
!*******************************************************************
procedure readpartindxl(drvfilename:string)
declarations
TabA: dynamic array(string,range,string) of real
TabP: dynamic array(S1:set of string,R:range,S2:set of string) of real
it: iterator
end-declarations
!**** Reading a partially indexed table with string header fields
fopen(drvfilename,F_INPUT)
initialisations from getfname(F_INPUT)
TabA as makelabel(getfname(F_INPUT),"[]",TabA)
end-initialisations
! fclose(F_INPUT)
writeln("Index sets:", TabA.index(1), TabA.index(2), TabA.index(3))
! Enumerate the existing array entries (using an iterator since sets are not named)
inititer(it,TabA)
while (nextcell(it)) writeln(it.indices, ":", TabA(it))
!**** Reading a partially indexed table with preselection
S1:={"xy","Z","BB","ab"} ! Prepopulating the index set
! fopen(drvfilename,F_INPUT)
! Various range specifications that result in the same selection:
initialisations from getfname(F_INPUT)
TabP as makelabel(getfname(F_INPUT),"pidxtest",TabP) ! predef. range name
! TabP as makelabel(getfname(F_INPUT),"[1$A:F]",TabP)
! TabP as makelabel(getfname(F_INPUT),"[A:F]",TabP)
end-initialisations
fclose(F_INPUT)
writeln("Index sets:", S1,R,S2)
! Enumerate the existing array entries
forall(i in S1, j in R, k in S2 | exists(TabP(i,j,k)))
writeln([i,j,k], ":", TabP(i,j,k))
end-procedure
!*******************************************************************
procedure readpartindcsv
declarations
TabA: dynamic array(string,range,string) of real
TabP: dynamic array(S1:set of string,R:range,S2:set of string) of real
it: iterator
end-declarations
!**** Reading a partially indexed table with string header fields
fopen("mmsheet.csv:partidxdata.csv",F_INPUT)
initialisations from getfname(F_INPUT)
TabA as makelabel(getfname(F_INPUT),"[]",TabA)
end-initialisations
! fclose(F_INPUT)
writeln("Index sets:", TabA.index(1), TabA.index(2), TabA.index(3))
! Enumerate the existing array entries (using an iterator since sets are not named)
inititer(it,TabA)
while (nextcell(it)) writeln(it.indices, ":", TabA(it))
!**** Reading a partially indexed table with preselection
S1:={"xy","Z","BB","ab"} ! Prepopulating the index set
! fopen("mmsheet.csv:partidxdata.csv",F_INPUT)
initialisations from getfname(F_INPUT)
TabP as makelabel(getfname(F_INPUT),"[A:F]",TabP)
end-initialisations
fclose(F_INPUT)
writeln("Index sets:", S1,R,S2)
! Enumerate the existing array entries
forall(i in S1, j in R, k in S2 | exists(TabP(i,j,k)))
writeln([i,j,k], ":", TabP(i,j,k))
end-procedure
!**********************************************************
writeln("XLS:")
readpartindxl("mmsheet.xls:partidxdata.xls")
writeln("XLSX:")
readpartindxl("mmsheet.xlsx:partidxdata.xlsx")
writeln("CSV:")
readpartindcsv
end-model
|
© 2001-2024 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.