Reading sparse data from text files, spreadsheets and databases, and from memory

Suppose we want to read in data of the form

from a file, setting up a dynamic array A(range, range) with just the A(i,j)= value(i,j) for the pairs (i,j) which exist in the file. Here is an example which shows different ways of doing this. We read data from

1. differently formatted text files (example trio.mos described in the 'Mosel User Guide', Section 3.4 Reading sparse data): using 'initializations from', 'readln', or 'diskdata',
2. spreadsheets or databases (examples duo.mos, duosheet.mos, duooci.mos, duoexc.mos, and duodd.mos described in the Xpress Whitepaper 'Using ODBC and other database interfaces with Mosel') ,
3. the application (C/Java) executing the Mosel model (examples datainputc.mos and datainputj.mos

With initializations from data from spreadsheets or databases is read in a similar way as text files are accessed; switching between data sources can be done by replacing the I/O driver name and the data source in the extended filename, such as "mmsheet.excel:data.xls" to access the spreadsheet data.xls or "mmodbc.odbc:data.mdb" to read from the MS Access database data.mdb and the string "mmodbc.odbc:DSN=mysql;DB=data" could be used to access a mysql database named data.

Instead of using an initializations block, we may read data from a database into a Mosel model by writing out the corresponding SQL statements (this format allows fine-tuning of the queries and gives access to a larger range of commands, in particular when working with databases). When developing applications with ODBC/SQL statements, the user is advised to use the following two parameter settings to obtain error messages and debugging information from ODBC:

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

The module mmoci (requires a separate license) defines a software-specific interface to Oracle databases that is used in a similar way as the ODBC connection.
Using initializations from the extended filename will take a form like "mmoci.oci:myname/mypassword@dbname" and it is also possible to work with PL/SQL statements like the following:

 OCIlogon('myname/mypassword@dbname')
 OCIexecute("select Index_i,Index_j,Value from MyDataTable", A5)
 OCIlogoff

Using initializations from it is also possible to read data into a Mosel model from the calling application (C/C++, C#, Java) using a combination of the I/O drivers mem (data held in memory) and raw driver (data in binary format) in C, drivers dotnet and dotnetraw in C# programs, and drivers java and jraw in Java programs.

Other possibilities of inputting data into a Mosel model include

• Static modules: data exchange between the calling application and a model via custom functions, using the Mosel Native Interface - see the example of embedding the cutting stock problem in the Whitepaper 'Embedding Optimization Algorithms'.
• Custom format/custom data sources: the Mosel Native Interface allows the user to implement his own I/O drivers (to be used with initializations blocks and all other Mosel functionality involving file access, such as matrix export or model compilation). Possible uses of such user-written drivers include encoding/decoding of data on the fly or compression/decompression of data. See the Xpress Whitepaper 'Generalized file handling in Mosel' for the complete code and documentation of an example implementing a compression driver with the zip library.
  Furthermore, the Mosel IO driver pipe may be used to recover the output of an external program. See the section on I/O drivers in the 'Mosel Libraries Reference Manual' for more information.

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

   file duo.mos
   ````````````
   Two ways of reading sparse data tables from
   spreadsheets or databases via ODBC.
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2006, rev. Sep. 2018
*******************************************************!)

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

 parameters
                                 ! Use Excel spreadsheet `data.xls'
!  CNCT = 'DSN=Excel Files;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselData/data.xls'
!  CNCT = 'data.xls'
!  CNCTIO = "data.xls"
                                 ! Use Access database `data.mdb'
!  CNCT = 'DSN=MS Access Database;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselData/data.mdb'
  CNCT = 'data.mdb'
  CNCTIO = "debug;data.mdb"
                                 ! Use mysql database `data' (not provided)
!  CNCT = 'DSN=mysql;DB=data'
!  CNCTIO = "debug;DSN=mysql;DB=data"
                                 ! Use SQLite database `data.sqlite' via ODBC
!  CNCT = 'DSN=sqlite;DATABASE=data.sqlite'
!  CNCTIO = "debug;DSN=sqlite;DATABASE=data.sqlite"
                                 ! Use SQLite database `data.sqlite' directly
!  CNCT = 'data.sqlite'
!  CNCTIO = "debug;data.sqlite"
 end-parameters

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

! First method: use an initializations block with the odbc driver
 initializations from "mmodbc.odbc:"+CNCTIO
  A4 as 'MyDataTable'
 end-initializations

! Second method: use SQL statements 
 SQLconnect(CNCT)
 SQLexecute("select Index_i,Index_j,Value from MyDataTable", A5)
 SQLdisconnect

! Now let us see what we have
 writeln('A4 is: ', A4)
 writeln('A5 is: ', A5)

end-model

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

   file duosheet.mos
   `````````````````
   Using the generic spreadsheet drivers for reading 
   sparse data tables from a spreadsheet.
   
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012
*******************************************************!)

model "Duo input (generic spreadsheet)"
 uses "mmsheet"

 parameters
  CNCTIO1 = "data.xls"           ! Use Excel spreadsheet `data.xls'
  CNCTIO2 = "data.xlsx"          ! Use Excel spreadsheet `data.xlsx'
  CNCTIO3 = "data.csv"           ! Use spreadsheet `data.csv'
 end-parameters

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

! Use the xls driver for reading the data
! (the spreadsheet ranges include a header line -> use option 'skiph')
 initializations from "mmsheet.xls:"+CNCTIO1
  A4 as 'skiph;MyDataTable'
 end-initializations

(! Alternatively:
 initializations from "mmsheet.xls:"+CNCTIO1
  A4 as 'MyDataTable2'
 end-initializations
! Or:
 initializations from "mmsheet.xls:"+CNCTIO1
  A4 as '[Sheet1$B3:D6]'
 end-initializations
!)


! Use the xlsx driver for reading the data
! (the spreadsheet ranges include a header line -> use option 'skiph')
 initializations from "mmsheet.xlsx:"+CNCTIO2
  A5 as 'skiph;MyDataTable'
 end-initializations

(! Alternatively:
 initializations from "mmsheet.xlsx:"+CNCTIO2
  A5 as 'MyDataTable2'
 end-initializations
! Or:
 initializations from "mmsheet.xlsx:"+CNCTIO2
  A5 as '[Sheet1$B3:D6]'
 end-initializations
!)


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

! Now let us see what we have
 writeln('A4 is: ', A4)
 writeln('A5 is: ', A5)
 writeln('A6 is: ', A6)

end-model

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

   file duoexc.mos
   ```````````````
   Using the excel IO driver for reading 
   sparse data tables from a spreadsheet.
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, Jan. 2007, rev. Dec. 2012
*******************************************************!)

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

 parameters
  CNCTIO = "data.xls"            ! Use Excel spreadsheet `data.xls'
 end-parameters

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

! Use the excel driver for reading the data
! (the spreadsheet ranges include a header line -> use option 'skiph')
 initializations from "mmsheet.excel:"+CNCTIO
  A4 as 'skiph;MyDataTable'
 end-initializations

(! Alternatively:
 initializations from "mmsheet.excel:"+CNCTIO
  A4 as 'MyDataTable2'
 end-initializations
! Or:
 initializations from "mmsheet.excel:"+CNCTIO
  A4 as '[Sheet1$B3:D6]'
 end-initializations
!)

! Now let us see what we have
 writeln('A4 is: ', A4)

end-model

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

   file duodd.mos
   ``````````````
   Using the diskdata functionality for reading a simple
   CSV format file.
   
   (c) 2020 Fair Isaac Corporation
       author: S. Heipcke, Apr. 2020
*******************************************************!)

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

 parameters
  CNCTIO = "datadd.csv"           ! Use CSV file `datadd.csv'
 end-parameters

 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(Index_i,Index_j,Value),skiph,'+CNCTIO
 end-initializations

! Use the diskdata subroutine for reading the data
 diskdata(ETC_IN+ETC_CSV+ETC_SKIPH, CNCTIO, A8)

! Now let us see what we have
 writeln('A7 is: ', A7)
 writeln('A8 is: ', A8)

end-model

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

   file duooci.mos
   ```````````````
   Two ways of reading sparse data tables from
   Oracle databases.
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, Jan. 2007, rev. Jul. 2010
*******************************************************!)

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

 parameters
  CNCT = 'myname/mypassword@dbname'
 end-parameters

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

! First method: use an initializations block with the oci driver
 initializations from "mmoci.oci:debug;"+CNCT
  A4 as 'MyDataTable'
 end-initializations

! Second method: use SQL statements 
 OCIlogon(CNCT)
 OCIexecute("select Index_i,Index_j,Value from MyDataTable", A5)
 OCIlogoff

! Now let us see what we have
 writeln('A4 is: ', A4)
 writeln('A5 is: ', A5)

end-model


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


! Creation of the input table in an Oracle database:

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

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

 OCIexecute("create table MyDataTable (Index_i integer, Index_j integer, Value float)")
 writeln(" - Create MyDataTable (",tsucc(getparam("OCIsuccess")),")")

 declarations
  A: array(range,range) of real
 end-declarations
 A(1,1):=12.5; A(2,3):=5.6; A(10,9):=-7.1; A(3,2):=1 

 OCIexecute("insert into MyDataTable (Index_i, Index_j, Value) values (:1, :2, :3)", A)
 writeln(" - Insert values in MyDataTable (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")