Spectre Build Excel Input

The Spectre Build Excel Input object brings Excel spreadsheet data into the build script. It accepts input from a .xlsx file.

Excel input GUI icon

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

Spectre Build Excel Input Propterties Panel

Excel Input Properties

You set values for the Excel input object's properties in the Excel Input Properties tab.

Spectre Build Excel Input Attributes

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 Include 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.

Headers

Indicates the presence or absence of column headings in the Excel file. Set to False to name each column manually.

Limit Rows

Directs the build to use a specific number of rows from the Excel file.

Password

Provides a password if the Excel file is password-protected. It is not recommended to use password-protected Excel files as data sources.

Prefix

Prefixes all the outputted column names with the specified string.

Range

Specifies a range of cells to use from the input. Any cells outside of the range are ignored. The format for the range is the top-left cell (column letter and row number), followed by a colon, followed by the bottom-right cell (column letter and row number). For example, A1:D25 returns the first 25 rows from the first column (column A) through the fourth column (column D).

Sheet

Specifies which sheet in the Excel file to pull data from. The value can be either a name or a number. Spectre checks for a name match first. If that fails, it tries treating the sheet as a number. Name matching ignores case, and if multiple sheets have the same name it only looks at the first one that matches. If this value is empty, the first worksheet in the Excel file is used.

Start Row

Defines the row number to begin reading data from. If this value is empty, Spectre attempts to automatically determine which row represents the start.

NOTE: If the Headers value is set to True, the value of Start Row should be the row number the header is on.

Trim

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.

Excel File Path

Defines a .xlsx file as a data source. Use project based pathing.

Columns

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.

Spectre Build Excel Input Column Grid

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

Attribute	Description
Column	If column headings exist, the name of each input column is displayed. 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 also 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 named column.

Column Properties

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

Spectre Build Excel Input 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 also 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. Adding custom properties Select the column. Under Properties, select the Custom Properties row, and click the browse button. The Custom Properties Editor displays. Select the plus button to add a row. Enter a Name and a Property Value. For example: Click OK.
Disable Similar Values Warning	Turns off data warnings of similar values for this column. NOTE: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. Setting the format Select the column. Click the Format property, and select a format from the list. The list shows different suggestions based on the data type of the column. 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. Setting the Input Format Select the column. Click the Input Format property, and enter a format or select one from the list. The list shows different suggestions based on the data type of the column.
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.
Repeat if Blank	When set to true each blank value in the column is set to have its value be the previous non-blank value in the column. For example, if the value of row 1 is "A", and row 2 has a blank value, row 2 is given the value "A".
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. Setting required dimensions Select the column you want to define as an Info Field. Under the Properties pane, select Required Dimension. Locate and select the core dimension from the list. 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. Setting sort by Select the column to which you want to apply an alternate sort order. Select Sort By in the Properties pane, and select from the available columns list. NOTES: 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, 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, 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, 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. NOTES: 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). 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 also 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.