TSTool / Command / ManipulateTableString

Overview
Command Editor
Command Syntax
Examples
Troubleshooting
See Also

Overview

The ManipulateTableString command manipulates a string column in a table. For example, it may be necessary to manipulate strings in a table to match time series identifier parts, so that lookups can occur, or to ensure that missing data are filled. The input is specified by:

a table column name (InputColumn1)
optionally, either a second input column name (InputColumn2) or a constant string value (InputValue2), depending on operator
optionally, some operators require an additional input value (InputValue3)

The result is placed in the output column (OutputColumn). Missing/blank input will be considered as empty strings when formatting the output. The output column can be the same as an existing table column.

If table input values are null, the default is to set the output to null. Use UseEmptyStringForNullInput=True to ensure that table input values are not null.

Command Editor

The command is available in the following TSTool menu:

Commands(Table) / Manipulate Table Values

The following dialog is used to edit the command and illustrates the syntax of the command.

ManipulateTableString Command Editor (see full-size image)

Command Syntax

The command syntax is as follows:

ManipulateTableString(Parameter="Value",...)

Command Parameters

Parameter	Description	Default
`TableID`	The identifier for the table to process. Can be specified with `${Property}` notation.	None – must be specified.
`ColumnIncludeFilters`	Specify values to match to include rows using syntax: `Column1:Value1,Column2,Value2`, where values can use `*` for wildcard. All values must be matched to include a row. Can be specified with `${Property}` notation.	Include all rows.
`ColumnExcludeFilters`	Specify values to match exclude rows using syntax: `Column1:Value1,Column2,Value2`, where values can use `*` for wildcard. All values must be matched to exclude a row. Can be specified with `${Property}` notation.	Include all rows.
`InputColumn1`	The name of a column containing strings, as the first input. Can be specified with `${Property}` notation.	None – must be specified.
`Operator`	The operation to perform on the input strings. See the table below for a summary of the input and output parameters required for each operator..	None – must be specified.
`InputColumn2`	The name of a column containing strings, as the second input. Can be specified with `${Property}` notation.	Required if a 2nd input value is needed no `InputValue2`.
`InputValue2`	A string constant, as the second input. Can be specified with `${Property}` notation. Specific requirements for `Operator` are: `Remove` - string or expression in input to remove: combination of `^` to indicate start of line, `$` to indicate end of line, `\$` to match a dollar sign, `\s` to indicate a space, and character pattern to remove `[ABC]` - a list of characters to remove from input `Replace` - string or expression in input to remove combination of `^` to indicate start of line, `$` to indicate end of line, `\$` to match a dollar sign, `\s` to indicate a space, and character pattern to remove	Required if a 2nd input value is needed and no `InputColumn2`.
`InputValue3`	A string constant, as the third input. Can be specified with `${Property}` notation. See note for `InputValue2` for `Replace` operator.	Required if a 3rd input value is needed.
`UseEmptyStringForNullInput`	If `True` set null input from table cell values to an empty string.	`False`
`OutputColumn`	The name of a column to receive the output. The output column can be the same as one of the input columns to update a column. Can be specified with `${Property}` notation.	None – must be specified.

The following table describes the Operator parameter and additional parameters that are needed for each operator.

Operators and Parameter Requirements

Operator	Description and Required Parameters
`Append`	Append the second input to the first input (requires 2 inputs).
`Copy`	Copy the first input to the output (requires 1 input).
`Prepend`	Prepend the second input before the first input (requires 2 inputs).
`Replace`	Start with the first input, replace the substring indicated by the second input with that of the third input (requires 3 inputs).
`Remove`	Start with the first input, remove the substring indicated by the second input (requires 2 inputs).
`Split`	Split string by delimiter and output part. `InputValue2` is the delimiter character and `InputValue3` is the part to output (1+).
`Substring`	Split out a substring from the first input, where: the second input is the starting character position (1+), or specify a negative number for the number of characters from the end of the string and the optional third input is the ending character position (1+), or specify a negative number for the number of characters from the end of the string This operator requires 2 or 3 inputs.
`ToBoolean`	Convert the first input to a boolean object. Input can be `true` or `false` (any case), or 0 or 1 (as string, integer, or long integer).
`ToDate`	Convert the first input to a DateTime object with date precision.
`ToDateTime`	Convert the first input to a DateTime object.
`ToDouble`	Convert the first input to a double precision object.
`ToInteger`	Convert the first input to an integer object. If the input string contains a floating point number, the number will be rounded.
`ToLong`	Convert the first input to a long integer object. If the input string contains a floating point number, the number will be rounded.
`ToLowerCase`	Convert the string to lower case.
`ToMixedCase`	Convert the string to mixed case, where the first letter of each word is capitalized and other characters are lowercase (e.g., `Mixed Case`). Words are separated by whitespace. Words that are concatenated are treated as one word (e.g., `Mixedcase` because the command does not know that `case` is a separate word).
`ToUpperCase`	Convert the string to upper case.

Examples

See the automated tests.

Troubleshooting

See the main TSTool Troubleshooting documentation.