Open topic with navigation
ODBC to File Export Specification (from Table)
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.
If you exporting data from ODBC to SQL Server, use the ODBC to SQL Export Specification (from Table) 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.
You can convert an ODBC to File from Table Export to an ODBC to File from Statement type export using the Convert to Statement button.
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 and Stay icon in the toolbar to save the current export specification and continue working.
|
|
|
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.
If you are configuring a Windows ODBC connection, refer to Configuring a Windows ODBC Connection for Use with Kourier.
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.
|
|
|
|
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.
If your selection criteria uses a dynamic substitution tag that will be specified in the Command field in a Service by including the HEADERS keyword, you must specify a default value for the substitution tag so that the Test capability works correctly. For example, if your HEADERS expression was HEADERS "StartDate=01-01-15" then your substitution expression in the export should be {%StartDate(S;*;'01-01-14'}.
For SQL ODBC sources, Kourier will automatically add the NOLOCK compiler hint to the Where clause.
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. This query will likely include a last change date column. The following run-time substitutions tags are available:
|
|
|
|
last_export_date
|
Inserts the last date the export successfully ran in external format (e.g. 04 DEC 2014).
|
|
last_export_time
|
Inserts the last time the export successfully ran in external format (e.g. 12:42:13).
|
|
last_export_datetime
|
Inserts the last date and time the export successfully ran in external format (e.g. 04 DEC 2014 12:42:14).
|
|
|
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.
|
|
|
|
COL.HDR.SUPP
|
Use this option to suppress columnar headings. Syntax is: COL.HDR.SUPP
|
|
EXPORT-SUBNAME
|
Used by the KEXPORT command to specify the name of the export subroutine associated to the export. Syntax is: EXPORT-SUBNAME "subroutinename".
|
|
|
Event Handler
|
Refer to the Export Specification Event Handlers topic for detailed information on Event Handlers. |
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
Related Topics:
Using Substitution Tags
Kourier Services
System Literals
ODBC to File Export Specification Line Item
Export Event Handlers