Schema Editing Guide

This guide outlines the steps required to export the schema information for a file to Excel for editing and to import the edited information into Kourier for further processing.

Assumptions made in this Guide

  1. The export DEMO_KTSCHEMA has been copied from the KOURIER account to the current working account.
  2. The wIntegrate host programs have been installed in the current working account. 
  3. wIntegrate is the telnet client being used to run TCL commands.

Procedure

  1. In the Kourier GUI, use the DEMO_KTSCHEMA export to display the results of the KT.FILEUSAGE command.  Change the FILENAME selection criteria to the file name you wish to export.
  1. In the Kourier GUI, click on the TEST button. Change the number of records to sample to 0 (this displays all fields for a file instead of just the first 100 fields). When the Test Export Specification Complete dialog displays, click the OK button to display the results in Excel.
  1. The following table describes the columns in the Excel spreadsheet, their meaning and how they are used by the quick-start tools.
Column Usage
ItemID The item-id for each record exported.  It indicates the source file and the corresponding attribute number.  This column should not be changed.
Type The source dictionary type code.  This column should not be changed.
Name The name of the source dictionary that was used to determine the contents of the Type, Conversion, Column_Heading, Format, Structure and Association columns. This column should not be changed.
Conversion The conversion code found in the source dictionary.  Change this field as required. 
Column_Heading The column heading found in the source dictionary with special characters and spaces removed.  Edit this column to reflect the naming conventions used in your target SQL database (i.e. camel casing, standard abbreviations, etc.).
Format The format code found in the source dictionary.  This column should not be changed.
Structure The structure code found in the source dictionary.  This column should not be changed.
Association The association found in the source dictionary.  Edit this column to indicate if two or more multi-valued fields. 
DataType The data type field is derived from the conversion code found in the source dictionary.  Possible values are: CHARACTER, INTEGER, NUMERIC, CURRENCY, DATE and TIME. This column should not be changed.
MVType The MVType field is derived from the actual structure of an attribute and will only contain a value if the analyzed value conflicts with structure code in the source dictionary.  Edit this column if you would like to override the structure code of the source dictionary.
DataLen The data length field is derived from the actual length of the data in an attribute, value or sub-value.  This column will only contain a value if the actual data length of an attribute, value or sub-value exceeds the length specified in the format code in the source dictionary.  Edit this column if you would like to override the data length of the source dictionary.
Xclud The exclude field is derived from the actual data in an attribute and will contain a value of "Y" if all records analyzed contain no values for this attribute.  Edit this column if you would like to exclude additional fields.
No_AM The number of records that contain a non-null attribute.
No_VM The number of records that contain values for this attribute.
No_SVM The number of records that contain sub-values for this attribute.
No_NNUM The number of records that contain non-numeric data for this attribute.  This column will only contain a value if non-numeric data was found for a field with a data type of either TIME, DATE, CURRENCY, NUMERIC or INTEGER.
!! Indicates that this row contains non-conforming data in either the MVType, DataLen or No_NNUM columns.
  1. Once all edits are complete, save the spreadsheet as a Microsoft Excel 4.0 Worksheet in a temporary directory (i.e. ../ODS/Schema).
  1. At TCL, use the wIntegrate Export to Host feature to download the changes made in your spreadsheet back into the KT_SCHEMA file. The fields that are important in the dialog are:

Field Usage
Local File the path the the file you just saved as an Excel 4.0 Worksheet
Host File Always KT_SCHEMA.
Format Always Excel (4.0).
Overwrite Always Yes.

 

User Guide - Table of Contents