Standard Export Specification

Standard Export Specification (kourier_export_def.aspx)

Purpose

This page is used to define standard export definitions which are used to extract data from U2 in various formats such as: tab-delimited, fixed, XML, or HTML. Note: if you are exporting data from U2 to SQL server, use the SQL Export Specification to define your exports to SQL tables.

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.

Available Toolbar Commands

	Click the New icon in the toolbar to save the current export specification and then reset the page for new data entry.
	Click the Save icon in the toolbar to save the current export specification and return to the export specifications listing.
	Click the Save As icon in the toolbar to save the current export specification to a new name. You will be prompted to enter a new name. Click OK to save the export to a new name and then continue editing the export. Click Cancel to return to the existing export without saving.
	Click the Delete icon in the toolbar to delete the current export specification. You will be prompted with a "Are you sure you want to delete?" dialog. Clicking Yes will permanently delete the export and then return to the export specifications listing. Click Cancel to return to the existing export without deleting.
	Click the Test icon in the toolbar to test an export. Testing an export will perform a sample data extract using all of the information entered on the export specification and then outputs the results to the screen where it can be viewed and validated. Typically a test is performed to ensure that the correct data fields are being extracted and all data conversions, formats, and formulas are working correctly. After clicking the Test icon, you will see a dialog requesting additional information: Sample Number of records: Defaults to 100. This number determines how many records are processed and exported to the screen. Record IDs to export: If you want to test individual records, enter one or more item ids. Entering a value here overrides the sample number of records. View test results in native MultiValue Format: click the checkbox to view the exported data in a detailed data view which shows the data for each field in its native MultiValue format (including value and sub-value delimiters) and without processing it through formats, conversions, etc. This is very useful if you need to see exactly what the data you are exporting looks like which may help you understand why export format, conversions, or formulas are not working. Click the OK button to view the export results or click the Cancel button to return to the Export Specification page.
	To see the runtime statistics for an individual export, click the Statistics icon from the toolbar. This will take you to the Export Statistics listing which displays all export statistics that have been captured for the export.
	Click the Help balloon to open the help for this page.

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.

Last Update

Display only field, shows the last date/time that the export specification was saved.

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.

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.

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 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.

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

Line Item Fields

Click the add button in the line item section command bar to add a new line item field or click the Line number to edit an existing line item. For more information on each individual field, see the help for Standard Export Specification Line Item