Spectre Build Text Input

The Spectre Build Text Input object brings raw data into the build script from one or more text files.

text-input icon

The text input object has four sections in the properties panel where you set attributes.

Text Input Properties Pane

You specify input files in the Paths panel.

text-input Paths

Attribute Description

Input Files

Defines one or more text-input files as a data source. Use project based pathing.

  1. Click the browse button for a row in the Input Files.

    The Select File dialog box displays.

  2. Navigate to the file or a folder if using multiple files similarly named.

  3. Select the input file or type a matching string with a wildcard character.

    The Input Files row is now populated.

  4. Click the plus button and repeat steps 1—3 to add additional rows in the Input Files section.

    NOTE: Alternatively, you can type the path and file name or a matching string with a wildcard character in each row of the Input Files section.

  • For the wildcard character, you can use question mark (?) or asterisk (*).

      • ?—Matches any single character

      *—Matches a sequence of characters

  • Wildcard character matching is case-insensitive and can search more than on directory.

    For example: /data/b*/*/*.txt

  • When using wildcard characters, files are returned in the order that they are found in the directory; the files are not necessarily sorted alphabetically. The order can vary across systems, and is often related to how files are deleted and added in the directory. Programs should not rely on a certain order.
  • Zip files are supported, and the files within are treated as multiple files.
  • Windows has a limit of 16,384 files. On Linux, the limit is set by an administrator.

Each object has an area where you can enter comments for documentation purposes.

It is a DI best practice to enter a comment for every object in a Spectre build script.

Comments box

The text-input object's Columns grid displays the columns from the input files. A count is included in the header. The context menu on the Columns grid supports the following row actions: Copy, Paste, Insert Row, and Delete Row.

Text Input Columns

For each column, specify the column name, data type, and whether to keep or remove the column.

Attribute Description
Column

If column headings exist, displays the name of each input column.

If column headings do not exist, you must specify all columns for Spectre to know the names. An empty string cannot be used as a column name. See the Column Properties tab.

Column names are case-insensitive.
Type

Specifies the type of data in the column. Spectre attempts to auto-detect the type; however, it is a best practice to check these and correct as necessary to ensure faster processing and less memory usage.

TIP: Spectre does not auto detect datetime fields. If you want a column to be interpreted as a datetime, be sure to specify that type.

Valid values are boolean, date, datetime, double, fixed100, integer, period, and string. See Spectre Data Types.

NOTE: The type for date assumes that the input has a formatted date, not the binary date value.
Keep

Keeps only the specified columns from the input. Useful when there are many columns and only a select few are needed.

TIP: Best practice is to specify which columns to keep rather than which to remove.
Remove

Removes the column named.

You set values for the text input object's properties in the TextInput Properties tab. These are general properties related to the text file.

Attribute Description

Include Other Columns

Ensures that no columns are dropped. Use when you explicitly define a few columns with special properties and then want to ensure that all data is included in the build.

Click on the ellipses to display the ClosedInclude other columns editor.

Include other columns editor dialog

Additional properties

Misc Property Value
Include other columns Select to toggle between True or False.
Suggested Dimension Select True if you want the column treated as a dimension in ProDiver; otherwise False.
Summary Type Select the summary type: sum, info, any, first, last, const.
Type Select the column type: string, integer, double, fixed 100, date, datetime, period, boolean.

If no columns are specified in a Build script, Include Other Columns is assumed in order to avoid building a cBase with no columns.

Contains Column Headers

Indicates the presence or absence of column headings in the flat file. Defaults to True. Columns with an empty heading ("") are ignored. Set to False if the input file does not contain headings, then be sure to list each column by name and type. See the Column Properties tab.

Contains Quotes

Indicates how to handle quotation marks. By default, Spectre looks for quotation marks and keeps strings intact.

Set to False for Spectre to ignore any quotation marks found in the data file. This is useful when the input does not use quotation marks consistently.
Date Format

Deprecated.

Delimiter

Specifies the delimiter for the flat file. If delimiter is not specified, Spectre attempts to auto-detect the value. Best practice is to specify the delimiter value. The value must be a string and is either a single character (for example, "\t" for tab, " " for space, "," for comma, ";" for semicolon, "|" for pipe) or the keyword tab, space, comma, semicolon, or pipe.

TIP: An empty string cannot be used as a column name.
Dictionary File

Specifies the path to a dictionary file to use with text-input. Dictionary files are used to specify column names when the input lacks column headers. When a dictionary file is selected, it is used to populate the available columns in the grid.

NOTE: Dictionary files are considered legacy functionality—not all options are supported.

Encoding

Defines the encoding used in the input and output files. Encoding types supported include: cp-1252, iso-8859-1, utf-16be, utf-16le, utf-8, gb18030, and windows-1255.

  • Best practice is to specify the encoding of the input data.
  • For text inputs, Spectre analyzes the first few megabytes of the file attempting to determine the encoding.
  • Information output from the build indicates which encoding is in use.
  • Any incoming bytes that are not valid per the specified or assumed encoding are replaced with placeholders; these appear as squares in ProDiver.
  • Non-Unicode DI clients are Unicode-aware.
  • See also Integrator Unicode Data Support.

Filename Column

Adds a column with the input file name. When reading multiple files, this attribute captures the file name on each row in the cBase.

Limit Rows

Directs the build to use a specific number of rows from each input file. If multiple input files are used, the first specified number of rows are read from each file, ensuring a complete sampling of data values.
TIP:  Setting limit rows to 1 is an easy way to ensure that calcs find all needed columns in the input.
Prefix

Prefixes all the outputted column names with the specified string.

Trim Whitespaces

Removes any leading or trailing spaces from the data. This is the default behavior for Spectre. Set to False to disable the trimming of white space.

Limit Line Warnings

Limits the detail line warnings to the specified number before the text reader stops logging the errors.

Limit Similar Value Warnings

Limits the similar-value check for the entire text-input. The default is 20 warnings per column. Set to zero to turn off all warnings for the input. See also Disable similar value warning.

Limit Value Warnings

Limits display of values with problems in them to the specified number.

You set values for the input columns in the Column Properties tab. These are properties related to the selected column. Select a column on the left, then select the Column Properties tab on the right to set values.

text-input column properties

Attribute Description

Calendar

Adds a calendar for the selected column. If the column type is period, a calendar tag is required.

Valid calendars are:

  • standard—Gregorian calendar; period type options are:
    • year
    • year-half
    • year-quarter
    • year-tertile
    • year-month
  • offset <month>—Fiscal year similar to the standard calendar except starting on a different month; period type options are the same as those for standard calendars.
  • iso 8601—ISO week-numbering system; period type options are:
    • year
    • year-week
  • custom—Custom calendar created from a text file with a contiguous range of dates; required columns are date and year, optional columns are half, quarter, tertile, month, and week.

Deprecated but available for compatibility:

  • gregorian month—Gregorian year-month
  • gregorian quarter—Gregorian year-quarter
  • iso8601 week—ISO-8601 year-week
  • gregorian+<N> month—Offset Gregorian year-month for fiscal calendars. Specifying gregorian+6 month means add six months to the real year-mo to get the fiscal year-mo, which is like saying The fiscal year starts in July.
  • <custom calendar file>—For more tailored fiscal calendars

See Calendar Declarations.

Column Name

Displays the column name to indicate which column's properties you are configuring.

Custom Properties

Defines custom properties to associate extra data with a column. The custom property can be extracted after the build and used in expressions.

  1. Select the column.
  2. Under Properties, select the Custom Properties row, and click the browse button.

    3. The ClosedCustom Properties Editor displays.

    Custon Properties Editor

  3. Select the plus button to add a row.

  4. Enter a Name and a Property Value. For Closedexample:

    Sample Custom Properties Entry

  5. Click OK.
Disable Similar Values Warning

Turns off data warnings of similar values for this column. Use only after data validation steps have determined that the warnings are in fact not worrisome.
Format

Use to format numbers, dates, periods, currency, and Booleans. Sets both the display format and the input properties, unless a separate input format is specified.

  1. Select the column.

  2. Click the Format property, and select a format from the list. The list shows different suggestions based on the type of the column.

    text-input Format Property

Also, see the input format and Spectre Format Conventions.

Input Format

Clarifies the incoming data. For example, the default format for date data is YYYY/MM/DD. Use the input format to define the date format that is in the input file to ensure correct interpretation.

  1. Select the column.

  2. Click the Input Format property, and enter a format or select one from the list. The list shows different suggestions based on the type of the column.

    text-input Colum Properties Input Format

Keep Column Status

Indicates the column status as determined by what is checked in the Keep and Remove columns. Possible values: Explicit Keep, Explicit Remove, and Auto Remove, where nothing is checked.
Label

Defines the display label used for a column.

Nulls

Maps known values used to represent no data to the null value or a special value in the cBase. Enter a string for the known value followed by a string for the substitute value. Or enter a string for the value to be set to null.

Period Type

Refines the period type when the Spectre data type for the column is period. See the calendar attribute for valid values.

Required Dimension

Sets a column to behave like a classic Info Field. In ProDiver, the info field is available as a column that you can add to other columns displayed in a dive. For example, to see Address requires that Customer is available.

  1. Select the column you want to define as an Info Field.
  2. Select Required Dimension in the Properties pane.
  3. Locate and select the core dimension from the list.

    4. The first column is only available when the second column is displayed in the client.

See Defining Info Relationships in Build Code Samples. Also, see the separate ProDiver Help.

Sort By

Specifies an alternate sort. When specifying columns, use sort by to order them based on another column's value.

  1. Select the column to which you want to apply an alternate sort order.
  2. Select Sort By in the Properties pane, and select from the available columns list.
  • An alternate sort can only be applied to a string column, and the sort-by column must be a string or an integer column.
  • You cannot add, remove, or change alternate sorts after a cBase is built.
  • Dive and cPlan files also have a sort tag, but for string data, rely on alternate sorts (if any) that are established when the cBase is built. Dives can also specify sorting on numeric columns.

  • By default Spectre uses "numeric collation" when sorting strings. When a subset of a string looks like a number, then the numeric value is considered during the sort. For example, Spectre sorts "a2" before "a10". Also, see the sort natural option.

  • Null strings sort at the end.

  • If two distinct strings in the column to be sorted are set to sort as the same value in the sort-by column, then the normal ordering of those distinct strings resolves the tie.

  • If a row has the value null or the value unknown in the column with an alternate sort, then the sort-by column must also have the same value.

  • If the sort-by column has type string AND has the value null or the value unknown, then the column to be sorted must have the same value.

  • Spectre issues a warning if you attempt to alternate sort null as non-null, or unknown as not-unknown.

Sort Natural

Disables natural string sorting in a text-input object. The default is True—Spectre interprets value of digits as decimal numbers and sorts according to the numeric values in the string.

TIP: This feature only makes sense together with sort-by if the sort-by column is also a string column.

Suggested Dimension

Allows you to promote or demote a column.

  • Set to True to promote a numeric column type to become a diveable dimension and have it appear in the ProDiver console.
  • Set to False to demote a column from classification as a dimension so it does not appear in the ProDiver console.
  • When set to False, the new dimension does not appear in the console (which requires dimcount processing), but it can be promoted to a dimension for diving—equivalent to the concept of a dynamic dimension for a classic model.

  • Using Required Dimension on a column effectively sets Suggested Dimension to False.

Summary Type

Sets the column's summary type explicitly. Any summary function that takes a single argument (except for count and percentile) is allowed. See Summary Functions.

Most common values are: sum, info, any, first, last and const. The default for numeric data is sum; use const for a column with only one value. The rest of the functions (for example, min, average, median) are better done as a separate calculation, that is, not the default one.

Mentioned in: