ODBC to File Export

ODBC to File Export Specification

Purpose

This page is used to define ODBC to File Export Specifications, which are used to extract data from files using an ODBC connection to a flat file format.

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

note

WidgetNote

If you exporting data from ODBC to SQL Server, use the ODBC to SQL Export Specification to define your exports.

Here's the typical workflow when creating a ODBC to File Export Specification:

Create an export and add one or more line items.
Use the Test command to verify the export is working correctly.
Once complete, an export is typically 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 clear the page making it ready for a new item.
	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.
	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.

Fields

Field Name

Description - Usage - Notes

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.
PIPE	Data values are delimited by the pipe character. This format is primarily used when debugging the output.
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.
Tilde	Data values are delimited by the tilde character.
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.

Last Update

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

Note

Enter a free form comment or note for this Export Specification. If a note is entered, a small icon will display on the Export Specification listing. Hovering your mouse over the icon on that listing will display about 40 characters of the note for quick reference.

ODBC Connection

Select an ODBC Connection from the list of available choices to indicate what type of database will be used for the ODBC connection. For example: Oracle 11g, MS Access, MS SQL. These must be defined using the ODBC Connection page.

Source Table

Enter the name the ODBC table to be used as the source of the data. To enter a list of related export files, click the drop-down icon

Note, if more than one table is entered, the field will be disabled (grayed out) and only the primary file name will be displayed, followed by the total number of files in brackets. For example. If the primary file is GL, and there are two additional related GL files, the field will be disabled and display as "GL [3]". Use the drop-down icon to edit the list of files. Refer to the Related Data Files Guide for more information on using this feature.

Where

Enter the selection criteria statement using the query syntax that is native to the source ODBC database. This is used to limit the data selected from the Source table. This field may also contain substitution tags.

Net Change Where

Enter any additional selection criteria using the query syntax that is native to the source ODBC database to provide additional data filtering before processing the file

Net Change with Checksum

Enable Net Change with Checksum to provide an additional level of filtering when records are exported. This feature is useful if the ODBC file contains attributes are not included in the export specification.

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. See help for ODBC to File Export Specification Line Item