Skip to end of metadata
Go to start of metadata

Introduction

A flat file schema is a simple, one-level field structure that has the same data row type throughout the text document. This page covers creating a custom flat schema within a transformation.

Creating a Flat Schema

To be able to create a flat file schema, first add a transformation to an operation. If the operation also an activity located adjacent to the transformation on the side where you want to use a flat schema in the transformation, it must not have a file schema provided in the activity configuration. That is, while configuring the activity, in the area where you can provide a request or response schema, make sure to select No so that the schema is not inherited by the transformation.

Provided this condition is met, the Define Schema button will be displayed in mapping mode on each side of the transformation that does not already have a schema defined in an activity. Click Define Schema on the source or target side of the transformation and select Create Flat:

Configuring a Flat Schema

Configuration of a file schema using the flat file schema editor is covered as follows:

  • Schema Name: In the text entry box at the top of the screen, enter a name to use to identify the flat file schema.
  • Save Status: To the right of the schema name are the date and time that the transformation that this schema is associated with was last saved. This information is not associated with the schema itself, which is saved using the Save Changes button located at the bottom of the configuration.
  • Type: Select between two types for a custom flat file schema:
    • Fixed Width: The schema will be created in CSV format using length to delineate fields.
    • Character Delimited: The schema will be created in CSV format using character(s) to delineate fields.
  • Options: When Character Delimited is selected, these options become available:

    • Delimiter: Enter the character(s) to use as field delimiter(s) that specify the boundary between data fields. The default is a comma. You can also use multiple-character delimiters, such as a double pipe ||.
    • Line breaks for end of records: Select this checkbox to use line breaks to separate records. This option is selected by default. When this option is selected, end of records are denoted by line breaks so that each record exists on one line (unless using a qualifier). When disabled, line breaks are ignored and new records begin after the total number of fields are reached.

      NOTE: This option is relevant only for schemas used on the source side of a transformation. For schemas used on the target side of a transformation, line breaks are always used to denote end of records.
    • String Qualifier: Enter a character to use as wrapper to indicate a string of words to be treated as a data unit. The default is a double quote. In addition, select a corresponding business rule, Always or When needed, to indicate when the qualifier should be used. The default When needed is recommended.

    • Convert escape sequences: Select this checkbox to convert or ignore escape sequences as indicated by a backslash \. By default, escape sequences are converted in accordance with Java standards. When disabled, text conversion occurs with no interpretation of escape sequences.

      NOTE: This option is relevant only for schemas used on the source side of a transformation.
  • Add Field: Click the Add Field button to add new field rows one at a time to the table. These columns are available for each row:

    • #: Each field row will automatically be assigned a number in the order the row appears. Once you have at least two rows, to reorder them, either drag and drop to another position within the table, or hover over the number and click the up  or down  arrow.
    • Field: Enter a name for the field. The field name cannot contain whitespace characters or symbols such as these: @ $ % & / + ( ) : ; ,.  In addition, the field name cannot begin with a number, period (.), or hyphen (-).

      TIP: For reference on all unallowed characters, see NCName as defined by W3C.
    • Type: Use the dropdown to select a data type. Options for new custom schemas include stringintegerlongdatefloatdouble, and booleanIf a custom schema was created by mirroring a schema from a server-based activity, additional data types may be listed.

    • Begin: For Fixed Width schemas only, enter the starting character position. Note that 1 is considered to be the position for the beginning of the file.
    • Length: For Fixed Width schemas only, enter the number of characters to be reserved for this field. When the starting character position and ending character position are already entered, this field will be calculated automatically.

      Fixed-length text files always specify a certain number of characters that are reserved for each field. For example, you may identify a field called LastName that always has 30 characters reserved for it. If the actual value is "Smith", only 5 characters are used; the remaining characters have nothing in them.

    • End: For Fixed Width schemas only, enter the ending character position. When the starting character position and length are already entered, this field will be calculated automatically.

      NOTE: By default, fixed-length segments are terminated with a newline character.
    • Default Value: If you want this field to have a default value, enter the value here.
    • Format: If you want this field to have a specific format, such as for date or currency, enter the format here.
    • Actions: For actions you can take on an existing field row, hover over the field on the far right under the actions menu icon . Then click the copy icon  to duplicate the field row, or click the trash icon  to delete the field row.

  • Review Schema: As you manually define each field within the schema, a preview of the schema is displayed on the right side. The schema is displayed using the standard interface elements described under Nodes and Fields.
  • Save Changes: Click this button to save and close the file schema.
  • Discard Changes: Click Discard Changes to close the file schema without saving changes.

Accessing Menu Actions

After a custom flat file schema is created, you can access additional menu options that are available for all schemas.

These include deleting, renaming, viewing dependencies of, deploying, configurable deploying of, and adding to a group, as well as editing a schema or clearing a schema from a transformation. For details on these actions, see Schema Menu Actions.

On This Page

Last updated:  Jan 29, 2020

  • No labels