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]
DUMMY 550-1 lbs ""