SCA Applets Example

SCA Utility Applet: XLSREAD

Download the XLSREAD Applet (8/25/2000, 520K)

The SCA Applet, XLSREAD, is used to read numeric data from an Excel spreadsheet and write the data into a plain ASCII file or an SCA data macro. The data can then be read into the SCA workspace using the INPUT or CALL commands.

SCA Development Partners may also leverage this applet to read data from an Excel spreadsheet and export it into a simpler format which can be used by other applets and user-developed programs. The XLSREAD applet, as well as other programs, can be executed coveniently by imbedding instructions directly within an SCA macro procedure. Arguments are passed to the XLSREAD applet by submitting a control file or by setting command line options. The applet is then executed from within an SCA macro procedure using the RUN or RUNW commands.

Below is an example of how to contruct a control file as imbedded commands within an SCA macro procedure. The BUILD command specifies the name of the control file to create. The statements between the BUILD and ENDBUILD commands are the actual instructions to be written to the control file. The ENDBUILD command closes the opened file and allows the user to continue processing other commands in the SCA System.

In this example, a control file named "xlsread.ctl" is created. The commands written to this file instruct the applet to read three variables (X, Y, and Z) from a worksheet named "sheet1" in an Excel file named "myfile.xls". The data extracted from the Excel spreadsheet is then written to an output file named "test.dat" in ASCII format.

BUILD 'xlsread.ctl'
READ VARIABLES X, Y, Z. FILE 'myfile.xls'. @
SHEET 'sheet1'. OUTFILE 'test.dat'. @
TYPE ASCII.
ENDBUILD

Once the necessary instructions for the applet are generated in the "xlsread.ctl" file and the file is closed using the ENDBUILD command, the applet may be executed by issuing the following RUN command in the SCA System:

RUN XLSREAD 'xlsread.ctl'

After the "RUN XLSREAD ..." command is executed, the data may be read into the SCA System workspace by issuing the following INPUT command:

INPUT X, Y, Z. FILE 'test.dat'.

The XLSREAD applet provides an alternative command syntax based on parameter tags. This alternative command syntax is useful when many of the default parameter settings of an applet can be used. An example of this shorthand command style is presented below. It accomplishes the same result as the control file example above:

RUN XLSREAD /V:X,Y,Z /F:'myfile.xls' /O:'test.dat'

If a program does not have an "SCA-like" control language, you can still leverage SCA Applet technology to execute these programs under the SCA System. However, these programs must be capable of executing in batch mode. The instructions for building commands for alternative style programs follow the example presented here.

XLSREAD Applet syntax

READ VARIABLES v1, v2, --- .
       FILE 'c'.
       SHEET 'c'.
       OUTFILE 'c'.
       FORMAT IS 'c'.
       SPAN i1, i2.
       TYPE w.
       LABELS./NO LABELS.

@
@
@
@
@
@
@

Required sentence: VARIABLES, FILE

Legend: v -- variable name; i -- integer value;
'c'-- character string; w -- keyword

Sentence Descriptions

Note:

The XLSREAD applet provides an alternative syntax called parameter tags. The parameter tags are indicated next to the program sentences enclosed within parenthesis.

VARIABLES sentence (/V)
   The VARIABLES sentence is used to specify the names of the
   variables that are to be read from the Excel spreadsheet.
   The names must appear in the first row of the Excel
   spreadsheet conforming to normal conventions. The names of
   the variables must be no more than eight characters and may
   not contain spaces.

FILE sentence (/F)
   The FILE sentence is used to specify the Excel file name,
   'c',from which the data will be read. The file name may
   contain spaces but may not exceed 255 characters in length.

SHEET sentence (/S)
   The SHEET sentence is used to specify the Excel sheet
   name, 'c',from which the data will be read. The sheet
   name may contain spaces but may not exceed 255 characters
   in length. If this sentence is omitted, the default
   'SHEET1' is used.

OUTFILE sentence (/O)
   The OUTFILE sentence is used to specify the name of the
   file to which the extracted data will be written, 'c'.
   The file name may not contain spaces and may not exceed
   255 characters in length. If this sentence is omitted,
   the data is displayed on the screen only.

FORMAT sentence (/FMT)
   The FORMAT sentence is used to specify a Fortran-based
   format for the write statement. The acceptable format
   specification must be enclosed by a pair of apostrophes
   or quotes, '(3F14.4)'. The maximum length for any single
   record is 132. If this sentence is omitted, the default
   '(nF12.3)' is used where n represents the number of
   variables written to the file/screen.

SPAN sentence (/SPA)
   The SPAN sentence is used to specify the range of data to
   be written to the file. The first integer specified
   represents the beginning row and the second integer
   specified represents the ending row for the data. If
   the ending row is specified as zero, or is larger than
   the number of available rows, XLSREAD will reset this
   value to the last available data row in the Excel file.

TYPE sentence (/T)
   The TYPE sentence is used to specify whether to generate
   a plain ASCII data file, SCAGRAF data file, or an SCA Data
   Macro file. The keywords are 'ASCII' to generate a plain
   ASCII data file, 'SCAGRAF' to generate a data file for
   the SCAGRAF utility applet, or 'MAD' to generate an SCA
   Data Macro file. The default type is plain ASCII.

LABELS sentence (/L:ON)
   The LABEL sentence is used to specify whether or not to
   write the variable names in the first row when XLSREAD
   generates a plain ASCII data file. The default is
   NO LABELS.