(!****************************************************** 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, rev. Aug. 2023 *******************************************************!) model "autoindex" uses "mmsheet", "mmetc", "mmodbc" options keepassert 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)' ! Selecting twice the same column 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: dynamic array(range) of integer end-declarations SQLconnect("adt.sqlite") assert(getparam("SQLsuccess")) 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") assert(getparam("SQLsuccess")) SQLexecute("drop table if exists MyTable") SQLexecute("create table MyTable (Col1 integer, Col2 integer)") assert(getparam("SQLsuccess")) SQLexecute("insert into MyTable (Col1,Col2) values (?,?)", A) assert(getparam("SQLsuccess")) SQLdisconnect end-procedure !) ! ******** Read input data from the different sources ******** readsht readdd readsql end-model