xlsnz: An Automation Tool for
Extracting Numeric Nonzeros from Excel Workbooks

Thomas F. Rutherford

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.


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.


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.