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.xlsx

$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

$callTool win32.msappavail Excel
$if errorlevel 1 $abort.noError "No Excel 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, *.xlsx, *.xlsm, *.xlsb);dbq=.\Profit.xlsx;
Q=SELECT year, loc, prod, 'sales', sales FROM [profitdata$A1:E17] UNION SELECT year, loc, prod, 'profit', profit FROM [profitdata$A1:E17]
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;