Import Layout Definition

Use the Import Layout Definition function to define, in detail, the layout of the input source file.

For more information on data imports, read the Data Import Tutorial in the Traverse Knowledge Base.

Creating an Import Layout Definition

  1. Click the New button, on the toolbar, to open a blank import layout definition record.
  2. Enter a new ID for the import layout in the Layout ID field.
  3. Enter a description for the import layout in the Description field.
  4. Select the structure of the source file from the File Type field.
  5. Enter, if applicable, the number of rows to initially skip, such as column headings or file headers, when importing data in the Skip Rows field.
  6. Select, if applicable, a character to separate fields from the Field Separator field.
  7. Select, if applicable, a character to separate rows from the Row Separator field.
  8. Select, if applicable, a special character to enclose text that may include the field delimiter from the Text Qualifier field.
  9. Select, if applicable, the default value to use when reading data from the Filename field.
  10. Use the data Filter, if applicable, to build conditions to filter the rows of imported data.

    11. A filter condition can consist of up to four options. Each option within the condition appears as a different color help distinguish it from the other options, Black Text [Blue Text] Green Text <gray text>. The instructions below illustrate how to build a condition.

    1. Select how to group values by clicking the black text component (See Table 1).
    2. Select a column heading to filter by clicking the blue text component .
    3. Select the filtering criterion by clicking the green text component (See Table 2).
    4. Enter, if applicable, a string of text or numbers to complete the condition by clicking the gray text component.
    5. Click the icon to add additional conditions if applicable.
    6. Use the Print, Preview, or Reset buttons to generate the report or set all fields to their default values.

      7. Button Name
      And
      Or
      Not And
      Not Or
      Add Condition
      Add Group
      Clear All
      Table 1 - Black Component
         
      Button Name
      Equals
      Does not equal
      Is greater than
      Is greater than or equal to
      Is less than
      Is less than or equal to
      Is between
      Is not between
      Contains
      Does not contain
      Begins with
      Ends with
      Is like
      Is not like
      Is blank
      Is not blank
      Is any of
      Is none of
      Table 2 - Green Component
  11. Enter a number for the order of the value within the source data in the Sequence field.
  12. Enter a description for the field in the Description field.
  13. Select the type of value to process from the source data using the Type field.
  14. Enter the character width of the value, if values have a fixed length, in the Width field.
  15. Enter a constant value or an available function in the Value field.
    Note: The Value determines the content of any non-RawText column. To view the available functions, click the View Functions button.

    Conversion (translation) FunctionsClosed Functions used to convert RawText data to the proper data type for import.

    16. Function Syntax Description
    LEN =LEN([FieldId]) Returns the numeric length the identified field value.
    TRIM =TRIM([FieldId]) Removes leading and trailing spaces from the identified field value. Returns a text value.
    SUBSTRING =SUBSTRING([FieldId], start, length) Returns the subset of text characters from the identified field value.
    CONVERT =CONVERT([FieldId]) Converts the identified field value to the field type of the current field.
    ISNULL =ISNULL([FieldId1],[FieldId2]) Replaces a null (or empty string) value with the identified replacement value.
    NULLIF =NULLIF([FieldId1],[FieldId2]) Returns a null when the value of field 1 matches the value of field 2.
    ROWID =ROWID() Returns the sequential id of the source data.
    GETDATE =GETDATE() Returns the current session date (workstation date).
    XLATE =XLATE([FieldId])
    Extended: val1;xlate1;val2;xlate2    		 Translates the value of the identified field using a set of key/value pairs provided in the Extended properties of the field definition.
    PARSE =PARSE([FieldId], "mask")

    Evaluates the contents of the identified field using a preset mask to specify positional attributes of the field value.

    Number Mask: Uses the '#' character to identify a numeric value position. The right-most position of the mask can be used to identify a special "sign" identifier by using a value other than a '#'.

    Example: =PARSE([RawValue], "#,###.##")

    Date Mask: Uses the following characters to identify the parts of a masked date value
    M = Month
    D = Day
    Y = Year
    h = Hour
    m = Minute
    s = Second

    Example: =PARSE([RawDate], "MM-DD-YYYY")
      =([FieldId1] + 1.25)
    =([FieldId1] + [FieldId2])    		 General arithmetic operations and string concatenation are also supported as a definition.
  16. Enter, if applicable, the values required for the selected Value in the Extended field.
  17. Click the Save button, on the toolbar, to save the new import layout definition.

Editing an Import Layout Definition

  1. Select an import layout definition to edit from the Layout ID field.
  2. Edit the fields as necessary.
  3. Click the Save button, on the toolbar, to save any changes made to the import layout definition.

Deleting an Import Layout Definition

  1. Select an import layout definition to delete from the Layout ID field.
  2. Click the Delete button, on the toolbar, to delete the selected import layout definition.
  3. Click Yes at the "Are you sure you want to delete selected record(s)?" prompt.