Use the Flat File Schema editor to set and define data formatting in source and target flat file Schemas.
Data records and fields can be selected for editing from the Schema section and then defined in the Properties view.
Record Groups, Records, and Fields
The Schema section displays data structure based on Records, Record Groups, and Fields.
The table below describes the available navigational buttons.
Click to determine whether the file is delimited or not; use the Sample section of this editor to set a Delimiter and Escape Wrap Character when it is.
Note: The delimiter specified in the Schema instructs the Studio to show what sample data would look like when using the specified delimiter. This field does not control the delimiter used when data is written to a flat file.The actual delimiter used at runtime to write a file must be specified in the Transformation Settings object.
Add Record Group
Adds a parent group to house multiple records. Record groups can be at the parent, sibling, or child level.
|Add Record||Adds a single record.|
|Add Field||Adds a single field.|
|Move Left/Right||Moves a selected field to a new position within the record.|
|Expand/Collapse All||Expands the Schema to display all records; Collapses the Schema so that no records are displayed.|
|Remove||Removes selected Records, Fields, or Record Groups from the Schema.|
Note: These same options can also be selected with the right-click of the mouse.
Using the Properties View
The Properties view displays different options based on the Record Group, Record, or Field that is selected in the Schema section. This is how you view and define properties for data structures.
Defining Properties at the Record Group Level
Different properties for a Record Group can be defined using four tabbed views: Properties, Group Constraints, Data Group, and Constraints. (These can be seen in the image above)
Properties tab: Includes the Description (Group Name and optional Description) and Group Type sections.
Group Type provides two options:
- OrderedAfterStart (default)
OrderedAfterStart is default behavior, which (after a Record Group starts) will process records in the sequence they are listed in the Group. While they may be omitted (optional), they can't appear out of this order. In the example below, the Record Group LT204 contains 5 records, starting with ELT204 and ending with EOT_1. If your inbound data does not match the order as defined in the Schema, then transformation errors will occur, as the Ruleset will be unable to identify a record that is out of order.
However, if you know that your source data is not in the order as defined in the Schema, then selecting the advanced UnorderedAfterStart Group Type allows records to appear out of order after the start of the group but still match to that group.
Note: Since Record Groups are defined by the very next record or group, the UnorderedAfterStart logic does not include the first record. Using the same example above, ELT204 would need to the first record in order for processing to start. After that, the remaining records could be in a different order as shown, and processing would still complete.
Group Constraints tab: Defines constraints for all records within a group.
Constraints tab: Defines constraints for the actual Record Group.
Data Group tab: Choose either to group on record or no grouping at all. Data Grouping is a way of segregating data based on common attributes or structure and is used mainly by the Data Analysis Ruleset (DARS) to properly analyze and transform specific groups of data. Data Grouping is not used for single-format flat files. Data can also be grouped at the individual record level or field(s) level.
Defining Properties at the Record Level
Different properties for a record can be defined using three tabbed views: Properties, Data Group, and Constraints.
- No Record ID: Applies to Single-format only. Record IDs are required for multi-format flat files.
- Field: Used to set a specific field as a Record ID for delimited multi-format flat files that contain record IDs in a particular field.
- Fixed Position: Used to set the starting position for fixed-length multi-format flat files that contain record IDs in fixed positions.
- Value: Sets a value for a Record ID. This value is used to define the record whenever the Studio encounters it. This applies to fixed or delimited multi-format flat files.
Note: Record IDs are used in multi-format inbound records; for outbound records, if the ID Field is not mapped, the Studio will automatically map the defined ID Value from the Schema.
Data Group tab: Data Grouping is a way of segregating data based on common attributes or structure, and is used mainly by the Data Analysis Ruleset (DARS) to properly analyze and transform specific groups of data. Data Grouping is not used for single format flat files.
- No grouping.
- Group by record (the record must be selected)
- Group by field (with parent record selected); and specific fields to be included.
In the example below, choosing to group by the PO record would result in the Ruleset identifying two record groups. Two groups would also result when grouping by field, in this case, Bob Smith.
Constraints tab: This section of the editor lets you define certain constraints for the selected record.
Defining Properties at the Field Level
Different properties for a field can be defined in the Properties tabbed view. This includes the Description (Field Name and optional Description) and Field sections; the latter containing these settings:
- Length of the field (number of characters)
- Minimum and Maximum Length
- Total Digits (Enabled when Type is Integer, Decimal, or Implied Decimal)
- Fractional Digits (Enabled when Type is Decimal or Implied Decimal)
- Type: String, Integer, Decimal, or Implied Decimal.
- On Read, treat empty string as null. (Applies to String type only)
- Default Value on Target: Set a value to populate fields whenever the source contains null data.
Note: In order to set the default value on target, you must also configure the Null Source Action drop-down found on your Ruleset editor, under the Runtime view. This only applies to older, Version 1 Rulesets. New engine functionality (Version 2) no longer requires this additional configuration to the Ruleset editor.