Overview

A datadef file contains a number of mandatory segments

Optional Segments:

COL_HEADER block - should be the first record in the file. Used to define the new-line charcter for generated field headers- the default being underscore ("_"). The backslash character "\" should not be used, as it is required for escaping certain other characters.

COL_HEADER "|"

SKIP_ROWS block - must be before any field definitions. defines the number of rows to ignore when identifying the header row.

SKIP_ROWS n [NO_HEADER]
Where n is the number of rows to skip, and the optional NO_HEADER flag means that row 1 contains data rather than headers.

ROWS_TO_READ block - must follow the SKIP_ROWS block, if present.
Limits the number of rows to read.

ROWS_TO_READ n
Where n is the number of rows to read.

BIS_FIELDS Block

{symbolic_name} {offset_length} {format} {separator} {field_name}

{symbolic_name} - the id to reference this field position in later mappings.

{offset_length} - e.g. 10-30, defines a 30-character field starting in column position 10. If the offset is omitted, the field starts immediately after the previous field, plus separator if any.

{format} - Justification, fill, type, so:

{separator} - the character used to terminate the field; specify as a quoted string, or use the keyword TAB. If no separator is specified, and explicit column definitions are not used, the fields will be contiguous - you can use this facility to create composite columns in BIS.

{field_name} - (Optional) Field name to insert in headers. Quoted string, delimited by underscores; multi-line headers may be created using the delimiters.

All the fields for both header and trailer lines are declared in the same BIS_FIELDS block.
The field definitions may overlap, as long as they are not used on the same line. For obvious reasons, the trailer line definitions will have no header text.

BIS_FIELDS

; Header line fields
    refnum 2-4 rzi TAB "REF_NUM"
    colour 7 lbs TAB "COLOUR"
Would create:
  *REF  COLOUR
  *NUM
  *====.=======.
Whereas:
BIS_FIELDS

; Header line fields
    refnum 2-4 rzi TAB "REF_NUM"
    colour 7 lbs TAB "_COLOUR"
Would create:
  *REF
  *NUM  COLOUR
  *====.=======.
Example BIS_FIELDS Block
  BIS_FIELDS
    ; Header line fields
    id 2-3 rzi TAB "ID"
    cost_per_item 10 rzf2 TAB "COST_" "PER_" "ITEM"
    short_desc 10 lbs TAB "SHORT_DESC"
    long_desc 50 lbs "" "LONG_DESC"
    ; Trailer line fields
    colour 2-10 lbs ""
    size   22-6 lbs ""
HEADER_LINE Block

This defines the mappings between Excel1 and BIS fields for the header line. It is formatted as in the following:

  HEADER_LINE TAB
    MAP id [id]
    MAP cost_per_item [price]
    MAP short_desc [description]
    MAP long_desc "Unavailable"

This defines a tab line where the BIS id field (as defined in the example BIS_FIELDS block above) is imported from a column named id in the Excel1 sheet,
cost_per_item and short_desc are imported from the Excel1 columns price and description respectively and long_desc is always set to the literal string "Unavailable". In place of named columns, you can use explicit Excel1 column designators, e.g. and equivalent block would be:

  HEADER_LINE TAB
    MAP id A
    MAP cost_per_item B
    MAP short_desc C
    MAP long_desc "Unavailable"

TRAILER_LINE Block

These blocks (if present) define the mappings between Excel1 and BIS fields for the report's trailer line(s).
The format is identical to that for header lines but with an extension that allows a variable number of trailer lines per header line, based on the data in the spreadsheet.
E.g. suppose that an item may be available in several colours and these appear in Excel1 as Colour1, Colour2, Colour3 and Colour4.

The following code will generate up to four '*' trailer lines, stopping when it reaches an Excel1 'colour' field which is empty.

TRAILER LINE "*" LOOP 1-4
  IMPORT_WHILE colour{#} <> ""
  MAP colour [colour{#}]
Alternatively, the following syntax will generate a trailer line for each colour which is not set to 'N/A':
TRAILER_LINE "*" LOOP 1-4
  IMPORT_IF colour{#} <> "N/A"
  MAP colour [colour{#}]
You can use full boolean expressions:
IMPORT_IF ([id] <> "") and (([amount] > 170) or not ([product] = "diplodocus"))
You can use IMPORT_WHILE and/or IMPORT_IF on header lines. (If you use both then IMPORT_WHILE must be first).

There's a 'special variable' - {!} - which is incremented every time IMPORT_IF rejects a header line and reset to 0 every time it accepts one.

Taken together, this means you can do this:

HEADER_LINE TAB
   IMPORT_WHILE {!} < 50
   IMPORT_IF [id] <> ""
etc... Which means skip lines where id is blank (e.g. blank lines inserted for readability) but if you get 50 consecutive such lines then assume that you've reached the end of the data and stop. Within a TRAILER_LINE block which includes a LOOP construct, the string '{#}' is replaced by the current value of the loop counter. So:
   MAP colour [colour{#}]
is equivalent to:
   MAP colour [colour1]
on the first pass through the above loop. The following syntax will put the current value of the loop counter as a literal into the specified BIS field:
   MAP counter {#}
The loop index itself can be either a range of numbers (e.g. 1-30) or a range of single letters (e.g. A-H).

It's worth noting that the # may be embedded: if there are fields in the Excel1 sheet such as PicAFile, PicBFile etc, you may specify:


TRAILER_LINE "*" LOOP A-D
   map type "F"
   map filenum {#}
   map file [Pic{#}File]

A 'simple' TRAILER_LINE block without the LOOP construct is formatted identically to a HEADER_LINE block:

TRAILER_LINE "*"
  MAP colour [colour]

Miscellaneous Notes

Output Field Length

There is no direct way of specifying the output record length - it is calculated as the length needed to hold the 'rightmost' BIS field. However, you can force the length to be at least a certain value by declaring a dummy field at an appropriate offset. E.g:

  DUMMY 550-1 lbs ""

Script Comments

Any characters in a script following a semicolon up to the end of that line are treated as a comment.

(1) "Excel" is a trade mark of the Microsoft Corporation