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

Auto-indexation for single-dimensional arrays

Description

This example shows how to read single-dimensional data tables from a spreadsheet or database file that only contains the data values. The data arrays must have an index set of type range that gets populated automatically based on the row count, either starting with the default value 1 or with a specified start value. Several arrays can be populated via a single statement.



Further explanation of this example: Xpress Whitepaper 'Using ODBC and other database interfaces with Mosel', Section Examples - Dense vs. sparse data format - Auto-indexation.


Source Files

Data Files





autoindex.mos

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

   file autoindex.mos
   ``````````````````
   Auto-indexation feature for various methods of 
   data input from spreadsheets or databases.
   
   (c) 2020 Fair Isaac Corporation
       author: S. Heipcke, Apr. 2020
*******************************************************!)
model "autoindex"
 uses "mmsheet", "mmetc", "mmodbc"

 parameters
  CSTR = "mmsheet.csv:adt.csv"
!  CSTR = "mmsheet.xls:adt.xls"
!  CSTR = "mmsheet.xlsx:adt.xlsx"
!  CSTR = "mmsheet.excel:adt.xls"
 end-parameters

! **** Reading from spreadsheet files ****
 procedure readsht
  declarations
    A,A2,B,C,B2,C2: dynamic array(range) of integer
  end-declarations

  initialisations from CSTR
    A as 'autondx;[a:a]'                ! All data in column A
    A2 as 'autondx=-3;[a:a]'            ! Use start value -3 for indexation
    [B,C] as 'autondx=0;[a:b]'          ! Populating 2 arrays
    [B2,C2] as 'autondx;[b:b](#1,#1)'   ~~COMMENT10~~
  end-initialisations
  writeln("SHT: A=", A)
  writeln("SHT: A2=", A2)
  writeln("SHT: B=", B, " C=", C)
  writeln("SHT: B2=", B2, " C2=", C2)
 end-procedure

! **** Reading from CSV-format files via 'diskdata' functionality ****
 procedure readdd
  declarations
    A,A2,B,C,B2,C2: dynamic array(range) of integer
  end-declarations

  initialisations from 'mmetc.diskdata:'
    A as 'csv(1),autondx;adt.csv'      ! Selecting the first column
    A2 as 'csv(1),autondx=-3;adt.csv'  ! Use start value -3 for indexation
    [B,C] as 'csv,autondx;adt.csv'     ! Populating 2 arrays
  end-initialisations
  writeln("DD: A=", A)                 ! Output: [(1,10),(2,20),(3,30)]
  writeln("DD: A2=", A2) 
  writeln("DD: B=", B, " C=", C)
  diskdata(ETC_IN+ETC_CSV+ETC_AUTONDX,'adt.csv',[B2,C2])
  writeln("DD: B2=", B2, " C2=", C2)
 end-procedure

! **** Reading from SQLite database via SQL commands ****
 procedure readsql
  declarations
    A,A2,B,C,B2,C2: dynamic array(range) of integer
  end-declarations

  SQLconnect("adt.sqlite")
  setparam("SQLautondx",true); 
  SQLexecute("select (Col1) from MyTable", A)  ! Selecting a single column
  setparam("SQLfirstndx",-3)            ! Use start value -3 for indexation
  SQLexecute("select (Col1) from MyTable", A2)
  setparam("SQLfirstndx",0)
  SQLexecute("select * from MyTable",[B,C])    ! Populating 2 arrays
  SQLdisconnect
  writeln("SQL: A=", A)
  writeln("SQL: A2=", A2)
  writeln("SQL: B=", B, " C=", C)
 end-procedure

(!
! **** Creating a database via SQL commands ****
 procedure createdb
  declarations
    A:array(integer) of integer
  end-declarations
 
  A::([10,20,30])[40,50,60]
  SQLconnect("adt.sqlite")
  SQLexecute("drop table if exists MyTable")
  SQLexecute("create table MyTable (Col1 integer, Col2 integer)")
  SQLexecute("insert into MyTable (Col1,Col2) values (?,?)", A)
  SQLdisconnect
 end-procedure
!)
 
! ******** Read input data from the different sources ********
 readsht
 readdd
 readsql

end-model



Back to examples browserPrevious exampleNext example