Extract Layout

Overview

The Extract Layout tool is a metadata tool; it extracts the schema of a table or a data stream, which can then be output as a file or viewed in a Data Viewer tool. (The S connector outputs the sort order meta-data of the input and is rarely used.)

Extract Layout is a good tool for documenting the schema of a data file or database table for future reference. It is also useful for manual processing of metadata. For example:

• If you are generating a flat file or CSV file to send to someone else, you'll want to document the layout of the file to make it easier for the recipient to read.

• If the recipient of your file also uses Data Management, you can save the Extract Layout tool's results to a CSV file and import it directly into a Flat File Input tool or CSV Input tool by selecting Import Layout.

• You can use the Extract Layout tool to check whether multiple database tables or data files have the same schema. Once you've extracted the schema of each table or file to separate layout files, you can use a standard text-differencing tool like UNIX diff, MKS diff, or Microsoft Word's Document Compare feature to check for differences.

Extract Layout always produces at least three columns: NAME, SIZE, and DESCRIPTION. These contain the field name, formatted field size, and field description. The Include type and Include type details options let you include data type information, in either simple or detailed form. Note, however, that detailed type information can be rather verbose and inscrutable, for example:

( DIGITS = 19 FIELD_SIZE = 19 FORMAT_DECIMAL = Y KIND = INTEGER SCALE = 5 SCAN_IMPLIED_DECIMAL = N )

There are only two supported uses of the Include type details option:

• Producing a file that can be used by the Import Layout feature of the Flat File Input and CSV Input tools.

• Producing files for comparison with other layout files.

The type details information is intended primarily for use within Data Management; the format may change in future versions of the program.

Extract Layout tool configuration parameters

The Extract Layout tool has a single set of configuration parameters in addition to the standard execution options.

Configuring the Extract Layout tool

1. Select the Extract Layout tool.

2. Go to the Configuration tab on the Properties pane.

3. To include field descriptions in the layout, select Include description.

Not recommended except for documentation. Do not select this option if you are exporting a layout for use with the Data Management's CSV Input and Flat File Input tools.

4. To include field type information in the layout, select Include type and optionally specify Type details.

Recommended for exporting a layout for use with Data Management's CSV Input and Flat File Input tools.

Type details	Description
None	Outputs the simplest type specifier. The type name is included (for example, TextVar, Int, Decimal). If the type's size is different than the SIZE, it is included in parentheses. The Int type is always represented with a size in bytes such as Int(4). The Decimal type is always shown with digits and scale, such as Decimal(12.2).
XML	Outputs a complete type specifier in XML format. Recommended for use in Data Management. Not recommended for interchange with other software.
Columns	Outputs Type, Format, and FlatFormat columns. The flat format specification is used only by the Flat File Input and Output tools, and is used to specify non-ASCII formatting of data like EBCDIC or binary representations. May useful to other software and for documentation purposes. May be used by CSV and Flat File Input tools.

5. If the layout file will be used to configure a flat flat, select Use flat sizes.

Note that if the input data is not from a Flat File Input tool, you may also need to use the Change Field Types tool to define the flat sizes of the columns.

6. Optionally, go to the Execution tab, and then set Web service options.

To produce a format file for import into the CSV or Flat File Input tools, connect the Extract Layout tool to a CSV Output tool  and configure the following settings:

Include description: no
Include type: yes
Type details: columns
Use flat sizes: yes

Extract Layout S output

The Extract Layout tool has an extra output, labeled S. This writes the sort order meta-data of the upstream input. This information is generally used for testing purposes.

The sort-order meta-data describes how the data is known to be sorted, and can consist of multiple parts. The data format of this connector has three columns:

• PART: an integer, starting at 1, identifying the sort order part.

• FIELD: the field name.

• ORDER: either ASCENDING or DESCENDING.

For example, suppose you have some data that you sort by ZIP(ASCENDING) then by NAME(DESCENDING). Then you number of the records and put the record number into the RECORDID field. At this point the sort meta-data has two parts, and will look like:

This output shows that the data is sorted in two different ways, both of which are simultaneously valid.