xlsnz: An Automation Tool for
Extracting Numeric Nonzeros from Excel Workbooks
May 2006
* This web page documents a computational tool which has been
developed within the project "Indicators and Quantitative Tools for Improving
the Impact Assessment Process for Sustainability" (I.Q. Tools), 6th Framework
Programme of the European Commission, Contract SSP1-CT-2003-502078, Thematic
Priority 8: Policy Oriented Research.
Overview
A common task in GAMS programming involes extracting data from Excel
workbooks. This page describes a "no-frills alternative" to the
GDXXRW
utility which is provided for this purpose with the GAMS distribution media. My
routine simply dumps every numeric nonzero value from a given Excel
workbook. These data are written to a GAMS-readable text file. This
file contains a set definition (wsdim) and one GAMS
parameter declaration per worksheet. Parameters are named
ws1(*,*), ws2(*,*) etc. Worksheet names are written
as parameter descriptive text.
Here is a typical command-line invocation:
d:\xlsnz>xlsnz IO-Ecuador1998
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
xlsfile = D:\xlsnz\IO-Ecuador1998.xls
gmsfile = D:\xlsnz\IO-Ecuador1998.gms
Reading worksheet oferta98, nrow,ncol = 81,70
Writing parameter ws1 (oferta98, 973 nonzero values)
Reading worksheet utiliza98, nrow,ncol = 80,79
Writing parameter ws2 (utiliza98, 2359 nonzero values)
Output from this invocation is IO-Ecuador1998.gms which
appears as follows:
set wsdim /1*81/;
parameter ws1(*,*) "oferta98" /
3.1 1997
6.8 1
6.9 2
6.10 3
...
/;
parameter ws2(*,*) "utiliza98" /
3.1 1998
6.12 1
...
/;
One parameter is written for each of two worksheets in the source
workbook. The syntax of the data is:
row index.column index value
Why use this utility?
This utility is provided for GAMS programmers who wish to read Excel
data files in their original, unmodified format. Unlike
GDXXRW
which requires introduction of appropriate row and column labels for
individual worksheet tables, the XLSNZ utility can read Excel
files without alteration. This approach may require additional GAMS
programming, as it is necessary to define mapping tuples which
bridge set identifiers to row and column indices, but I generally find
this to be less onerous than introducing additional label rows and columns
into an unfamiliar Excel file. Use XLSNZ when working with a
static Excel workbook. This utility is ill-suited to
situations in which the source data files are frequently modified.
Excel Programming Tip
The XLSNZ routine writes out data with integer labels. You
can view the original XLS data file with the same labels if you (i) open
the workbook, and (ii) enter Tools, Options, (iii)
Choose the General tab, and (iv) Click R1C1 reference
style.
Installation
Here is a zip file which contains the utility
and a few sample xls files. The test.bat routine illustrates
usage.
Here is a sample application of
XLSNZ which illustrates how it can be used to extract tables
of data from appendices provided in the International Energy Outlook
(2007) from the Energy Information Administration Office of Integrated
Analysis and Forecasting.
