Purpose 3

Simple Examples 4

Help-file Printout 7

Available File Formats 12

Defining Output Data 15

Syntax Guide 19

Version History 43

DOWNLOAD is a utility program which produces output files in ASCII, WordPerfect, dBASE, or DIF formats. Output files can be used by service bureaus and government agencies or used directly by standard software packages. DOWNLOAD is easy to use, because it uses syntax like the LIST statement. The examples in the next chapter illustrate the ease with which the program can be used.

This software was written at Cedarville College by Doug Sjoquist and modified by Dave Rotman. You may freely distribute this software, but this software is not to be sold by itself nor as part of any other software package. A current version of the software may be obtained via anonymous ftp from:

ftp.cedarville.edu

This software is made available on an "as-is" basis, with no warranty of any kind.

Currently, the software is being maintained by Dave Rotman. Suggestions for new features or bug fixes should be sent to rotmand@cedarville.edu. Since Dave cannot devote a lot of time to support, you can often get usage questions answered more quickly by posting an item to e-mail list serves which focus on Unidata software (currently, the list address is ud-list@courtfld.demon.co.uk) and Datatel products (moderated-subscription list; contact Datatel directly).

DOWNLOAD 4.0 February 1997

This is a brief introduction, intended to help you get started with DOWNLOAD.

For more extensive help, consult the documentation file DOWNLOAD.DOC which

came with the DOWNLOAD software.

Basic command line syntax

DOWNLOAD FileName field.names

More complete syntax

DOWNLOAD [BEGIN] [DICT] FileName

[[field.prefix] field.names [field.qualifiers]]

[SUBR('sname'[,arg1,etc]) [field.qualifiers]]

[LITERAL 'value' [field.qualifiers]]

[@variables]

[Record.IDs]

[Phrase from Dict]

[Phrase from VOC]

[Options]

Specifying data to include on the output

- Reference a data field or I-descriptor described in the current

dictionary.

- Reference a data field or I-descriptor described in the VOC file.

- Use the SECONDARY.FILE option to relate another file to this one.

This is like doing a TRANS (translate) to another file, but

without creating an i-descriptor.

- Use a literal value specified with the "LITERAL 'value'" clause.

- Return a value from a subroutine specified with the

"SUBR('sname')" clause.

- Use an "at" variable to return a system-defined value.

All of these items may also include qualifiers or prefixes which

further define how the value is to be downloaded. There are also

command options that may be used to change the default behavior

of DOWNLOAD.

Valid Field Clauses

DataFieldName [Field Qualifier(s)]

You may use any data field from the data file(s) specified

on your command line.

I-descriptorName [Field Qualifier(s)]

The dictionary item for a data field or an I-descriptor can come

from either the current dictionary (which can be changed with the

USING DICT option,) or the VOC file.

LITERAL 'constant value' [Field Qualifier(s)]

The default format and length for this type of value is the actual

length of the data, left-justified.

SUBR('subroutine.name' [, argument2]) [Field Qualifier(s)]

This clause can have from 1 to 10 arguments and functions similar

to the SUBR usage in I-descriptors. The subroutine should return

the value to be downloaded in the first argument. If the value

being returned is multi-valued, then the field qualifier

MULTI.VALUE should be added since the default is single-value.

The default format and length for this type of value is "30L".

@variable [Field Qualifier(s)]

You may select from any of the following variables:

@ACCOUNT host operating system path

@DATE system date (internal format) that

DOWNLOAD began running

@DAY day of the month that DOWNLOAD began running

@LOGNAME user's login name

@MONTH month of the year (numeric) that DOWNLOAD

began running

@SYSTEM.RETURN.CODE system return code at the start of execution

(usually, the number of records in the

active select list)

@TIME system time (internal format) that

DOWNLOAD began running

@YEAR year (four digit) that DOWNLOAD began running

Valid Command Options and Default Values

***** Option ***************************** ***** Default Value *****

FORMAT COMMA|DBF|DIF|FIXED|WP50|WP51 COMMA

QUOTE.CHAR quote.char "

COMMA.CHAR comma.char ,

EOR.CHAR end.of.record.char (null)

[NO.]DISPLAY.COUNT DISPLAY.COUNT

[NO.]PRINT.ERRORS PRINT.ERRORS

PRINT.LAYOUT Off (do not print layout)

LPTR Off (errors/layout on screen)

SAMPLE [SampleSize] Entire list or file

FROM SelectList# 0 (default select list)

USING [DICT] AlternateInfoFile DICT InfoFileName

FILE Type1File RecordName Off (display on screen)

OVERWRITING Off (do not overwrite)

UPCASE Off (do not change case)

RECORD.ORIENTATION HORIZONTAL | VERTICAL HORIZONTAL

RECORD.LENGTH fixed.size none (only valid with FIXED)

FIELD.GAP #blanksbetweencolumns none (only valid with FIXED)

NO.LINEFEED Off (LF between records)

HEADING ...record layout options... no report heading record

FOOTING ...record layout options... no report footing record

DETAIL ...detail layout options.... default is DETAIL

BY.EXP MVField1

WHEN MVField2 oper Field | Value(s)

DEFAULT field.qualifier new.default.value

Valid operators for WHEN option:

EQ, NE, GE, GT, LE, LT, LIKE, UNLIKE

Valid Field Prefixes:

BREAK.ON field.name

TOTAL field.name

AVERAGE field.name

MIN field.name

MAX field.name

Valid Field Qualifiers: -- Valid for these formats -- Default

FIXED COMMA WP DIF DBF Value

LINE LogicalLine# Yes Yes 1

[LENGTH] MaximumLength Yes Yes Yes Yes Yes

BEG.COL BegColumn# Yes 1

END.COL EndColumn# Yes n/a

COLUMNS BegColumn# EndColumn# Yes n/a

FMT FmtCode Yes Yes Yes Yes Yes dict value

CONV ConvCode Yes Yes Yes Yes Yes dict value

SINGLE.VALUE | MULTI.VALUE Yes Yes Yes Yes Yes Single

NUM.VALUES #Values Yes Yes Yes Yes Yes 1

NUM.VALUES ALL Y/N Yes Yes

(valid with FORMAT FIXED & vertical orientation)

(default = ALL for FORMAT WP50 or WP51)

MV.ORIENTATION VERTICAL | HORIZONTAL

Yes Yes Horizontal

DEFAULT.VALUE value Yes Yes Yes Yes Yes Null

Valid control heading and footing record layouts:

HEADING.ON break.field [...record layout...]

HEADING.ON break.field NONE

FOOTING.ON FINAL [...record layout...]

FOOTING.ON FINAL NONE

FOOTING.ON break.field [...record layout...]

FOOTING.ON break.field NONE

DET.SUP (show only heading/footing lines)

For each break field (each use of BREAK.ON), a default footing record

with the same layout as the detail record will be setup, as well as a

final control footing record (different from the report footing record).

This default may be disabled with the optional keyword NONE following

the FOOTING.ON phrase.

SECONDARY.FILE option

***** Option ********************** ***** Default Value *****

SECONDARY.FILE filename

[KEY primaryFileFieldName] @ID

[ALIAS text] filename

Examples

1. To create a "comma-quote" file of id numbers and names, try

statements like the following:

GET.LIST MAJOR.DONORS

DOWNLOAD PEOPLE ID.NO NAME FILE _HOLD_ DONOR.DAT

Sample output for a single record from above statement:

"1031567","Carnegie, Andrew"

2. To change the above to a WordPerfect merge file:

GET.LIST MAJOR.DONORS

DOWNLOAD PEOPLE ID.NO NAME FILE _HOLD_ DONOR.DAT FORMAT WP51

3. The following example creates a data file named GRAD1995.DAT in

the directory named &HOLD&. The file is in comma format with the

student's name, 2 lines (always) of address, city, state, zip, first and

second major, and all terms that have been transcripted or registered.

GET.LIST GRADS

DOWNLOAD STUDENTS \

NAME ADDRESS NUM.VALUES 2 CITY ST ZIP \

MAJOR NUM.VALUES 2 \

REG.TERMS WHEN REG.STATUS = 'T''''R' NUM.VALUES ALL \

FORMAT COMMA FILE &HOLD& GRAD1995.DAT

Sample output for a single record from above statement:

"Smith, John Q","250 North Main","","Columbus","OH","44444","ENG",

"BUS","89/FA","90/WI","90/SP","90/FA","91/WI","91/SP"

4. The following example uses a subroutine to call Datatel's GET.ADD system

with the default key of "AR" to determine the mailing name & address.

The rest of the example is the same as above.

GET.LIST GRADS.1991

DOWNLOAD STUDENTS \

SUBR('DL.GET.ADD','AR',5) MULTI.VALUE NUM.VALUES 5 \

MAJOR NUM.VALUES 2 \

REG.TERMS WHEN REG.STATUS = 'T''''R' NUM.VALUES ALL \

FORMAT COMMA FILE &HOLD& GRADUATES.1991.DATA

Sample output for a single record from above statement:

"Mr. John Smith","250 North Main","Columbus, OH 44444","","","ENG",

"BUS","89/FA","90/WI","90/SP","90/FA","91/WI","91/SP"

5. Examples using the SECONDARY.FILE option

The SECONDARY.FILE option lets you reference fields from other

files without creating a lot of i-descriptors.

DOWNLOAD PEOPLE SECONDARY.FILE STUDENTS KEY @ID

LAST FIRST STUDENTS->CLASS

references fields LAST and FIRST from the PEOPLE file and the field

CLASS from the students file (the same record key is used for both

files).

DOWNLOAD STUDENTS SECONDARY.FILE STUD.SCHEDS KEY LAST.SS.KEY \

NAME STUD.SCHEDS->COURSE NUM.VALUES ALL

references field NAME from the STUDENTS file and field COURSE

from the STUD.SCHEDS file. The record key for STUD.SCHEDS is

computed in field LAST.SS.KEY of the STUDENTS file.

DOWNLOAD STUDENTS \

SECONDARY.FILE PEOPLE KEY PARENT.ID ALIAS PGS \

SECONDARY.FILE PEOPLE KEY SPOUSE ALIAS SP \

NAME PARENT.ID PGS->NAME SPOUSE SP->NAME \

FORMAT FIXED FIELD.GAP 2

retrieves data from the PEOPLE file:

NAME is the person's name

PARENT.ID is the id number of the person's parent

PGS->NAME is the name of the parent (accessed via PARENT.ID)

SPOUSE is the id number of the person's spouse

SP->NAME is the name of the spouse (accessed via SPOUSE)

* This software was written at Cedarville College by Doug Sjoquist

* and modified by Dave Rotman. You may freely distribute this

* software, but this software is not to be sold by itself nor as

* part of any other software package. A current version of the

* software may be obtained via anonymous ftp from:

* ftp.cedarville.edu

* This software is made available on an "as-is" basis, with no

* warranty of any kind.

DOWNLOAD can produce output in a variety of formats as described below. Which format you use will likely be dictated by the application that will be reading your output file (spreadsheet, database, word processor, external service bureau, etc.). Here are the available formats:

This is the default output format for DOWNLOAD files. Non-numeric fields are surrounded by quotation marks on the output. All fields are separated by commas. This file format can be used by many programs, including word processors and spreadsheets.

This is the basic approach:

If you want the field names to appear in your output file, try this:

The DBF layout creates a file in native format for the database program dBASE. This layout can also be read by most other database packages (Paradox, FoxPro, etc.) and spreadsheet programs. You should use ".DBF" as part of your file name so that the database program can access the file.

DOWNLOAD CUSTOMERS NAME ZIP \

The field names from the Unidata file will become the database field names for the dBASE file. Note that dBASE field names are limited to 10 characters. Longer names will be truncated by DOWNLOAD. If the truncation results in a duplicate field name, DOWNLOAD will adjust field names until each one is unique.

Supported dBASE field types include character, numeric, and date. Fields which are not dates and not numeric will be stored as character fields.

dBASE files are limited to about 65,000 records per file.

DIF is an acronym for "data interchange format". This format was developed many years ago to facilitate exchange of data between different software packages. It is considered a "native" format for Excel and QuattroPro, so output files in DIF format can be read directly as spreadsheet files (no importing is necessary).

This is an example of creating a DIF file:

If you want the field names to be the first row in the spreadsheet, use this variation (with the HEADING option):

This layout forces the value of every field to be formatted to the same length. The actual length of each field is controlled by the dictionary FMT option, or you can override the FMT when you run DOWNLOAD. The FIXED layout is often used by service bureaus. This layout is also useful if the data will be read by languages like COBOL and FORTRAN. Some people like to use this layout for spreadsheet importing, though doing so requires a "parse" statement in the spreadsheet. (The COMMA or DIF formats are much easier to use with spreadsheets.)

This layout produces a "merge" file used by WordPerfect version 5.0. The output file can be used to generate letters, envelopes, etc. using the WordPerfect 5.0 merge operations.

Here is an example of generating a WP50 merge file:

This layout produces a "merge" file used by WordPerfect version 5.1 and later versions (including Windows versions). The output file can be used to generate letters, envelopes, etc. using the WordPerfect merge operations. This format is also suitable for use by Microsoft Word.

Output data for DOWNLOAD can be obtained by:

All of these items may also include qualifiers or prefixes which further define how the value is to be downloaded. There are also command options that may be used to change the default behavior of DOWNLOAD. This chapter illustrates field definition. The next chapter explains each of the available qualifiers, modifiers, and command options.

You may use any data field from the data file(s) specified on your command line. In the example below, "NAME" and "HOME.PHONE" are fields in file CUSTOMERS.

Literal values can be used wherever a regular data field would be expected. The default format and length for this type of value is the actual length of the data, left-justified. (The formatting can be overridden; see "CONV" and "FMT" options in the next chapter.)

These commands would produce a file to be used in generating labels or letters to parents of students:

Output records would look like this:

DOWNLOAD can retrieve data based on dictionary entries which have been placed in the VOC. To use this feature, create the item in DICT VOC, compile it, and then copy it to the VOC file. For example, suppose that you want to be able to include the length of the first data field in each output record. Create the DICT entry shown below and then follow the commands illustrated:

In this example, NAME is a field on the CUSTOMERS file and LEN.FIELD1 is an I-descriptor defined in the VOC file. Because LEN.FIELD1 is defined in the VOC, it can be used when DOWNLOADing any file.

You can DOWNLOAD using data from one file and control the DOWNLOAD using a dictionary of another file. See USING in the chapter "Syntax Guide" for more information.

DOWNLOAD has the capability of obtaining data from more than one file during processing. This is especially useful when a field in the first file is actually the key to a second file. See SECONDARY.FILE in the chapter "Syntax Guide" for more information.

Subroutines are invoked in a fashion similar to use of SUBR in I-descriptors. The subroutine should return the value to be downloaded in the first argument. If the value being returned is multi-valued, then the field qualifier MULTI.VALUE should be added since the default is single-value. The default format and length for this type of value is "30L".

Suppose that letters are to be generated to customers, indicating the balance due, the due date, and the rate of interest. The rate of interest depends on the customer rating and the size of the balance due. A subroutine named "GET.RATE" has been written to determine the interest rate. "GET.RATE" has the following definition:

This subroutine can be called directly by DOWNLOAD without having to create a virtual field:

Subroutines used by DOWNLOAD can have up to ten arguments.

"@" variables (pronounced "at variables") give you access to predefined system values as shown below. These variables can be used anywhere a regular dictionary item can be used. Here is a typical example using the "@DATE" variable:

Output would look like this:

The "@" variables can be used to generate a heading record required by some service bureaus. Suppose that the header must contain a record count and the date that the data file was created. The following commands could be used:

The first record in the output file would look like this (assuming that the DOWNLOAD occurred on 03/17/97 and the select list contained 156 records):

Detail records (after the heading record) would look like this:

Each of the "@" variables is described in the table below.