DIF-file interface

Transcribed from the printed issue; not yet reviewed. Read it in the PDF of the issue, page 149

Abstract: Spreadsheet packages offer facilities that complement those available in APL. This article describes software that allows easy data transfer between the two environments. The conversion software is written in C, but can be invoked from the APL workspace in a manner transparent to the user.

Introduction¶

Most companies operate budgeting systems. In Cocking & Drury we have a 12-monthly operating plan, which we currently maintain using Symphony. The plan contains anticipated headcount, expenditure and revenue, divided into about forty sub-categories. We enter ‘actual’ data as it becomes available, and we produce variance reports, year-to-date reports, and consolidations that combine figures by department, location and subsidiary company. The data is not only used to monitor our performance against plan; we also use it to improve the accuracy and flexibilty of the model from which our plan is derived.

While Symphony or Lotus 1-2-3 are ideal for data entry and for the preparation of simple reports (e.g. P & L for a single unit ), APL is much better for the sort of data manipulation required for variance reports, consolidations and extrapolation.

The ideal solution would be to use each tool for the task it is best at. We needed an easy way of moving data from the spreadsheet environment into an APL workspace and back again; to our surprise, we found nothing available that met our requirements.

The structure of spreadsheet data¶

We wanted a method of moving data between a spreadsheet and the APL workspace. Although we found no suitable software, there is a standard file structure developed for this sort of requirement. Called the DIF file format, it was developed by Software Arts Inc (authors of VisiCalc). Most spreadsheets available today can export and import data in DIF format.

The file format was designed with BASIC and PASCAL programmers in mind, and DIF files are not very tractable in APL. It would be hard to write APL functions to handle DIF files efficiently, and the resulting code would be difficult to maintain and prone to WS-full.

A spreadsheet is just a table of data, though, and ought to be easy to handle in APL. The only problem lies in the fact that each spreadsheet cell may contain either a single (scalar) number or a text (character vector) label. Let us look at a simple example

              Jan 85       Feb 85       Mar 85        Total
Sales          89000        93000       117000       299000
Costs          73450        81830        95460       250740
-----------------------------------------------------------
Profit         15550        11170        21540        48260
-----------------------------------------------------------

Figure 1 - sample spreadsheet

We can divide this up into the cells which were set up in SYMPHONY. The result is shown in Figure 2.

Figure 2 - spreadsheet cells

If we were using a nested array system, such as Sharp APL, APL2 or APL*PLUS/1000, we could represent this structure as a matrix in which each element was either a number or an enclosed character vector. We wanted to use APL*PLUS/PC, so we needed to find a simple representation in ‘conventional’ APL.

We found a solution that has proved easy to understand, and straightforward to implement. The spreadsheet is represented by four APL arrays - a numeric matrix, a text matrix, a mask matrix and a vector of text column widths.

The numeric matrix has the same number of rows and columns as the spreadsheet. Numeric spreadsheet cells match numeric values in the matrix; text cells match numeric matrix entries of zero. For the example above, the numeric matrix looks like the one in Figure 3.

0      0      0      0      0
0  89000  93000 117000 299000
0  73450  81830  95460 250740
0      0      0      0      0
0  15550  11170  21540  48260
0      0      0      0      0

Figure 3 - ‘nums’ matrix

The text matrix has as many rows as the spreadsheet. Each column of the spreadsheet corresponds to a set of consecutive characters in the text matrix. The widths vector tells us how many characters wide each column of the spreadsheet is. Going back to our example, the text matrix (called chars) and widths vector (called widths) look like Figure 4.

      chars
                    Jan 85         Feb 85        Mar 85         Total
Sales
Costs
---------------------------------------------------------------------

Profit
---------------------------------------------------------------------

      widths
15 9 9 9 9

Figure 4 - ‘chars’ and ‘widths’

In most cases it will be clear from looking at ‘nums’ and ‘chars’ which cells of the original spreadsheet contained numbers and which ones contained characters. However, this information is explicitly held in the mask matrix, which has one character per spreadsheet cell - a ‘t’ for text cells and a ‘v’ for numeric-values cells. In our example, the mask matrix is shown in Figure 5.

      mask
ttttt
tvvvv
tvvvv
ttttt
tvvvv
ttttt

Figure 5 - ‘mask’ matrix

The transfer technique¶

The processing of our budget data is done by several people, not all of whom are expert in APL. We needed to be able to package up the transfer of spreadsheet data into an APL function that could be integrated into a reporting system. In principle this transfer consists of three distinct stages:

(i)

conversion of the spreadsheet into DIF file format

(ii)

conversion of the DIF file to APL format

(iii)

fetching the data into the active APL workspace

The first stage can be done by one of the programs on the SYMPHONY utilities diskette. The program (WRKDIF) expects a worksheet pathname and a DIF-file pathname to follow the program name on the line where it is invoked; it can be called from within APL by means of the quad-CMD system command, provided that it has been copied into the appropriate DOS sub-directory.

The second stage could be done by an APL function, but the DIF file format means that this would be very inefficient. We decided that we would develop a DIF file conversion program in the C programming language. C has many advantages for this sort of application; these include portability, ease of maintenance, efficiency and simplicity.

The program that we developed expects a DIF filename and a DOS filename to follow it on the line where it is invoked. It reads the DIF file, creating the numeric and mask matrices and storing each text string and column width in appropriate data structures. It then creates a DOS file, and writes out a series of entries in the same format used internally by APL. The file contains

(i)

the shape of the spreadsheet (number of rows, number of columns, both held as two-byte integers)

(ii)

the ravelled numeric matrix (an array of floating-point numbers in IEEE format, as used by APL*PLUS/PC).

(iii)

the ravelled mask matrix (a series of bytes representing ASCII characters)

(iv)

the width vector (a series of two-byte integers)

(v)

the ravelled character matrix (in the same format as the mask matrix.)

The user is blissfully unaware of this, as the APL program reads all the relevant data into APL variables using the APL*PLUS ‘native file read’ quad-function. A listing of the relevant APL function is included in appendix 2, as is a sample session using the interface.

Software development in C¶

We approached the development of the DIF file conversion program with some apprehension. I was keen to do the development myself, as we expect the combination of C system software and APL applications software to play an increasingly important part in our business. I found many points of similarity between C and APL, but there are also fundamental differences. Like APL, C is terse and powerful. This can lead to elegant software, and it can help you produce maintainable code - but you will need maturity and self-restraint if you want to be remembered for writing programs that are both elegant and easy to maintain!

C differs from APL in that it has (and needs to have) a full range of constructs to control looping and branching. Because arrays in C have to be manipulated one element at a time, loops are common. The language constructs (linked with appropriate conventions about program layout) mean that loops can be efficiently executed and easily understood.

Another fundamental difference is that C is a compiled language. (C interpreters exist, but lose the efficiency advantage of compiled code). Although C compilers can be fairly fast, they cannot yet yield the same programmer productivity as APL. C Programs can run much faster, however - it takes about 6 seconds to generate a full cross-reference listing for the DIF file interface, whereas an APL cross-reference of a similar sized APL system would take several minutes on a PC.

For those who do not know C, but wish to get a flavour of the language, a listing of one of the functions used in the DIF file interface is included as appendix 1. The function (called sa_iota) looks up a string of characters in a vector of strings. It resembles dyadic iota in APL.

It has not been possible to list the complete DIF source; it would take up too much space, and in any case a commercial product based on this software is currently undergoing beta-test.

Conclusion¶

We feel we have achieved considerable benefit through this development. Much has been gained by using the right tool for the right job - we have combined the ease of use of spreadsheet software with the power of APL. We have also been able to integrate package software, tailored C code and APL thanks to the powerful interfacing capability of APL*PLUS/PC.

A further benefit awaits us. When spreadsheets producing DIF files become widespread under UNIX, we will be able to use all the interface software without modification. C is portable across operating systems, and both the APL code and internal data formats are fully compatible with APL*PLUS/UNX.

Appendix 1 - C function listing¶

sa_iota(v,s) /* return index of string s in vector of string pointers v.
                return -1 if not found. last element of v must be NULL. */
char *v[], *s;
{
  int c;
  char **p;

  p = v;
  while (*p) {
       c = strcmp(s,*p);
       if (!c) return(p-v);
       p++;
  }
  return(EOF);
}

Appendix 2 - Sample APL session¶

      ∇WRK2APL[⎕]∇
[0]   WRK2APL pathname;rho;cells;⎕IO;char;int;real;tw;trho
[1]   ⍝ WRK File into APL variables
[2]   ⍝
[3]   ⍝ Calls <if>, <∆cmd>. Invokes <WRKDIF.EXE>, <DIF.EXE>.
[4]   ⍝
[5]   ⍝ <nums>, <mask>, <widths> and <chars> are used to contain
[6]   ⍝ the relevant items of the spreadsheet.
[7]   ⍝
[8]   ⍝ <pathname> is used as the source file;
[9]   ⍝ <TEMP.DIF> is used as the DIF transfer file, which is erased.
[10]  ⍝ <TEMP.APL> is used as the APL transfer file, which is erased.
[11]   ⎕NUNTIE ⎕NNUMS ⋄ ⎕IO←1 ⋄ char←82 ⋄ int←163 ⋄ real←645
[12]   →err if 0≠∆cmd 'WRKDIF ',pathname,' TEMP.DIF' ⍝ CONVERT WRK TO DIF
[13]   →err if 0≠∆cmd 'DIF TEMP.DIF TEMP.APL' ⍝ CONVERT DIF TO APL FORMAT
[14]   →err if 0≠∆cmd 'ERASE TEMP.DIF'
[15]   'TEMP.APL' ⎕NTIE ¯1
[16]   cells←×/rho←⎕NREAD ¯1,int, 2 0 ⍝ READ SHAPE VECTOR
[17]   nums←rho⍴⎕NREAD ¯1,real,cells ⍝ READ NUMERIC VALUES
[18]   mask←rho⍴⎕NREAD ¯1,char,cells ⍝ READ MASK
[19]   tw←+/widths←⎕NREAD ¯1,int,rho[2] ⍝ READ WIDTHS
[20]   chars←trho⍴⎕NREAD ¯1,char,×/trho←rho[1],tw ⍝ READ CHARS
[21]   'TEMP.APL' ⎕NERASE ¯1 ⋄ →0 ⍝ ERASE APL TRANSFER FILE
[22]  err:'OUCH'

      WRK2APL 'C:\SYMPHONY\RJC\EG1.WRK'

      nums
 0      0      0      0      0
 0  89000  93000 117000 299000
 0  73450  81830  95460 250740
 0      0      0      0      0
 0  15550  11170  21540  48260
 0      0      0      0      0

      chars
               Jan 85   Feb 85   Mar 85    Total
Sales
Costs
------------------------------------------------
Profit
================================================

      widths
15 9 9 9 9

      mask
ttttt
tvvvv
tvvvv
ttttt
tvvvv
ttttt

      +\0 ¯1↓nums
 0      0      0      0
 0  89000 182000 299000
 0  73450 155280 250740
 0      0      0      0
 0  15550  26720  48260
 0      0      0      0