Reading a Table From Excel in Gams
This tutorial gives an overview on how to substitution data between GAMS and Microsoft Excel.
GAMS tin can communicate with Microsoft Excel via GDX (GAMS Information Exchange) files. In order to write data from GAMS and to Excel, selected GAMS data can be written into a GDX file and and then to an Excel file: GAMS -> GDX -> Excel. Similarly selected Excel data can exist written to a GDX file so read into GAMS: Excel -> GDX -> GAMS.
Some of GAMS/Excel data commutation tools that provide functionality to exchange data between GAMS and Excel are likewise discussed in the section Data Substitution Tools. The data exchange between GAMS and a CSV (Comma-separated values) file format and GAMS is covered in Data Exchange with Text Files.
From GAMS to Excel
Consider the post-obit modication of the [TRNSPORT] model from the gams model library.
Sets i 'canning plants' / seattle, san-diego / j 'markets' / new-york, chicago, topeka / ; Parameters a(i) 'capacity of found i in cases' / seattle 350 san-diego 600 / b(j) 'need at market j in cases' / new-york 325 chicago 300 topeka 275 / ; Table d(i,j) 'altitude in thousands of miles' new-york chicago topeka seattle 2.5 1.seven 1.viii san-diego 2.5 1.8 1.4 ; Scalar f 'freight in dollars per case per m miles' /90/ ; Parameter c(i,j) 'transport cost in thousands of dollars per instance' ; c(i,j) = f * d(i,j) / m ; Variables x(i,j) 'shipment quantities in cases' z 'total transportation costs in thousands of dollars' ; Positive Variable x ; Equations cost 'define objective function' supply(i) 'observe supply limit at plant i' demand(j) 'satisfy need at market j' ; toll .. z =due east= sum((i,j), c(i,j)*10(i,j)) ; supply(i) .. sum(j, x(i,j)) =l= a(i) ; demand(j) .. sum(i, x(i,j)) =g= b(j) ; Model ship /all/ ; Solve send using lp minimizing z ; Display x.l, ten.m ; *=== Export to Excel using GDX utilities *=== First unload to GDX file (occurs during execution phase) execute_unload "results.gdx" x.L x.Chiliad *=== Now write to variable levels to Excel file from GDX *=== Since we practice not specify a sheet, data is placed in first sheet execute 'gdxxrw.exe results.gdx o=results.xls var=ten.L' *=== Write marginals to a different sheet with a specific range execute 'gdxxrw.exe results.gdx o=results.xls var=x.M rng=NewSheet!f1:i4' After the solve statement, the data (10.50 and x.M) from variable x can be written into a GDX file during the execution time using the command execute_unload:
execute_unload "results.gdx" ten.50 x.Yard The execute_unload command higher up is executed during the actual execution phase to create a GDX file called results.gdx. The solution ten and the marginals of x in the GDX file tin exist written to the Excel file results.xls using GDXXRW tool:
execute 'gdxxrw.exe results.gdx var=10.L' execute 'gdxxrw.exe results.gdx var=ten.K rng=NewSheet!f1:i4' For the first phone call for x.L, in that location is no range specified and the data is written in cell A1 and beyond in the commencement available sheet. For the second call for marginals 10.Chiliad, information will be written to cells F1:I4 in the sheet named NewSheet.
Note that GAMS can likewise write data into a GDX file during compile time. It is too possible to catechumen data stored in a GDX file into an Excel file spreadsheets using GDX2XLS tool and to write GAMS data to standard output formatted as a GAMS programme with information statements using GDXDUMP tool.
From Excel to GAMS
Consider the following modifciation of the [TRNSPORT] model from the gams model library and the file results.xls file created from the previous example.
Sets i 'canning plants' / seattle, san-diego / j 'markets' / new-york, chicago, topeka / ; Parameters a(i) 'capacity of found i in cases' / seattle 350 san-diego 600 / b(j) 'demand at market j in cases' / new-york 325 chicago 300 topeka 275 / ; Table d(i,j) 'altitude in thousands of miles' new-york chicago topeka seattle 2.5 one.7 1.8 san-diego 2.5 1.8 1.4 ; Scalar f 'freight in dollars per example per one thousand miles' /ninety/ ; Parameter c(i,j) 'transport toll in thousands of dollars per case' ; c(i,j) = f * d(i,j) / one thousand ; Variables ten(i,j) 'shipment quantities in cases' z 'total transportation costs in thousands of dollars' ; Positive Variable ten ; Equations cost 'define objective function' supply(i) 'observe supply limit at plant i' need(j) 'satisfy demand at market place j' ; cost .. z =e= sum((i,j), c(i,j)*x(i,j)) ; supply(i) .. sum(j, 10(i,j)) =l= a(i) ; need(j) .. sum(i, x(i,j)) =g= b(j) ; Model transport /all/ ; *=== Import from Excel using GDX utilities *=== First unload to GDX file (occurs during compilation phase) $call gdxxrw.exe results.xls par=Level rng=sheet1!A1:D3 *=== At present import information from GDX Parameter Level(i,j); $gdxin results.gdx $load Level $gdxin *=== Gear up variables to values from Excel file ten.FX(i,j) = Level(i,j); display Level, ten.50; Solve send using lp minimizing z ; Display x.l, x.one thousand ; The data in the Excel file can be loaded into a GDX file using the $phone call command and GDXXRW tool:
$call gdxxrw.exe results.xls par=Level rng=A1:D3 The command $phone call above executes a programme called GDXXRW during compile time. The GDXXRW reads data from the range A1:D3 in results.xls into a GAMS parameter called Level in the GDX file results.gdx. Every bit an output GDX file is non specified when calling GDXXRW, the output file will be derived from the input file by changing the file extension of the input file and removing any path information.
To import data from a GDX file into a parameter, the parameter must be defined over appropriate sets earlier read. The information from a GDX file can be read during the compile fourth dimension using the commands $gdxin and $load:
Parameter Level(i,j); $gdxin results.gdx $load Level $gdxin The first command $gdxin specifies the proper name of the GDX file results.gdx to be read. The command $load reads parameter Level from the GDX file. The second command $gdxin closed the GDX file.
GAMS can read from a GDX file either during compile time or during execution fourth dimension. Come across Example 4 - Reading a GDX File when reading information with domain information and Instance 5 - Reading a GDX File when reading from a GDX file during execution time.
Note that information technology is too possible to write all worksheets of an Excel workbook into a GDX file using XLSDUMP tool.
Data Exchange Tools
There are a number of tools that provide functionality to exchange data betwixt GAMS and an Excel file. This section discusses some of the data exchange tools with some examples. The complete list of the tools can be institute at GAMS/Excel Data Substitution tools.
GDXRW
GDXXRW is a tool to read and write Excel spreadsheet data. GDXXRW can read multiple ranges in a spreadsheet and write the information to a 'GDX' file, or read from a 'GDX' file, and write the data to different ranges in a spreadsheet.
How to use GDXXRW to exchange data between GAMS and Excel is covered in the section From GAMS to Excel and the department From Excel to GAMS. More than details on usage and examples of GDXXRW tool is covered in GDXXRW.
XLS22GMS
XLS2GMS is a simple utility that allows you lot to extract data from an Excel spreadsheet and convert it into a GAMS include file. XLS2GMS tin can exist run interactively or in batch way.
Consider the Excel data from the following spreadsheet:
The data can be imported from the Excel file into a GAMS include file by calling XLS2GMS tool and inserted an include file as parameter data elements using the command $include:
set ssi / 'new york', 'washington dc', 'los angeles', 'san francisco' /; parameter ssdata(ssi) / $call =d:\util\xls2gms I="c:\my documents\test2.xls" B O=d:\tmp\x.inc $include d:\tmp\10.inc /; brandish ssdata; Notice the B parameter, which is needed as in that location are embedded blanks in the labels.
Sometimes a translation betwixt the labels used in the model and the ones used in the is needed. One way to do this is to use a mapping fix in GAMS. Suppose the residuum of the model is defined in terms of the set up I which is divers as:
prepare i / ny, dc, la, sf/; To map a parameter data defined over this ready, the post-obit simple GAMS fragment can be used:
fix map(i,ssi) mapping set up / ny.'new york' dc.'washington dc' la.'los angeles' sf.'san francisco' /; display map; parameter information(i); data(i) = sum(map(i,ssi), ssdata(ssi)); display data; SQL2GMS
In some cases it is user-friendly to consider tabular data in an Excel spreadsheet as a database table and to import it via GDX file using the SQL2GMS tool.
Consider the following spreadsheet:
This table can be read using an SQL query:
SELECT yr,loc,prod,'sales',sales FROM [profitdata$] \ Marriage SELECT year,loc,prod,'profit',profit FROM [profitdata$]
The tabular array proper noun is equal to the sheet name(profitdata). Nosotros tin can pass the query to the Excel ODBC driver using the tool SQL2GMS tool as follows:
set y 'years' /1997,1998/; ready c 'metropolis' /la,nyc,sfo,was/; set p 'product' /hardware,software/; set k 'fundamental' /sales,profit/; $onecho > excelcmd.txt c=DRIVER=Microsoft Excel Commuter (*.xls);dbq=%organisation.fp%turn a profit.xls; q=SELECT yr,loc,prod,'sales',sales FROM [profitdata$] \ UNION SELECT year,loc,prod,'profit',profit FROM [profitdata$] ten=fromexcel.gdx $offecho $call =sql2gms @excelcmd.txt parameter d(y,c,p,k) ; $gdxin excel.gdx $load d=p display d; and the DISPLAY results will be:
--- 21 PARAMETER d FROM SQL2GMS Index 1 = 1997 sales profit la .hardware 80.000 5.000 la .software 60.000 10.000 nyc.hardware 100.000 15.000 nyc.software 130.000 25.000 sfo.hardware 50.000 9.000 sfo.software threescore.000 6.000 was.hardware 80.000 7.000 was.software ninety.000 8.000 INDEX 1 = 1998 sales profit la .hardware 88.000 5.250 la .software 66.000 10.500 nyc.hardware 110.000 15.750 nyc.software 143.000 26.250 sfo.hardware 55.000 nine.450 sfo.software 66.000 vi.300 was.hardware 88.000 7.350 was.software 99.000 8.400
GDXVIEWER
GDXVIEWER is a tool to view and catechumen data contained in GDX files. It can also export to csv, xls, xml-files and pivot tables. The usage and examples are covered in GDXVIEWER.
GDX2XLS
GDX2XLS tool o conver the contents of a GDX file into an Excel file or an xml-file. The usage and examples are covered in GDX2XLS.
thompsonbettandow1937.blogspot.com
Source: https://www.gams.com/33/docs/UG_DataExchange_Excel.html
0 Response to "Reading a Table From Excel in Gams"
Post a Comment