FICO
FICO Xpress Optimization Examples Repository
FICO Optimization Community FICO Xpress Optimization Home
Back to examples browserPrevious exampleNext example

Writing out solution values to text files, spreadsheets or databases

Description
Solution values of decision variables or constraints need to be copied into an array. The contents of this array can then be written to a spreadsheet or database using ODBC or software-specific drivers (or simply to text file using Mosel's default output).

  • Spreadsheets and databases can be accessed from Mosel through an ODBC connection using the odbc driver (soleg.mos) or with SQL statements (soleg2.mos).
  • If Microsoft Excel is installed, the software-specific driver excel (soleg3.mos) can be used.
  • For Oracle databases a software-specific connection is provided by the module mmoci (soleg4.mos).
  • For generic spreadsheets it is recommended to use the portable mmsheet module that provides subroutines to read and write xls and xlsx (soleg5.mos) and csv (soleg6.mos) files.
As an alternative to the explicit copying of arrays of solution values you can use the keyword evaluation in initializations to blocks. If preceded by the marker evaluation of solution values, results of Mosel functions or other expressions can be used directly in the initializations to block as shown in the example initeval.mos.

Further explanation of this example: Xpress Whitepaper 'Using ODBC and other database interfaces with Mosel', Section Examples - Outputting solution values. 'Mosel User Guide', Section 10.2.4 Solution output with initializations to.


Source Files

Data Files
soleg.csv[download]
soleg.mdb[download]
soleg.xls[download]
soleg.xlsx[download]
soleg.sqlite[download]





soleg.mos

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

   file soleg.mos
   ``````````````
   Writing out solution values to
   a spreadsheet or database via ODBC.
   - Using 'initializations to' with the odbc driver -
  
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2006, rev. Oct. 2017
*******************************************************!)

model "Solution values output"
 uses "mmxprs", "mmodbc"

 parameters
!  CNCT = "soleg.xls"             ! Use Excel spreadsheet `soleg.xls'
  CNCT = "soleg.mdb"            ! Use Access database `soleg.mdb'
                                 ! Use SQLite database `soleg' via ODBC
!  CNCT = 'DSN=sqlite;DATABASE=soleg.sqlite'
!  CNCT = 'soleg.sqlite'         ! Use SQLite database `soleg' directly
 end-parameters
 
 declarations
  R = 1..3
  S = 1..2
  SOL: array(R,S) of real        ! Array for solution values
  x: array(R,S) of mpvar         ! Decision variables
 end-declarations

! Define and solve the problem
 forall(i in R) sum(j in S) x(i,j) <= 4
 forall(j in S) sum(i in R) x(i,j) <= 6
 maximise( sum(i in R, j in S) (i*j)*x(i,j) )

! Get solution values from LP into the array SOL
 forall(i in R, j in S) SOL(i,j) := getsol(x(i,j)) 

! Data output using an initializations block with the odbc driver
 initializations to "mmodbc.odbc:debug;"+CNCT
  SOL as "MyOut1"
 end-initializations

(! Alternative form:
 initializations to "mmodbc.odbc:debug;"+CNCT
  evaluation of array(i in R, j in S) x(i,j).sol as "MyOut1"
 end-initializations
!)
end-model

Back to examples browserPrevious exampleNext example