Excel.gms : Reads Data from Excel through ODBC

Description

The spreadsheet is accessed via ODBC (DSN-less connection).

This model is referenced in "Example 9 - Reading from an MS Excel Spreadsheet"
from the SQL2GMS Documentation.

Keywords: SQL2GMS, data exchange, GAMS language features


Category : GAMS Data Utilities library


Main file : Excel.gms   includes :  Excel.gms  Profit.xls

$title Reads Data from Excel through ODBC (Excel,SEQ=058)

$onText
The spreadsheet is accessed via ODBC (DSN-less connection).

This model is referenced in "Example 9 - Reading from an MS Excel Spreadsheet"
from the SQL2GMS Documentation.

Keywords: SQL2GMS, data exchange, GAMS language features
$offText

$if %system.filesys% == UNIX $abort.noError 'This model cannot run on a non-Windows platform';
$call msappavail -Excel
$if errorlevel 1 $abort.noError 'Microsoft Excel is not available!';

Set
   y 'years'   / 1997, 1998 /
   c 'city'    / la, nyc, sfo, was  /
   p 'product' / hardware, software /
   k 'key'     / sales, profit      /;

$onEcho > excelcmd.txt
C=DRIVER=Microsoft Excel Driver (*.xls);dbq=Profit.xls;
Q=SELECT year, loc, prod, 'sales', sales FROM [profitdata$] UNION SELECT year, loc, prod, 'profit', profit FROM [profitdata$]
O=excel.inc
$offEcho

$call sql2gms @excelcmd.txt > %system.nullfile%
$ifE errorLevel<>0 $abort Error using SQL2GMS!

Parameter d(y,c,p,k) /
$include excel.inc
/;
display d;