FICO Xpress Optimization Examples Repository: Reading and writing records and lists

FICO Xpress Optimization Examples Repository

FICO Optimization Community

FICO Xpress Optimization Home

Reading and writing records and lists

Description

This set of examples shows how to work with advanced data structures when accessing data in spreadsheets and databases:

reading in records (recordin*.mos)
writing out records (recordout*.mos)
reading and writing lists (listinout*.mos)

The basic model version uses an ODBC connection to spreadsheets or databases through the odbc driver, model version (2) uses ODBC through SQL statements, and model version (3) uses the software-specific driver excel to access Excel spreadsheets. Model version (1) shows how to work with Mosel's text data file format.

Further explanation of this example: Xpress Whitepaper 'Using ODBC and other database interfaces with Mosel', Section Examples - Working with lists - Working with records.

listrecord.zip

[download all files]

Source Files

By clicking on a file name, a preview is opened at the bottom of this page.

recordin.mos	[download]
recordin1.mos	[download]
recordin2.mos	[download]
recordin3.mos	[download]
recordin4.mos	[download]
recordin5.mos	[download]
recordin6.mos	[download]
recordout.mos	[download]
recordout1.mos	[download]
recordout2.mos	[download]
recordout3.mos	[download]
recordout4.mos	[download]
recordout5.mos	[download]
recordout6.mos	[download]
listinout.mos	[download]
listinout1.mos	[download]
listinout2.mos	[download]
listinout3.mos	[download]
listinout4.mos	[download]
listinout5.mos	[download]
listinout6.mos	[download]

Data Files

recorddata.dat	[download]
recorddata.csv	[download]
recorddata2.csv	[download]
recorddata.mdb	[download]
recorddata.xls	[download]
recorddata2.xls	[download]
recordout_templ.xls	[download]
recorddata.xlsx	[download]
recorddata2.xlsx	[download]
recorddata.sqlite	[download]
listdata.dat	[download]
listdata.csv	[download]
listdata.mdb	[download]
listdata.xls	[download]
listdata.xlsx	[download]
listdata.sqlite	[download]

recordout3.mos

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

   file recordout3.mos
   ```````````````````
   Writing out records to
   spreadsheets or databases via ODBC.
   - Using 'initializations to' with the excel driver -
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, Nov. 2007, rev. Feb. 2019
*******************************************************!)

model "Record output (Excel)"
 uses "mmsheet", "mmsystem"

 parameters
  CSTR = 'mmsheet.excel:recorddata.xls'
  CSTR2 = 'mmsheet.excel:skiph;recordout.xls'
 end-parameters
 
 declarations
  PRODUCTS: set of string
  MACH: range
  ProdRec = record
   Cost: real
   Duration: integer
  end-record 
  PDATA: dynamic array(PRODUCTS,MACH) of ProdRec

  R = 1..9
  AllDataRec = record
   Product: string
   Mach: integer
   Cost: real
   Duration: integer
  end-record 
  ALLDATA: array(R) of AllDataRec
 end-declarations

! **** Reading data from a text file
 initializations from "recorddata.dat"
  PDATA as "ProdData"
  ALLDATA as "AllData"
 end-initializations

! **** Writing out into a spreadsheet with named ranges ****

! Write out complete records
! (This assumes that the output tables have been created previously.)
! ATTENTION: results from previous runs must be removed previously.

! Driver options: noindex - dense data
 initializations to CSTR
  PDATA as "ProdDataOut"
  ALLDATA as "noindex;AllDataOut"
 end-initializations

! Write out selected record fields
 initializations to CSTR
  PDATA(Cost) as "CostOut"
  ALLDATA(Product,Mach,Duration) as "noindex;DurationOut"
 end-initializations


! **** Writing out into a simple spreadsheet without named ranges

! The mmsheet.excel driver cannot create files so we copy a template file
! to be used as output destination (output file from previous runs is replaced)
 fcopy("recordout_templ.xls", "recordout.xls")
 if getsysstat<>0 then writeln("Copying failed"); exit(1); end-if

! Column ranges for output can be specified in various ways
 initializations to CSTR2
 ! Output complete records
  PDATA as "grow;[A:D]"
  ALLDATA as "noindex;grow;[F:I]"

 ! Output selected record fields
  PDATA(Cost) as "grow;[R1C11:R1C13]"
  ALLDATA(Product,Mach,Duration) as "noindex;grow;[R1C15:R1C17]"
 end-initializations

end-model