XTREE provides support for CSV format as a variation of file mode. To indicate that the input file is in CSV format, a new flag has been defined (see notes for 1146.0 for info on mapping the second flags word):
define XTF2_CSV = &h00000020
The are several variations on how columns and other attributes can be defined:
| • | If you do not specify a column definition string in COLDEF (XTF_COLDFX), XTREE will auto-define columns based on the number of fields in the first row of the CSV file. (Currently it sets them all to a generic string type, although perhaps in the future an attempt will be made to auto-detect numeric and date columns). In the typical case, XTREE assumes that the first row of the CSV file contains the column names, which will be used to build the XTREE column headers. If your CSV does not contain such a header row, you can specify XTR'HIDEHEADER=1 (in XTRCTL), in which case the first row will be treated as data, and there will be no column headers. |
| • | You may specify your own column definitions using the standard COLDEF syntax, either in the CSV file itself (in which case set XTF_FCOLDEF and XTF_COLDFX), or in the COLDEF parameter (in which case set XTF_COLDFX as you would normally). However, note that if any real columns are defined, then only those columns will be properly recognized during the load operation. You may choose to define only pseudo-columns (with cpos=0 and cwidth=0, i.e. "0~0~x~H~...~~") in order to define other attributes, such as colors, fonts, popup menus, etc., in which case the actual column definitions will be auto-generated as in the first case described above. Note that whenever any real columns are defined using the advanced coldef syntax (in either COLDEF or in the file), it is assumed that there is no other header row in the file. If there is, use the skip syntax described below to skip over it. |
| • | To skip over lines at the top of the file, you can begin your COLDEF parameter with a {-skipval} clause as documented with 5.1.1115 or http://www.microsabio.net/dist/51dev/doc/ashdevnotes/coldefenhancement.htm Note that when XTF2_CSV is specified, the {-skipval} clause does not require XTF_FCOLDEF (i.e. doesn't require any column definitions in the data file), and it doesn't have to be followed by "+" or "=" and a valid coldef definition. So in other words, you can skip lines by starting the COLDEF parameter with a {-skipval} clause, whether or not you use any other COLDEF features. |
Examples
Assume the CSV file looks like this and that we are specifying the XTF2_CSV flag in all cases:
Account, Name, Transaction, Date, Amount
12345,"Smith,Fred",08/15/09,999.12
12346,"Jones,Jane",08/16/09,100.00
If COLDEF="", and neither the XTF_COLDFX nor XTF_FCOLDEF flags are set, XTREE will recognize and display 4 columns of data, creating headers from the first line.
If XTR'HIDEHEADER=1, then it would treat the first line above as data, rather than as headers, and no header bar would be created.
If XTF_COLDFX is set and COLDEF="-1", then it would skip the first line. (When skipping, even if XTR'HIDEHEADER=0, there will still be no header bar unless the COLDEF string contains real column definitions.)
If COLDEF="0~0~x~H~HdrScale=200~~" (and XTF_COLDFX set), then the header scale will be set to 200, but the headers and column information will still be taking from the file (because the COLDEF does not define any real columns).
If COLDEF="-1=1~1~Acct#~#~~2~1~Name~S~~3~1~Date~D~4~1~Amount~#~~", then the first line of the data file will be skipped (due to the "-1=" at the start of the COLDEF, and the columns will be defined by the COLDEF string in the normal way, except that due to the XTF2_CSV flag, the cpos and cwidth parameters do not matter (but must be >0). By convention, we have specified cpos values which equate to the field number, but there is currently no way to rearrange the columns with this technique.
If we replaced the standard CSV header line at the top of the CSV file with a COLDEF string, as follows:
1~1~Acct#~#~~2~1~Name~S~~3~1~Date~D~4~1~Amount~#~~
12345,"Smith,Fred",08/15/09,999.12
12346,"Jones,Jane",08/16/09,100.00
Then we could specify XTF_FCOLDEF and XTF_COLDFX to have XTREE retrieve the column definitions from the file. Optionally, we could add to the column definitions by setting the COLDEF parameter to "+" followed by a legal set of column definitions (presumably pseudo-columns in this example).
