| |||||||||||||||||||||||||||||||||||||||||||||||
Spreadsheets and databases: working with multiple data tables and arrays Description A database table (or a spreadsheet range) may contain in its different fields (columns) the data for several Mosel arrays and inversely, a Mosel array may correspond to several data tables (ranges). We have here examples of various different cases:
Source Files By clicking on a file name, a preview is opened at the bottom of this page. Data Files
multicol2.mos (!******************************************************* Mosel Example Problems ====================== file multicol2.mos `````````````````` Reading several data arrays from a single table. - Using SQL commands - (c) 2008 Fair Isaac Corporation author: S. Heipcke, 2002, rev. Aug. 2023 *******************************************************!) model "Multiple data columns (2)" uses "mmetc", "mmodbc" declarations PRODUCTS: set of string MACH: range COST,COST1,COST2: dynamic array(PRODUCTS,MACH) of real DUR,DUR1,DUR2: dynamic array(PRODUCTS,MACH) of integer end-declarations ! **** Read data in Mosel format **** initializations from "multicol.dat" [COST,DUR] as "ProdData" end-initializations writeln("Mosel format:"); writeln(COST); writeln(DUR) ! **** Read data in diskdata format **** diskdata(ETC_IN+ETC_SPARSE, "multicdd.dat", [COST1,DUR1]) ! Alternatively: use diskdata IO driver: (! initializations from "mmetc.diskdata:sparse" [COST1,DUR1] as "multicdd.dat" end-initializations !) writeln("'diskdata' format:"); writeln(COST1); writeln(DUR1) ! **** Reading data from databases **** ! This assumes the spreadsheet/database contains a table "ProdData" ! in sparse format (i.e., with indices) with the columns "COST" and "DUR" ! Read data from the Excel spreadsheet multicol.xls ! SQLconnect('DSN=Excel Files;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselData/multicol.xls') ! SQLconnect('multicol.xls') ! Read data from the Access database multicol.mdb ! SQLconnect('DSN=MS Access Database;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselData/multicol.mdb') SQLconnect('multicol.mdb') ! Read data from the mysql database multicol (not provided) ! SQLconnect('DSN=mysql;DB=multicol') ! Read data from the SQLite database multicol.sqlite ! SQLconnect('DSN=sqlite;DATABASE=multicol.sqlite') ! SQLconnect('multicol.sqlite') if not getparam("SQLsuccess"): setioerr("Database connection failed") setparam("SQLverbose",true) SQLexecute("select * from ProdData ", [COST2,DUR2]) SQLdisconnect writeln("ODBC:"); writeln(COST2); writeln(DUR2) end-model | |||||||||||||||||||||||||||||||||||||||||||||||
© Copyright 2023 Fair Isaac Corporation. |