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]

recordout6.mos

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

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

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

 parameters
  CSTR= 'mmsheet.csv:recorddataout.csv'
  FNAME2='recordout.csv'
  CSTR2= 'mmsheet.csv: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 an existing file ****

! Write out complete records
! (This assumes that the output tables have been created previously.)
! Driver options: noindex - dense data
 initializations to CSTR
  PDATA as "[B4:E12]"
  ALLDATA as "noindex;[G4:J12]"
 end-initializations

! Write out selected record fields
 initializations to CSTR
  PDATA(Cost) as "[B17:D25]"
  ALLDATA(Product,Mach,Duration) as "noindex;[G17:I25]"
 end-initializations


! **** Writing out into a new file ****

! 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
 initializations to CSTR2
 ! Output complete records
  PDATA as "grow;[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