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]

recordout5.mos

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

   file recordout5.mos
   ```````````````````
   Writing out records to spreadsheets.
   - Using 'initializations to' with the xls driver -
   
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012, rev. Feb. 2019
*******************************************************!)

model "Record output (generic spreadsheet)"
 uses "mmsheet", "mmsystem"

 parameters
  CSTR= 'mmsheet.xls:recorddata.xls'
!  CSTR= 'mmsheet.xlsx:recorddata.xlsx'
  FNAME2='recordout.xls'
  CSTR2= 'mmsheet.xls:skiph+;'+FNAME2
!  FNAME2='recordout.xlsx'
!  CSTR2= 'mmsheet.xlsx:skiph+;'+FNAME2
 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.)
! 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 new spreadsheet file (without named ranges) ****

! Remove any copy of the output file created by previous runs
 fdelete(FNAME2)

! Driver option skiph+ enables addition of specified column titles in output
! Column ranges can be specified in various ways;
! if no sheet name is given the first sheet is used
 initializations to CSTR2
 ! Output complete records
  PDATA as "grow;[OutputData$A:D](Product,Mach,Cost,Duration)"
  ALLDATA as "noindex;grow;[F:I](Product,Mach,Cost,Duration)"

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

end-model