Column actions

The Analyst's Notebook import wizard provides column actions that can modify incoming data in several different ways. The following table contains a complete list of the available actions.

Action Name Description
Add Prefix Adds characters immediately before the existing value. For example, if you know that all the telephone numbers in the data have the same area code, but that code isn't in the data, you can add it.
Add Suffix Adds characters immediately after the existing value.
Change Capitalization Changes the case of text. You can change case to lowercase or uppercase, capitalize initial letters, or reverse the current capitalization.
Compress Repeated Characters Replaces a repeated character with a single instance of that character. For example, automatically generated data might contain extra space characters that you can use this column action to remove.
Copy Value from Previous Row Replaces the value in a column with the value from the same column in the row above it.
Extract Text using Fixed Widths Extracts a range of text from the value in a column by specifying the fixed positions of its first and last characters within the value. For example, you might use this action to extract only the hours and minutes from time values that contain seconds in the incoming data.
Extract Part using Separators Splits column values into parts that are separated by a particular character. You can extract one or a range of parts from the value by specifying their position within it. For example, using space as the separator character, the value Andrew Peter Smith is made up of three parts. To extract the middle name from the value, you type 2 in the Extract part number box. To extract multiple parts, you can use the Up to and including part option. Decide which direction you want the count to begin from and click the corresponding button.
  • From the beginning of the column, you can only include part numbers that are greater than your first part. For example if your first part is 3, you cannot include parts 1 and 2.
  • From the end of the column, you can only include part numbers that are less than your first part. For example, if your first part is 4, you can include parts 1, 2 and 3.
Find and Replace Text Replaces a sequence of characters with another sequence of characters. For example, to remove English ordinal suffixes, you can replace st, nd, rd, and th with a sequence of zero characters.
Note:
This column action can cause unintended changes. For example, if you replace the st date ordinal suffix with zero characters, words such as August might be modified too.
Prefix with Another Column Adds the value from another column to the beginning of the value in the selected column. For example, you can prefix time values with date values to create a combined date and time column. You can also specify a separator character to use between the values.
Remove Characters Removes all occurrences of the specified characters from each value. You can configure the column action to remove spaces or tabs, and enter custom characters to remove. For example, to remove ) and ( from telephone numbers like (863) 555 0140, enter () in the Remove other characters field.
Remove Prefix Removes characters only when they appear at the beginning of values. For example, you might remove "MR " from names like MR SMITH.
Remove Suffix Removes characters only when they appear at the end of data. For example, you might remove " Esq" from Andrew SMITH Esq.
Replace from Substitution File Replaces specific sequences of characters with other sequences of characters. For example, if your data contains marital status codes, you can replace them with words: S=Single, M=Married, D=Divorced.
  • The substitution file can have the extension .csv, .tsv, or .txt. The file contains pairs of character sequences on each line.
  • Each pair consists of a sequence of characters to find in values, a separator, and a sequence of characters to replace the found characters with. The separator can be a tab, a comma (,), an equals sign (=), or a colon (:).
  • To use a separator character as part of a character sequence, enclose it in double quotation marks (") or precede it with a backslash (\). To make the Analyst's Notebook disregard text in the file (to include a comment, for example), precede it with a semicolon (;).
Replace Value Replaces a whole value with another value.
Suffix with Another Column Adds the value from another column to the end of the value in the selected column. For example, you can suffix date values with time values to create a combined date and time column. You can also specify a separator character to use between the values.
Trim Characters Tabs and spaces are trimmed by default. You can trim other unwanted characters from the beginnings and ends of values. For example, to trim { and } from {Jelicos Restaurant}, click Trim other characters listed below: and enter {} in the field. If you modify the default selections, or specify other characters to trim, the cogs icon is displayed in the column header. Import specifications created in previous versions do not have these default trim actions applied.