Contributed by Jeff Pilant
This program is designed to assist in the generation of output for other programs. Its basic function is to take a temp table and create a file with the contents of the temp table in a convenient form. Output can be to the screen via a browse, or to a file in any of the following formats: plain text (*.TXT), comma-separated values (*.CSV), Excel spreadsheet (*.XLS), Sylk spreadsheet (*.SLK), XML file (*.XML), and Dictionary Definition file (*.DF).
The program is called as follows:
RUN tt-file.p(temp-table tt-name:handle, filename, switch)
Note: If the definition of the temp table does not add the rcode information tag, then tt-file.p cannot access the label or format of the temp table. It will instead use the field's name and the data-type default format.
The filename, if needed and not supplied, will be prompted for. This file will be used to store the results of the program.
The switch is used to provide options to the temp table expositor. Below is a list of all options that are currently available. Some of the options do not work with all export types. In that case they are ignored and can do no harm. The options must be provides as a list, separated by CHR(1). Each option is in the form of: option:value
LOGICALS
Some options require a logical value. A logical value may be given as 'true', 'false', 'yes' or 'no' or abbreviations of these. Only the first character is significant, so values of 't', 'f', 'y' or 'n' may also be used.
LISTS
Some options require a list of elements. Whenever a list is required it can be provided, separated with commas.
The Options - Quick overview
Here is a quick overview of all options:
• FileType: determine the output type of the tt expositor
• FieldList: provide a list of fields to be shown
• SkipList: provide a list of fields to skip in the output
• Append: indicates to append an existing file or not.
• ExcelAlert: indicates to complain if a file already exists
• ExcelVisible: indicates whether Excel is visible or not
• Labels: indicates whether Column labels should be displayed
• Title: title of the brows
• ReturnList: list of fields to be returned (values)
• FirstOfList: display only fields that are different from the previous line
• Grid: display lines between rows and columns as a grid.
• GridCharHor: define the character to use as the horizontal separator.
• GridCharVer: define the character to use as the vertical separator.
• GridCharCross: the character at the crossing of a horizontal and a vertical line
• GridInterval: the number of lines between two horizontal gridlines
• GridField: if this field changes, a gridline is displayed.
Download
* tt-file-13.zip for the core expositor;
* tt-file.doc for the Word documentation;
* tt-file_html.zip for HTML documentation; or
* all.zip to get everything plus additional work in process and notes files.
The minimum set of options for the tt expositor is empty, this means that none of the options is mandatory (and that's why they are called 'options'). The default values of all options are set so they match the former behaviour of the tt expositor. This means that you should be able to replace your current version of the tt expositor with this one without having to alter the calling of the procedure (see also 'disclaimer').
The following options are supported:
FileType option list
Short description : determine the output type of the tt expositor
Valid in modes : All
Default value : TXT
This corresponds to the old 'switch'. To maintain backward compatibility this is the only option that needs not be in the form 'option:value' but can be as well in the form 'value'. Valid value is one of the character values defined in tt-file.i. These are: txt, xls, csv, xls, slk or df. If left blank, the type will be derived from the filename (even if prompted). If the filename does not have a valid type, it will be assumed a text file.
Note: Excel spreadsheets require access to the desktop, so they cannot be run in batch mode.
Note: XML files do not like special characters in your fieldnames so these are filtered out. In the examples you will see the fieldname 'Track#' to appear as 'Track'. This may cause some problems if your temptable holds both 'Track#' and 'Track'.
The Sylk spreadsheet format was added to allow generation of spreadsheets in batch programs. While the format is outdated, it is still accepted by Excel. The Sylk format allows some special formatting options in this routine for date and time values. Time can be formatted as "HH:MM", "HH:MM AM", "HH:MM:SS", "HH:MM:SS AM". Dates can be formatted as "MM/DD/YY", "DD-MMM-YY", "Month DD, YYYY", "YYMMDD", "YYYYMMDD". To assist you, the following preprocessor constants are defined: tt-fmt-hm, tt-fmt-hm-a, tt-fmt-hms, tt-fmt-hms-a, tt-fmt-mdy, tt-fmt-dmy, tt-fmt-md-y, tt-fmt-ymd, and tt-fmt-cymd. You can use them like this: define variable date-var as date format {&tt-fmt-dmy}. define variable time-var as integer format {&tt-fmt-hms}. If you are showing a number, and you use the format chars "9" or "Z", the format results in forcing that many places. For example: define variable zip-code as integer format "99999". define variable four-dig as integer format "Z,ZZ9".
Note: There are lots of notes about the Sylk format in the code for those that are interested.
FieldList option list
Short description : provide a list of fields to be shown
Valid in modes : TXT XLS CSV XLS SLK DF XML
Default value : *
The FieldList is defined as a series of field names within the temp table, each seperated with a comma. The default values are defined to select all fields of the temp table. If you provide a list of fields in the FieldList, then only those fields will be shown.
If you provide lists for both FieldList and SkipList, then only those fields in the FieldList that are not in the SkipList will be shown. Example: FieldList:cust-name,cust-num,sales-rep SkipList:sales-rep Shown Fields are: cust-name and cust-num
Note: If you use FieldList for DF output and you skip fields that participate in an index, no error is reported, but the produced DF file is corrupt, since there will be an index reference to the field you skipped. Use with care!
SkipList option list
Short description : provide a list of fields to skip in the output
Valid in modes : TXT XLS CSV XLS SLK DF XML
Default value : (empty)
The SkipList is defined as a series of field names within the temp table, each seperated with a comma. The default values are defined to select all fields of the temp table. If you provide a list of fields in the SkipList, then those fields will not be shown.
If you provide lists for both FieldList and SkipList, then only those fields in the FieldList that are not in the SkipList will be shown. Example: FieldList:cust-name,cust-num,sales-rep SkipList:sales-rep Shown Fields are: cust-name and cust-num
Note: If you use SkipList for DF output and you skip fields that participate in an index, no error is reported, but the produced DF file is corrupt, since there will be an index reference to the field you skipped. Use with care!
Append option list
Short description : indicates to append an existing file or not.
Valid in modes : TXT CSV DF XML
Default value : false
You can specify that you want to append to an existing file. If the file does not exist, this setting has no effect.
ExcelAlert option list
Short description : indicates to complain if a file already exists.
Valid in modes : XLS
Default value : true
When you export to XLS, try to save the file and it already exists, Excel asks you wether you would like to overwrite this file or not. By specifying 'ExcelAlert:false' you can override this. If a file already exists, Excel will no longer complain, but overwrite the existing file.
Note: ExcelVisible and ExcelAlert work independent of each other. When you specify 'ExcelVisible:no' and 'ExcelAlert:yes' then, when Excel tries to save the file you still get feedback from Excel if the file already exists.
ExcelVisible option list
Short description : indicates wether Excel is visible or not.
Valid in modes : XLS
Default value : true
When Excel does it's thing, you may choose not to see it using the setting 'ExcelVisible:false'. When set, all actions will be completed silently.
Note: ExcelVisible and ExcelAlert work independent of each other. When you specify 'ExcelVisible:no' and 'ExcelAlert:yes' then, when Excel tries to save the file you still get feedback from Excel if the file already exists.
Labels option list
Short description : indicates wether Column labels should be displayed.
Valid in modes : TXT
Default value : true
If you do not want the labels to appear above the text, you can specify 'labels:false'. This is especially useful when you want to append to an already existing report.
Title option list
Short description : Title of the browse.
Valid in modes : BRS
Default value : 'tt-browse'
This title is used in the title bar of the browse.
ReturnList option list
Short description : List of fields to be returned (values).
Valid in modes : BRS
Default value : (empty)
The values of the fields you list here get returned, separated by CHR(1) chars. The string can be retrieved by accessing the RETURN-VALUE after the invokation of tt-file.p.
FirstOfList option list
Short description : display only fields that are different from the previous line.
Valid in modes : TXT CSV XLS SLK
Default value : (empty)
This option is a list of fields you want to be printed only when they change value. This gives you the possibility to display blanks instead of a field if it is the same as the previous record. In the good old progress 4Gl you would solve this with a construction like:
for each OrderLine break by OrderNum.CustomerNum
by OrderLine.OrderNum
by OrderLine.OrderLine:
display
OrderLine.CustomerNum when first-of OrderLine.CustomerNum
OrderLine.OrderNum when first-of OrderLine.OrderNum
OrderLine.OrderLine
OrderLine.ItemNum.
end.
With the tt expositor you would specify 'FirstOfList:CustomerNum,OrderNum'.
Grid option list
Short description : display lines between rows and columns as a grid.
Valid in modes : TXT
Default value : false
You can specify that you want a horizontal or vertical grid to appear. This is disabled by default. You can specify a horizontal grid with 'grid:hor', a vertical grid with 'grid:ver' or set them both with 'grid:true'. When enabled, a line, consisting of '-' chars is printed after each line and the column separator ' | ' is printed after each column.
Note: using a default grid enlarges your output. It makes the output both longer (twice as long) and wider (2 extra chars for each column).
You can specify which characters to use and after how many lines a horizontal separater should appear using 'GridCharHor', 'GridCharVer', 'GridCharCross', 'GridInterval' and 'GridField'. Please take a closer look at the grid examples below.
GridCharHor option list
Short description : define the character to use as the horizontal separator.
Valid in modes : TXT
Default value : -
This is the character to use as the horizontal separator. You can specify more then a single character. When you do, this affects the wideness of the report since the character printed between two columns is Space + CrossChar + Space. If you specify a more-than-one-char GridCharHor (say '--=='), the Spaces in the above formula are expanded to match the number of chars in your GridCharHor so in the above example the column separator will become ' + '.
GridCharVer option list
Short description : define the character to use as the vertical separator.
Valid in modes : TXT
Default value : |
You can specify a separator longer than 1 char. If you do, make 'GridCharCross' and 'GridCharVer' both the same length for proper display.
GridCharCross option list
Short description : the character at the crossing of a hor. and a ver. line.
Valid in modes : TXT
Default value : +
You can specify a separator longer than 1 char. If you do, make 'GridCharCross' and 'GridCharVer' both the same length for proper display.
GridInterval option list
Short description : the number of lines between two horizontal gridlines
Valid in modes : TXT
Default value : 1
This determines how often a gridline is displayed. You can use this to make your output more readable. If you set this to 0, no horizontal gridlines are displayed.
GridField option list
Short description : if this field changes, a gridline is displayed.
Valid in modes : TXT
Default value : (empty)
If you want a horizontal gridline to appear only when a certain field changes its value. You can specify only one field. A good value would be the last field you specified in the 'FirstOfList' option. See the examples below.
AttributeList option list
Short description : list of fields used as XML record identifiers.
Valid in modes : TXT
Default value : (empty)
If you want the record to be identifyable you can specify one or more attributes for the record. These show up in the XML document in the tag that represents your temp-table (see Example 10).
Example setup
The examples use a temp-table holding a collection of MP3 files. The temp-table consist of 4 fields: the artist, the album, the tracknumber and the title of the track. See the code example for the definition.
Playing code
If you cut-and-paste this code into your editor you can replay the examples given and experiment with it.
define temp-table song RCODE-INFORMATION
field Artist as character label 'Artist' format 'x(12)'
field Album as character label 'Album' format 'x(12)'
field Track# as integer label 'Track#' format '>>9'
field TrackName as character label 'TrackName' format 'x(40)' .
define variable cOptions as character no-undo.
input from song.d.
repeat transaction:
create song.
import song.
end.
input close.
run tt-file.p ( input temp-table song:handle,
input 'temp.txt',
input cOptions ).
os-command no-wait start temp.txt.
You may need to change paths to temp.txt, song.d or tt-file.p. For each example the option string used is given.
Playing data
The data in the temp-table is as follows:
"Pink Floyd" "The Wall" 1 "In the flesh"
"Pink Floyd" "The Wall" 2 "Thin ice"
"Pink Floyd" "The Wall" 3 "Another brick in the wall"
"Pink Floyd" "The Wall" 4 "The Happiest Days Of Our Lives"
"Pink Floyd" "Meddle" 1 "One of these days"
"Pink Floyd" "Meddle" 2 "A pillow of winds"
"Pink Floyd" "Meddle" 3 "Fearless"
"Pink Floyd" "Meddle" 4 "San Tropez"
"Pink Floyd" "Meddle" 5 "Seamus"
"Pink Floyd" "Meddle" 6 "Echoes"
Save this data in a separate .d file named song.d. It is imported in the example program.
Example Overview
Below you will find the following examples:
* Display the contents 'as is'
* Use a default grid
* Use a grid for groups of 3 records.
* Make groups of records for each album
* Change the appearance of the gridlines.
* Suppress a field
* Suppress the header
* Over the top
* XML Output
* XML Output using attributes
Example 1: Display the contents 'as is'
- The TT expositor is called with only the filetype option. Note that the result would be the same if we did not specify anything at all as an option. This is left as an exercise for the reader ;-)
cOptions = 'FileType:txt'.
The following two lines are functionally equivalent to the former:
cOptions = ''.
cOptions = 'txt'.
output:
Artist Album Track# TrackName
Pink Floyd The Wall 1 In the flesh
Pink Floyd The Wall 2 Thin ice
Pink Floyd The Wall 3 Another brick in the wall
Pink Floyd The Wall 4 The Happiest Days Of Our Lives
Pink Floyd Meddle 1 One of these days
Pink Floyd Meddle 2 A pillow of winds
Pink Floyd Meddle 3 Fearless
Pink Floyd Meddle 4 San Tropez
Pink Floyd Meddle 5 Seamus
Pink Floyd Meddle 6 Echoes
Example 2: Use a default grid
- Display a 'grid' for text file output. Both a vertical and a horizontal grid is displayed (optional). You can specify that you want a horizontal grid, a vertical grid or both using the 'Grid' option. Use 'Grid:hor', for horizontal, 'Grid:ver' for vertical and 'Grid:yes' for both.
cOptions = 'FileType:txt' + chr(1) + 'Grid:yes'.
output:
Artist | Album | Track# | TrackName -------------+--------------+--------+----------------------------------------- Pink Floyd | The Wall | 1 | In the flesh -------------+--------------+--------+----------------------------------------- Pink Floyd | The Wall | 2 | Thin ice -------------+--------------+--------+----------------------------------------- Pink Floyd | The Wall | 3 | Another brick in the wall -------------+--------------+--------+----------------------------------------- Pink Floyd | The Wall | 4 | The Happiest Days Of Our Lives -------------+--------------+--------+----------------------------------------- Pink Floyd | Meddle | 1 | One of these days -------------+--------------+--------+----------------------------------------- Pink Floyd | Meddle | 2 | A pillow of winds -------------+--------------+--------+----------------------------------------- Pink Floyd | Meddle | 3 | Fearless -------------+--------------+--------+----------------------------------------- Pink Floyd | Meddle | 4 | San Tropez -------------+--------------+--------+----------------------------------------- Pink Floyd | Meddle | 5 | Seamus -------------+--------------+--------+----------------------------------------- Pink Floyd | Meddle | 6 | Echoes -------------+--------------+--------+-----------------------------------------
Example 3: Use a grid for groups of 3 records.
- The vertical grid can be told to appear after each lines using the GridInterval option: 'GridInterval:3'. The default value is 1. Specifying 0 as the interval disables the vertical grid.
cOptions = 'FileType:txt' + chr(1) + 'Grid:yes'
+ chr(1) + 'GridInterval:3'.
output:
Artist | Album | Track# | TrackName -------------+--------------+--------+----------------------------------------- Pink Floyd | The Wall | 1 | In the flesh Pink Floyd | The Wall | 2 | Thin ice Pink Floyd | The Wall | 3 | Another brick in the wall -------------+--------------+--------+----------------------------------------- Pink Floyd | The Wall | 4 | The Happiest Days Of Our Lives Pink Floyd | Meddle | 1 | One of these days Pink Floyd | Meddle | 2 | A pillow of winds -------------+--------------+--------+----------------------------------------- Pink Floyd | Meddle | 3 | Fearless Pink Floyd | Meddle | 4 | San Tropez Pink Floyd | Meddle | 5 | Seamus -------------+--------------+--------+----------------------------------------- Pink Floyd | Meddle | 6 | Echoes
Example 4: Make groups of records for each album
- We are going to suppress the artist and the album as long as they don't change. We also want a gridline to appear when the album changes.
cOptions = 'FileType:txt' + chr(1) + 'Grid:yes'
+ chr(1) + 'FirstOfList:Artist,Album'
+ chr(1) + 'GridField:Album'.
output:
Artist | Album | Track# | TrackName
-------------+--------------+--------+-----------------------------------------
Pink Floyd | The Wall | 1 | In the flesh
| | 2 | Thin ice
| | 3 | Another brick in the wall
| | 4 | The Happiest Days Of Our Lives
-------------+--------------+--------+-----------------------------------------
| Meddle | 1 | One of these days
| | 2 | A pillow of winds
| | 3 | Fearless
| | 4 | San Tropez
| | 5 | Seamus
| | 6 | Echoes
-------------+--------------+--------+-----------------------------------------
Example 5: Change the appearance of the gridlines.
- We are going to use different characters for the gridlines. We want to use '.' for horizontal lines and ':' for both vertical lines and crossings of hor and ver lines.
cOptions = 'FileType:txt' + chr(1) + 'Grid:yes'
+ chr(1) + 'FirstOfList:Artist,Album'
+ chr(1) + 'GridField:Album'
+ chr(1) + 'GridCharHor:.'
+ chr(1) + 'GridCharVer::'
+ chr(1) + 'GridCharCross::'.
output:
Artist : Album : Track# : TrackName
.............:..............:........:.........................................
Pink Floyd : The Wall : 1 : In the flesh
: : 2 : Thin ice
: : 3 : Another brick in the wall
: : 4 : The Happiest Days Of Our Lives
.............:..............:........:.........................................
: Meddle : 1 : One of these days
: : 2 : A pillow of winds
: : 3 : Fearless
: : 4 : San Tropez
: : 5 : Seamus
: : 6 : Echoes
.............:..............:........:.........................................
Example 6: Suppress a field
- We are not interested in the track number, so we want to suppress this field.
cOptions = 'FileType:txt' + chr(1) + 'Grid:yes'
+ chr(1) + 'FirstOfList:Artist,Album'
+ chr(1) + 'GridField:Album'
+ chr(1) + 'GridCharHor:.'
+ chr(1) + 'GridCharVer::'
+ chr(1) + 'GridCharCross::'
+ chr(1) + 'SkipList:Track#'.
output:
Artist : Album : TrackName
.............:..............:.........................................
Pink Floyd : The Wall : In the flesh
: : Thin ice
: : Another brick in the wall
: : The Happiest Days Of Our Lives
.............:..............:.........................................
: Meddle : One of these days
: : A pillow of winds
: : Fearless
: : San Tropez
: : Seamus
: : Echoes
.............:..............:.........................................
Example 7: Suppress the header
- We are not interested in the header either so we want to suppress this field. Secondly, this strange grid settings are annoying us so we also get rid of them.
cOptions = 'FileType:txt' + chr(1) + 'Grid:yes'
+ chr(1) + 'FirstOfList:Artist,Album'
+ chr(1) + 'GridField:Album'
+ chr(1) + 'SkipList:Track#'
+ chr(1) + 'Labels:no'.
output:
Pink Floyd | The Wall | In the flesh
| | Thin ice
| | Another brick in the wall
| | The Happiest Days Of Our Lives
-------------+--------------+-----------------------------------------
| Meddle | One of these days
| | A pillow of winds
| | Fearless
| | San Tropez
| | Seamus
| | Echoes
-------------+--------------+-----------------------------------------
Example 8: Over the top
- If you are really enthusiastic about al these new features, one warning: don't overdo it, or you may find yourself producing output which is somewhat over the top, if you catch my drift ...
cOptions = 'FileType:txt' + chr(1) + 'Grid:yes'
+ chr(1) + 'FirstOfList:Artist,Album'
+ chr(1) + 'GridField:Album'
+ chr(1) + 'GridCharHor:-='
+ chr(1) + 'GridCharVer:| '
+ chr(1) + 'GridCharCross:+='.
output:
Artist | Album | Track# | TrackName
-=-=-=-=-=-=-=+=-=-=-=-=-=-=-=-=+=-=-=-=-=-=+=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Pink Floyd | The Wall | 1 | In the flesh
| | 2 | Thin ice
| | 3 | Another brick in the wall
| | 4 | The Happiest Days Of Our Lives
-=-=-=-=-=-=-=+=-=-=-=-=-=-=-=-=+=-=-=-=-=-=+=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
| Meddle | 1 | One of these days
| | 2 | A pillow of winds
| | 3 | Fearless
| | 4 | San Tropez
| | 5 | Seamus
| | 6 | Echoes
-=-=-=-=-=-=-=+=-=-=-=-=-=-=-=-=+=-=-=-=-=-=+=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Example 9: XML Output
- If you want to produce an XML file containing your data, you can use the XML filetype.
cOptions = 'FileType:xml' + chr(1).
output:
<?xml version="1.0" ?>
-
-
Pink Floyd
The Wall
1
In the flesh
-
Pink Floyd
The Wall
2
Thin ice
-
Pink Floyd
The Wall
3
Another brick in the wall
(snip)
Example 10: XML Output using attributes
- You can specify which fields identify a record using the AttributeList.
cOptions = 'FileType:xml' + chr(1) + 'AttributeList:Artist,Album,Track#'.
output:
< ?xml version="1.0" ?>
- < RecordSet>
- < song Album="The Wall" Track="1" Artist="Pink Floyd">
In the flesh
< /song>
- < song Album="The Wall" Track="2" Artist="Pink Floyd">
Thin ice
< /song>
- < song Album="The Wall" Track="3" Artist="Pink Floyd">
< TrackName>Another brick in the wall
< /song>
(snip)
< /RecordSet>
Contributers
Who Full name
---- ----------------- ------------------------
JTP Jeff Pilant Jeff.Pilant@us.abb.com
PT Patrick Tingen p.tingen@vcd.nl
Revision History:
Ver Who Date Description
---- ---- ------------ --------------------------------------------------
1.0 JTP 06-Mar-2002 Created (TXT, XLS)
JTP 06-Mar-2002 Added (CSV), prompt for filename
JTP ??-???-2002 Added (BRS)
JTP 11-Apr-2002 Added AutoFit to Excel output
JTP 23-May-2002 Fixed ExcelColumn()
JTP 08-May-2002 Changed default column width for excel to a
function
Added Title Row code to Excel output
JTP 13-Sep-2002 Added code to fix Title Row to always be row 1
JTP 10-Oct-2002 Added ability to do csv, txt, or xls files if
type not given
JTP 16-Jul-2003 Added Sylk file type
1.1 JTP 08-Sep-2003 Fixup Sylk date format for dates before 1/1/1900
Added df file type [From code found at:
http://www.v9stuff.com/dynexport.htm
thanks to Tony Lavinio and Peter van Dam]
PT 22-Oct-2003 Added ,"character" to substring and length
functions to support multi-byte codepages.
[JTP: Adds compatability for other Code Pages]
1.2 PT 31-Oct-2003 Changed parameter ft to cOptions to provide
more options for the requested behaviour.
This is backward compatible.
Added options: FileType, FieldList, SkipList,
ExcelAlert and ExcelVisible
JTP 01-Nov-2003 Folded in PT's changes and rewrote the column
autofit portion of the excel code.
PT 02-Dec-2003 Added options: Grid, GridCharHor, GridCharVer,
GridCharCross, GridInterval, GridField
Added options: Append, Labels, FirstOfList, Title
ReturnList
Added HTML documentation.
Added XML mode.
Moved code and vars to internal procedures.
PT 12-Jan-2004 Use the name of the field as the XML tag, not the label
since the label is more likely to hold undesired chars.
Added some comments to procedures and functions.
PT 09-Feb-2004 Added support for writing array fields to XML
Disclaimer
The authors of this software tried to deliver a decent piece of work. However, this software is provided 'as is', so use it - or do not use it - at your own risk. The authors accept no liability whatsoever on the use of this software. If you find a bug or you do have a brilliant idea to increase the functionality, feel free to contact Jeff Pilant (see 'Contribitors').