Purpose
To alter the layout of target records.
Format
| /REFORMAT [condition] | {CREATE LAYOUT target_layout_name new_layout_specs} {MAPTO LAYOUT target_layout_name mapped_layout_specs} |
where
| condition | = | CONDITION condition_name |
| new_layout_specs | = | target_layout_type VALUES target_field_list |
| target_layout_type | = | {DELIMITED [EXPORT] [COMPRESSED] } {POSITIONAL [EXPORT] } {XML [EXPORT schema_type] [COMPRESSED]} {TABLEAU [COMPRESSED] } deprecated {EXTERNAL [COMPRESSED] } |
| schema_type | = | {DTD | SCHEMA} |
| target_field_list | = | target_field [, target_field …] |
| target_field | = | reformat_field [ALIAS target_field_name] |
| reformat_field | = | {source_field_name} {value_name } [formatting] |
| formatting | = | {format_conversion} {value_editing } |
| format_conversion | = | {data_format [length]} {length data_format } |
| value_editing | = | {(datetime_mask)} {(numeric_mask) } |
| mapped_layout_specs | = | [COMPRESSED] mappings |
| mappings | = | MAPPINGS [DYNAMIC] target_field = value [, target_field = value …] |
| value | = | {[side:]source_field_name} {value_name } |
| side | = | {LEFTSIDE } {RIGHTSIDE} |
Arguments
| condition_name | The name of the condition that directly supports the reformat. |
| target_layout_name | When creating a new target layout, this is the name assigned to the new layout. When mapping to an existing target layout, this is the name of a layout that describes the target records, defined in a /DELIMITEDRECORDLAYOUT or /RECORDLAYOUT, or defined in an external metadata file using /DATADICTIONARY. |
| source_field_name | The name of a source field. |
| value_name | The name of a value defined by a /VALUE option. |
| data_format | The data type of the reformatted field. See Data types reference /Connect ETL data types in the Connect help for the list of supported data types. |
| datetime_mask | The edit mask that defines the date time format. |
| numeric_mask | The edit mask that converts the numeric reformatted field value to a printable format. See Syntax reference/Syntax of numeric edit pattern in the Connect help for details. |
| leading_character | The character in the field-delimited text records that optionally precedes the data in each delimited field. |
| trailing_character | The character in the field-delimited text records that optionally follows the data in each delimited field. |
Location
If records are reformatted only for a specific target, the /REFORMAT options must appear after the option defining the specific target and before the option defining the next target, if any.
Defaults
If you do not provide a /REFORMAT option for a join, Connect ETL joins the records so that the join keys appear first, followed by all remaining fields from the left side record, followed by the remaining fields from the right-side record.
Notes
The /REFORMAT option supports target record formatting. Through the /REFORMAT option, each target field can be formatted. Multiple reformats can be specified per target using conditional reformats:
- Conditional reformat – An unlimited number of conditional reformats are allowed per target. Conditional reformatting is available through the CONDITION suboption.
- Unconditional reformat - Only one unconditional reformat is allowed per target and this unconditional reformat must be the last reformat option for the target.
New Layout Reformatting
To define a new target record layout, use the CREATE LAYOUT suboption argument.
When creating a layout, use a target layout type that matches the target file type:
- DELIMITED – can be used for stream, variable, and fixed record format targets
- POSITIONAL – can be used for all targets except XML, Apache Avro, Apache Parquet, Tableau Data Extract, and QlikView data eXchange
- XML – must be used for XML targets
- EXTERNAL – must be used for Apache Avro, Apache Parquet, Tableau Data Extract, and QlikView data eXchange targets
For DELIMITED layout types, the SN, EN, and LOB data types can be provided without a length specification. For POSITIONAL layout types, however, all data types must be specified with a length specification.
For aggregate tasks, only group-by fields and summarized fields can be referenced in the /REFORMAT option.
Layout-mapped Reformatting
If there is an existing layout that describes your target records, you can use the MAPPINGS keyword to specify source fields to be mapped to fields in the target layout. The appropriate format conversions will be made automatically depending on the target layout format at run-time. Compression will be automatically applied when mapping binary source fields to displayable numeric fields. Specifying the COMPRESSED keyword will apply compression to all fields.
The contents of a filler field in a mapped target layout are undefined.
If a mapped target field has been deleted at runtime, the task will continue with a warning. If a mapped source field has been deleted, the task will abort.
When the DYNAMIC option is specified, Connect ETL enables runtime mapping of target fields. Any target field that is not explicitly mapped will be mapped automatically to a simple source field or value of the same name. If a matched source field has the wrong data type, the task will abort. Any target fields that remain unmapped will be considered to be mapped to NULL.
When mapping to XML, Apache Avro, or Apache Parquet metadata (linked via the /DATADICTIONARY option), the linked metadata type must match the target type.
Reformatting Joined Records
When reformatting records after a join, you need to indicate from both sides of the join.
A join task must not have more than one /REFORMAT option.
XML layouts
If new_layout_specs is an XML layout externally linked through /DATADICTIONARY, this option can be applied to an XML target defined by /OUTFILE. XML layouts must have a single repetition at the top level, of the form: Root = (field1, field2…)*. Fieldi can be composite – a sequence of elements, but cannot contain any other structures, i.e. repetition, optional or choice.
The mappings that are allowed between Connect ETL data types and XML Schema data types can be found in the Connect help under Data types reference/XML built-in data types
Connect ETL will insert NULL into target fields that are not mapped to. If a NULL value needs to be written into a non-nullable target field, an undefined value will be written instead.
A global /REFORMAT option cannot be used if the task has at least one XML target. A single /REFORMAT can be specified for an XML target (/OUTFILE with XML).
Record Numbering
To obtain a target record number, create and call a dmexpress target record function.
The record number field is treated in the same way as all other fields in a reformatting. Since it is a fixed length field, it is not automatically delimited during reformatting of field-delimited text records.
Manual numbering occurs when at least one reformatting for the target explicitly includes a record number field. If there are multiple reformats for a target, there is no restriction on the location of record numbers, and it is not mandatory for all reformats to include a record number. Record numbering may or may not be requested on the /OUTFILE option. If the starting number is other than 1, you need to specify this through the START option of /OUTFILE.
Bit Fields in Reformatting
You can include both byte fields occupying an integral number of full bytes and bit fields in a single /REFORMAT option. When a byte field follows a bit field, Connect ETL automatically aligns the byte field on the next available byte boundary in the reformatted record. When the prior bit field does not end on a byte boundary, Connect ETL fills the unused bits up to the byte boundary with ’0’ bits.
When a bit field follows another bit field, Connect ETL places the second bit field immediately after the first bit field in the reformatted record.
LOB Fields
If a LOB field originating from a database source is included in the reformat, the files containing the large objects are written to the same location as the target file. The user can change the default name and location of the files via the ToFile() function. Refer to the Functions Reference in the Connect help for information on the ToFile() function.
If a LOB field originating from a file source is included in the target reformat, then the file paths written to the target file are the same as the file paths in the source field if the source field contained an absolute path. If the file path in the source field is relative, then the file path written to the target file is the file path relative to the target file location.
A /REFORMAT is required if the target file will have any LOB fields.
Composite Fields
If formatting is specified for a composite field, the composite field is treated as a single field.
Aliases
The ALIAS keyword is optional. If you do not provide an alias, the field name is used to generate the alias name.
Examples
/REFORMAT CREATE LAYOUT customer DELIMITED VALUES
cust_id ALIAS id,
cust_name ALIAS name,
address ALIAS address,
city ALIAS city,
state ALIAS state,
zip ALIAS zip/REFORMAT CREATE LAYOUT book DELIMITED VALUES
vol_id ALIAS vol_id,
author_name ALIAS author_name,
book_title ALIAS book_title,
publisher ALIAS publisher,
pub_date ALIAS pub_date,
price ALIAS price/REFORMAT CREATE LAYOUT book DELIMITED VALUES
vol_id ALIAS vol_id,
/REFORMAT
bl4 ALIAS filler1,
author_name ALIAS author_name,
bl4 ALIAS filler2,
book_title ALIAS book_title,
bl4 ALIAS filler3,
publisher ALIAS publisher,
bl4 ALIAS filler4,
pub_date ALIAS pub_date,
bl4 ALIAS filler5,
price ALIAS price/REFORMAT CREATE LAYOUT new_transact POSITIONAL VALUES
transact.customer_number ALIAS customer_number,
na.full_name ALIAS full_name,
na.phone ALIAS phone,
transact.amount ALIAS amount/REFORMAT MAPTO LAYOUT outlayout MAPPINGS
outreclayout.id = inreclayout.id,
outreclayout.name = name,
outreclayout.total = totalThis reformat is part of the copy task. The first field of the output record is set to the value of the id field in the input record. name and total fields of the output record are set to the corresponding calculated values name and total.