====== Data Exchange with Excel ====== ===== A tutorial on how to use the GDX facilities in GAMS to read data from Excel and to write data to Excel. ===== This section gives a brief overview on how to use the GDX facilities in GAMS to read data from Excel and to write data to Excel. For more detailed information please consult the document on [[http://www.gams.com/dd/docs/tools/gdxutils.pdf|GDX Facilities and Tools]]. * A sample model to write an Excel file: {{excel:gms2xls.zip|gms2xls.gms}} * A sample model to read from an Excel file: {{excel:xls2gms.zip|xls2gms.gms}} System Requirements: A GAMS system Disitrbution 21.0 or later is required. ==== Introduction ==== GAMS communicates with Excel via GDX (GAMS Data Exchange) files. A GDX file is a file that stores the values of one or more GAMS symbols such as sets, parameters variables and equations. GDX files can be used to prepare data for a GAMS model, present results of a GAMS model, store results of the same model using different parameters etc. A GDX file does not store a model formulation or executable statements. GDX files are binary files that are portable between different platforms. They are written using the byte ordering native to the hardware platform they are created on, but can be read on a platform using a different byte ordering. In order to write data from GAMS to Excel, the user writes a GDX file and then writes the Excel file from the GDX file: GAMS -> GDX -> Excel. This is practically seamless for the user and requires few commands. The process to import data from an Excel file to GAMS is similar: Excel -> GDX -> GAMS ==== Example: GAMS to Excel ==== We will build on the simple [[http://www.gams.com/modlib/libhtml/trnsport.htm|transportation model]] from the GAMS Model library and write the solution ''x'' and the marginals of ''x'' to an Excel file. After the solve statement, we unload the data (''x.L'' and ''x.M'') to a GDX file using the execute_unload command: execute_unload "results.gdx" x.L x.M Note that the ''execute_unload'' command is executed during the actual execution phase (not during compilation time as $ control options) and creates a GDX file called ''results.gdx''. Now let us write the data from the GDX file to an Excel file called ''results.xls''. We do this using the GDXXRW utility execute 'gdxxrw.exe results.gdx var=x.L' execute 'gdxxrw.exe results.gdx var=x.M rng=NewSheet!f1:i4' For the first call for ''x.L'', there is no range specified and the data is written in cell A1 and beyond in the first available sheet. For the marginals ''x.M'' data will be written to cells ''F1:I4'' in the sheet NewSheet. Note that we specified ''var=x.L'' and ''var=x.M''. If the user wishes to write parameters to the Excel file, the relevant command is par. ==== Example: Excel to GAMS ==== Again, we will use the transportation model and make use of the ''results.xls'' file created by the previous model. First we will create the GDX file from the Excel file. We will make use of the GDXXRW utility: $CALL GDXXRW.EXE results.xls par=Level rng=A1:D3 Note that since we are using the $CALL command, this occurs during the compilation phase and not during execution time. We specify that the data in the range ''A1:D3'' is read in as a parameter called ''Level''. The resulting GDX file will be called results.gdx Before we can read in the data, we must define a parameter called ''Level'' over the appropriate sets: Parameter Level(i,j); $GDXIN results.gdx $LOAD Level $GDXIN The GDXIN ''results.gdx'' command specifies that data will be read in from the appropriate GDX file. We then import data structures values using the ''$LOAD'' command. When we are finished, we terminate with ''$GDXIN''. In the example below, we then fix the level values of the variable ''x'' to the parameter ''Level'' so that solving results in a trivial fixed model. ===== Importing from Excel ===== ==== The gdxxrw tool ==== GDXXRW is a utility to read and write Excel spreadsheet data. GDXXRW can read multiple ranges in a spreadsheet and write the data to a 'GDX' file, or read from a 'GDX' file, and write the data to different ranges in a spreadsheet. For further information and the documentation, please consult the documentation about the [[gdx:gdxtools|wtools]]. ==== The sql2gms tool ==== In some cases it is convenient to consider tabular data in an Excel spreadsheet as a database table and to import it using the sql2gms tool. Consider the spreadsheet: {{ excel:profit.png?300 }} This table can be read using an SQL query: SELECT year,loc,prod,'sales',sales FROM [profitdata$] UNION SELECT year,loc,prod,'profit',profit FROM [profitdata$] The table name is equal to the sheet name(profitdata). We can pass the query to the Excel ODBC driver using the tool SQL2GMS as follows: $ontext Test MS EXCEL access through ODBC $offtext set y 'years' /1997,1998/; set c 'city' /la,nyc,sfo,was/; set p 'product' /hardware,software/; set k 'key' /sales,profit/; $onecho > excelcmd.txt c=DRIVER=Microsoft Excel Driver (*.xls);dbq=%system.fp%profit.xls; q=SELECT year,loc,prod,'sales',sales FROM [profitdata$] UNION SELECT year,loc,prod,'profit',profit FROM [profitdata$] x=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 60.000 6.000 was.hardware 80.000 7.000 was.software 90.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 9.450 sfo.software 66.000 6.300 was.hardware 88.000 7.350 was.software 99.000 8.400 Here are the {{excel:excel_sql.zip|input files}}, for more information about sql2gms click [[gdx:gdxtools|here]]. ==== Importing CSV Files ==== The [[wp>Comma-separated_values|CSV]] (Comma-separated values) file format can be easily imported into GAMS at compilation time. This format is for instance easily generated by Excel, using its Save As CSV functionality. Consider the GAMS table: table d(i,j) 'distance in thousands of miles' NEW-YORK CHICAGO TOPEKA SEATTLE 2.5 1.7 1.8 SAN-DIEGO 2.5 1.8 1.4 ; In Excel that table can be easily entered as follows: {{excel:excel1.png|}} Notice that we added a 'dummy' string in cell A1. This is necessary as we need a placeholder there (the underlying problem is a bug in GAMS: the comma should be enough to signal the end of a field). Now we save this worksheet as a CSV file - which will look like: dummy,new-york,chicago,topeka seattle,2.5,1.7,1.8 san-diego,2.5,1.8,1.4 This file can now be included directly into GAMS by using the $ondelim and $offdelim commands: Sets i canning plants / seattle, san-diego / j markets / new-york, chicago, topeka / ; table d(i,j) 'distance in thousands of miles' $ondelim $include data.csv $offdelim display d; Notice we have left out a ';' between the ''TABLE'' statement and the ''display'' statement. Usually GAMS is quite tolerant regarding this, and indeed the listing file shows: 1 Sets 2 i canning plants / seattle, san-diego / 3 j markets / new-york, chicago, topeka / ; 4 table d(i,j) 'distance in thousands of miles' INCLUDE C:\temp\data.csv 7 dummy ,new-york,chicago,topeka 8 seattle,2.5,1.7,1.8 9 san-diego,2.5,1.8,1.4 11 display d; ... ---- 11 PARAMETER d distance in thousands of miles new-york chicago topeka seattle 2.500 1.700 1.800 san-diego 2.500 1.800 1.400 ==== Caveat: CSV-Files and Non American English language settings ==== A well known problem is internationalization. In non-US countries often different conventions are used for formatting numbers. An example is the use of a decimal comma instead of a decimal point. This has a ripple through effect as the comma is then no longer available to be used as a separator symbol. This is illustrated when we change in Windows the Regional Settings to German (Standard) and save our spreadsheet table {{excel:excel12.png|}} as a CSV file (''input.csv''), we get: dummy;new-york;chicago;topeka seattle;2,5;1,7;1,8 san-diego;2,5;1,8;1,4 The decimal points have become decimal comma's and the comma separator symbols are now semi-colons. This file can not be processed by GAMS as GAMS does not consult the Windows settings but sticks to US standardization. To change the comma into a dot and the semicolon into a comma we just use one of the POSIX tools, which are part of any GAMS system and write a few lines of GAMS code: * translate , to . using tr $call "tr , . temp.csv" * translate ; to , using tr $call "tr ; , output.csv" table data(*,*) $ondelim $include output.csv $offdelim display data; or * translate , to . and ; to , using sed $onecho > sedscript s/,/./g s/;/,/g $offecho $call sed -f sedscript input.csv > output.csv table data(*,*) $ondelim $include output.csv $offdelim display data; and get ---- 14 PARAMETER data new-york chicago topeka seattle 2.500 1.700 1.800 san-diego 2.500 1.800 1.400 Note: When importing and exporting data using the [[gdx:gdxtools|GDX-tools or Wtools]] , the regional settings are not an issue. ==== XLS2GMS ==== XLS2GMS is a simple utility that allows you to extract data from an Excel spreadsheet and convert it into a GAMS include file. When running xls2gms.exe without extra command line parameters, the utility will run in interactive mode. It will come up with the following form: {{excel:xls2gms.png|}} The input file consists of an .XLS file. By default the used part of the first sheet is exported, but this can be changed by setting an appropriate range. An example of a range that can be specified is Sheet2!A1:G8. An output file is created that can be used as a GAMS include file. The browse buttons open a File Open or a Save As dialog box that can help you in navigating around on your hard disk. It is advised to use absolute paths, as for windows applications it is not always obvious what the current directory is. The philosophy of the utility is that you write pure GAMS syntax in the spreadsheet. I.e. you can write GAMS statements such as ''TABLE'', ''SET'', ''PARAMETER'' or parts of them. It is your responsibility to write correct GAMS syntax. The only "smart" thing the utility does it to align cells, such that GAMS TABLE data are correctly handled. In the above example we have a simple spreadsheet: {{excel:xls2gms_exa.png|}} and call xl2gms with: $onecho > commands.txt I=C:\temp\exa.xls O=exa.inc R=Sheet1!A1:C3 $offecho $call ="c:\gams\xls2gms.exe" @commands.txt the file exa.inc will look like: * ----------------------------------------------------- * XLS2GMS 2.8 Dec 24, 2007 WIN.FR.NA 22.6 280.000.000.vis Delphi * Erwin Kalvelagen, GAMS Development Corp. * ----------------------------------------------------- * Application: Microsoft Excel * Version: 12.0 * Workbook: C:\temp\exa.xls * Sheet: Sheet1 * Range: $A$1:$C$3 * ----------------------------------------------------- j1 j2 i1 1 3 i2 2 4 * ----------------------------------------------------- Note: The complete documentation is available [[http://www.gams.com/dd/docs/tools/xls2gms.pdf|here]]. ===== Exporting to Excel ===== ==== The gdxxrw tool ==== GDXXRW is a utility to read and write Excel spreadsheet data. GDXXRW can read multiple ranges in a spreadsheet and write the data to a 'GDX' file, or read from a 'GDX' file, and write the data to different ranges in a spreadsheet. For further information and the documentation, please consult the documentation about the [[gdx:gdxtools|wtools]]. ==== The gdxviewer tool ==== GDXVIEWER is a tool to view and convert data contained in GDX files. It can also export to csv, xls, xml-files and pivot tables. For further information and the documentation, please consult the documentation about the [[gdx:gdxtools#the_wtools|gdxviewer]]. ==== Excel CSV Import ==== When a CSV file is saved with an extension .csv Excel will read it directly. Either click on it in the explorer or use in Excel: File|Open and then select Files of Type: Text Files (*.prn; *.txt; *.csv). Now the file will be read directly into Excel without further questions. Below is a GAMS fragment to create a .csv file: file results /results.csv/; results.pc=5; put results; put "Model status",transport.modelstat/; put "Solver status",transport.solvestat/; put "Objective",z.l/; put "Shipments"/; loop((i,j), put i.tl, j.tl, x.l(i,j)/ ); putclose; Note: The [[gdx:gdxtools#the_wtools|GDXVIEWER]] can also export to CSV files. It is possible to spawn Excel automatically from GAMS to view the result by using the ShellExecute utility: execute '=shellexecute results.csv'; ==== gdx2xls ==== The gdx2xls tool dumps the contents of a gdx file into a spreadsheet or an xml-file. Please visit the [[gdx:gdxtools|gdx-Tools section]] for more details. ===== Mapping Index Label Names ===== ==== Using a Mapping Set in GAMS ==== Sometimes a translation step is needed between the labels used in the model and the ones used in the spreadsheet. One way to do this is to use a mapping set in GAMS. Consider the following case. The spreadsheet looks like: {{excel:setmap1.jpg }} This can be imported easily using the GAMS code: 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\x.inc /; display ssdata; Notice the B parameter, which is needed as there are embedded blanks in the labels. Now suppose the rest of the model is defined in terms of the set I which is defined as: set i /NY,DC,LA,SF/; To calculate a parameter data defined over this set, the following simple GAMS fragment can be used: set map(i,ssi) mapping set / ny.'new york' dc.'washington dc' la.'los angeles' sf.'san francisco' /; display map; parameter data(i); data(i) = sum(map(i,ssi), ssdata(ssi)); display data; ==== Inside the Database/Spreadsheet ==== Translation of index labels can be done inside GAMS (see the previous paragraph), but also very conveniently inside the database. Consider again the following data to import: {{excel:setmap2.jpg}} The way to handle the index conversion inside GAMS is: set ssi / 'new york', 'washington dc', 'los angeles', 'san francisco' /; parameter ssdata(ssi) / $call =d:\util\mdb2gms I="c:\my documents\test.mdb" Q="select city,value from [example table]" B O=d:\tmp\x.inc $include x.inc /; display ssdata; set i /NY,DC,LA,SF/; set map(i,ssi) / ny.'new york' dc.'washington dc' la.'los angeles' sf.'san francisco' /; display map; parameter data(i); data(i) = sum(map(i,ssi), ssdata(ssi)); display data; Notice that the B parameter is used to protect spaces inside label names. Square brackets are used inside the SQL statement to protect spaces embedded in the table name. Another way would be to add a table to the Access database: {{excel:setmap3.jpg|}} Now we can have the simpler GAMS fragment: set i /NY,DC,LA,SF/; parameter data(i) / $call =d:\util\mdb2gms I="c:\my documents\test.mdb" Q="select [GAMS City], value from [example table],CityMapper where [Access City]=city" O=d:\tmp\x.inc $include d:\tmp\x.inc /; display data; Note that this is a very long command line. It is better to move the command Line parameters to a separate file and use the @ notation to instruct mdb2gms to read command line parameters from that file. =====Executing GAMS from Excel===== Please also visit our notes and examples about [[env:spawning_gams_from_excel|executing GAMS from Excel]].