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. Many of the columns are informational only (highlighted as No). The other columns highlighted as Yes  can be changed as noted by the "Change" column. The data in the Excel spreadsheet will be used when building the data export specifications and services when using the KT.SCHEMA2EXPORT command.
Column Description - Usage - Notes

Change
ItemID The item-id for each record exported.  It indicates the source file and the corresponding attribute number.  This column should not be changed. No
Type The source dictionary type code.  This column should not be changed. No
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. No
Conversion The conversion code found in the source dictionary.  Change this field as required. 

Note:  using commas in the conversion code (e.g., MD4,) will cause the data in the column to be treated as a literal value (e.g., "MD4,") when using Excel 2007 or higher  to save the spreadsheet as tab delimited text file. See Step 4b below for more information.

 Yes
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., title casing, standard abbreviations, etc.). Yes
Format The format code found in the source dictionary.  This column should not be changed. No
Structure The structure code found in the source dictionary.  This column should not be changed. No
Association The association found in the source dictionary.  Edit this column to indicate if two or more multi-valued fields.  Yes
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.

Note: normally you do not need to change this field. If you need to change it, understand that the data type may be incompatible with the actual data in the file and if so the data might not load into the MS SQL Server database.

Yes
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. Yes
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. Yes
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. Yes
No_AM The number of records that contain a non-null attribute. No
No_VM The number of records that contain values for this attribute. No
No_SVM The number of records that contain sub-values for this attribute. No
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. No
!! Indicates that this row contains non-conforming data in either the MVType, DataLen or No_NNUM columns. No
  1. Once all edits are complete you will need to save the spreadsheet, use the format below based on your version of Microsoft Office.
     
    1. Using Microsoft Office Excel 2003 - save the spreadsheet as a Microsoft Excel 4.0 Worksheet in a temporary directory (i.e. ../ODS/Schema).
       
    2. Using Microsoft Office Excel 2007 and higher - save the spreadsheet as a Text (Tab Delimited) file in the directory ../ODS/Schema.

      Note: Excel 2007 and higher removed the ability to save a document as a Microsoft Excel 4.0 format. Saving as a tab delimited text file surrounds data containing embedded commas with quote marks. After the file has been saved to a text file, if there were any conversions that included a comma (e.g., MD4,), remove any unwanted quote characters around the conversion with an editor.
       

  2. At TCL, use the wIntegrate Export to Host feature (Run | Export File...) to download the changes made in your spreadsheet back into the KT_SCHEMA file. The fields that are important in the dialog are:

Field Name Description - Usage - Notes
Local File The path the the file you just saved (in either Microsoft Excel 4.0 Worksheet or Text (Tab Delimited) format).
Host File Always KT_SCHEMA.
Format Use Excel (4.0) if you saved the file with Excel 2003 as a Microsoft Excel 4.0 Worksheet.

Use Tab Separated if you saved the file with Excel 2007 or higher as  a Text (Tab Delimited) file.
Overwrite Always Yes.

 

User Guide - Table of Contents