Export Specifications (kourier_export_def.aspx)

Purpose

To define export definitions used to extract data from U2 in various formats such as tab-delimited, fixed, XML, or HTML. An export specification has two parts: selection and export. The selection portion of an export allows you to specify any selection criteria to apply to the data before the data export file is created. The export portion lists the fields to be included in the data export file, the column headings to use, and any data conversion required.

An export is normally processed in a Service using the command KMK.EXPORT.

To test an export, you may click the Test button from the tool bar. You will be asked to enter the number of records to sample. When the export completes, a dialog appears indicating the number or records processed and the number of rows created.  Click the OK button to view the export results or click the Cancel button to return to the Export Specification page.

To create the DDL from an export specification, you may click the DDL button from the tool bar.  You will be asked to enter the name of the SQL table.  Once the DDL has been created a dialog appears allowing you to view the DDL.  This feature is useful when you need to only create the DDL for an single export instead of all of the exports associated with a master file.

File/Key Field

KT_EXPORTS/Export Name

Prerequisites

If an export contains user-defined literals, they should be entered using Substitution Literals.

Cautions

None

Fields

Name Enter a unique name for the export. By convention, exports are named using an Integration ID such as KS for KommerceServer, followed by an underscore and a string that describes its purpose (e.g., KS_CONTACTS). 
Title Enter a short descriptive name for this export. This name is displayed in on-line lookups.
Export Format Enter the export format. If the export verb is KEXPORT, the possible choices are shown below.  Other export verbs (normally user-written BASIC programs)  may or may not support these formats.
Format Description
TAB Data values are delimited by the tab character.  Use this format to generate files that can be automatically loaded in the Excel or other PC applications.
FIXED Data values are of a fixed length.  Each column in the export will be padded with blank characters to the length of the column.
XML Data values are enclosed in XML tags.
HTML Data values are enclosed in standard HTML tags.
ASCII Data is exported in flat file format where each attribute appears on a separate line.  Value marks and sub-value marks are embedded in each line.  This format is similar to wIntegrate's ASCII format.
CSV Data values are delimited by the comma character.  If a data value is non-numeric it is enclosed by the double quote character.  If a data value contains the double quote character it will be duplicated.
PIPE Data values are delimited by the pipe character.  This format is often used when interfacing with Oracle systems.
TILDE Data values are delimited by the tilde character.
Enable Net Change When net change is enabled any add, change, or delete of a record in the file referenced in the Export Data File field will be tracked.  Kourier tracks these changes by creating an RDBMS index named KCL_INDEX. A subfile is created under the KT_CHGLOG file using the same name as the file referenced in the Export Data File field with the string _LOG appended to that file name.  Only files that are defined locally to the current account may have net change enabled.  You may also enable net change with checksum, which provides an additional level of filtering when records are exported.  This feature is useful if your application writes records to the file referenced but those changes may not be of interest to the target application.
Select Verb

Enter a valid verb to perform the selection, such as SELECT or SSELECT.

The export processor first processes the selection portion of the screen, and then passes the selected items to the export section. The selection portion of the screen can be used to limit the number of records exported.
Select Data File Enter the name of the data file to use to select data records for exporting.  This field may also contain substitution tags. 
Select Using File

Enter the name of the file that contains the dictionary items referenced in the select statement.

To use the dictionary file with the same name as the data file, leave this field empty. For example, if you enter CUSTOMER as the data file and do not enter a using file, the CUSTOMER dictionary file is used.

To specify an alternate dictionary file, enter DICT and the file name.

To specify a data file that contains dictionary items you want to use, enter the file name.
Select Statement

Enter the selection criteria statement using standard U2 query syntax.

For example, if you want to select all of the CUSTOMER records with PHONE 619], enter WITH PHONE 619] in the Statement field. This field may also contain substitution tags. 
Export Verb Enter the name of the export verb. In most cases this will be the Kourier program called KEXPORT, although it is possible to write a BASIC program to generate the dataset to be exported.
Export Data File Enter the name of the data file to use to export data records. This field may also contain substitution tags. 
Export Using File

Enter the name of the file that contains the dictionary items referenced in the export statement.

To use the dictionary file with the same name as the export data file, leave this field empty. For example, if you enter CUSTOMER as the data file and do not enter a using file, the CUSTOMER dictionary file is used.

To specify an alternate dictionary file, enter DICT and the file name.

To specify a data file that contains dictionary items you want to use, enter the file name.
Export Options Enter any valid export options.
Option Usage
COL.HDR.SUPP Use this option to suppress columnar headings. Syntax is:  COL.HDR.SUPP
EXPORT-REPEAT-VALUES If this option is specified, Kourier will repeat the last non-null value or sub-value of a field. Syntax is:  EXPORT-REPEAT-VALUES
EXPORT-STAMP Used by the KEXPORT command to specify the attribute which should receive a time/date stamp when a record is exported. Syntax is:  EXPORT-STAMP "amc".
EXPORT-SUBNAME Used by the KEXPORT command to specify the name of the export subroutine associated to the export. Syntax is:  EXPORT-SUBNAME "subroutinename".
XML-FORM-TAG When creating an XML export, this option indicates the form tag of the XML document. Syntax is:  XML-FORM-TAG "elementname".
XML-ROOT-TAG When creating an XML export, this option indicates the root element tag of the XML document. Syntax is:  XML-ROOT-TAG "elementname".  Default value is: Kourier file=export_data_file
XML-TRANS-TAG When creating an XML export, this option indicates the transaction tag of the XML document. Syntax is:  XML-TRANS-TAG "elementname".  Default value is: Trans id=@ID
Export Verb Requires Select List If enabled, then an active select list (or an empty selection criteria) must be present before the export statement will be executed.  If disabled, the export statement should be processes even if an active select list is not present. 

Line Item Fields

Line The order sequence this field should appear in the export.
Field ID You can specify D-, I- and V-type dictionary items. In addition, it is possible to reference a field simply by entering its attribute number. This feature is quite useful when exporting items from a temporary file.
Column Heading Enter the column heading for this exported field.  Depending upon the export format, the column heading is used in different ways.
Format Heading Usage
TAB The column heading will be included as the first line of the tab-delimited file. To suppress the column heading for all export fields, use the command keyword COL.HDR.SUPP.
FIXED The column heading will be included as the first line of the tab-delimited file. To suppress the column heading for all export fields, use the command keyword COL.HDR.SUPP.
XML The column heading is used as the XML tag for the field. All redundant white space is automatically stripped from the column heading, and any remaining spaces are converted to the underscore character.
HTML The column heading will be included as the first line of the HTML table. To suppress the column heading for all export fields, use the command keyword COL.HDR.SUPP
If the DDL button is used, the column heading will be used for the SQL table column names.
Conversion Enter any valid dictionary conversion code, substitution parameter, or a literal string.  This code will override the conversion code in the dictionary item. Multiple conversion may be seperated by the | character.
Format This field is only used when an export is generated in FIXED format.  It is used to over-ride the length of the output and the justification of the output.
Association Depending upon the export format or the command used, this column is used in different ways.

If the export is generated in XML format.  It is used to group repeating values or line items by including the association name as a tag in the XML document.  Up to two levels of tags can be specified (corresponding to associated values and sub-values) by seperating them by the | character. The association name may not contain any white space.

If the DDL command is used, this field indicates the primary keys (by using the special association of PK) to create in the DDL that is created from the export.