## `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.

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