Available actions

If the data that you are trying to import does not match the field structure of your database, you can add steps to transform the structure in the import specification. The actions that you can select are described.

Action Description
Add Prefix Adds text or values immediately before the import data. For example, Area Code: 01234, Area Code: 01235, Area Code: 01236 where the prefix is "Area Code: ".

To add a prefix to the values imported for a field, enter the text or values in the Prefix to be added before text box together with any additional spaces. For example, adding a prefix to a field that imports a date of birth of an entity, a prefix "DOB: " might be specified to import a date of birth in the format DOB: 20/11/58.
Add Suffix Adds text or values immediately after the import data. For example, ABC Inc., BCD Inc., CDE Inc where Inc. is the suffix.

To add a suffix to the values imported for a field, enter the text or values in the Suffix to be added after text box together with any additional spaces. For example, adding a suffix to a field that imports a currency amount, a suffix of "USD" might be specified to import a currency value in the format 12383478 USD.
Compress Repeated Characters Replaces repeated characters with a single instance of the character. If you are creating the source text files automatically using products such as spreadsheets or databases, you might find that they insert unnecessary characters into the data file. For example, if a field is defined to be 30 characters long and contains text that is 18 characters long, the text is printed followed by 12 space characters.

To complete the settings for an action to compress unwanted, repeated characters, specify the characters that you want to compress. If you compress alphabetic characters, you can also turn on Ignore case of characters to compress both uppercase and lowercase instances of these characters.
Extract Portion of Text Extracts a specific part of the data. For example, you might have a log of telephone calls of which only some have an area code, and you want to extract all the telephone numbers and not the area codes.
To extract a specific portion of text or data from a field, complete both the First Character and Last Character settings:
  • First Character - The number of the first character to use. Select either From start or From end to specify whether to count from the start or end of the numbered characters in the field.
  • Last Character - The number of the last character to use. Select either From start or From end to specify whether to count from the start or end of the numbered characters in the field.
For example, in a list of telephone numbers, you might want to use the last six characters of the telephone number. Telephone numbers might appear as follows in the imported file:

01234 567890

0234567890

441234 567890

567890

If you set the following conditions:
  • First character: 6 (from end)
  • Last character: 1 (from end)
You create an entity with the identity: 567890.
Find and Replace Text

Replaces text with different text. For example, you might remove ordinal suffixes (st, nd, rd, and th) from English-language dates by adding an action for each suffix that replaces it with an empty string.

Note: You might need to add extra actions to prevent (for example) the action for st also removing the last two letters of August, perhaps by replacing August with 08 first.
To complete the settings:
  1. Type the text string in the Match value box for the text that you want to replace.
  2. Turn on Ignore case if the value you want to replace appears in both lowercase and uppercase.
  3. Type the text string that you want to use as the replacement text in the Replace with box.
Prefix with Another Column Adds the values from another field immediately before the values imported from the selected field. For example, you can add a Date field to a Time field to import a combined date and time.
To complete the settings for an action to Prefix with Another Column:
  1. Select a field from the Select column to add list.
  2. Select the separator that you require to be inserted between the joined data columns. If you require a specific character or set of characters, for example " at " for [Date] at [Time], select Other separator and enter the characters and spaces in the adjacent box.
Remove Characters Removes unwanted characters such as spaces, tabs, or specific characters. For example, the characters () can be removed from (863) 555 0140.
To complete the settings for an action to remove unwanted characters, turn on the checkboxes of the characters that you want to remove. These are tab characters, space characters, or other specific characters that you enter in the box provided.
Note: If you have turned on Remove other characters listed below and have entered alphabetic characters, you can also turn on Ignore case of characters to remove both uppercase and lowercase instances of these characters.
Remove Prefix Removes unwanted text from the beginning of a field value. For example, MR(space) can be removed from MR SMITH.

To remove a prefix from the values imported for a field, type the text or values in the box provided together with any additional spaces.
Remove Suffix Removes text from the end of a field value. For example, "Esq" can be removed from Andrew SMITH Esq.

To remove a suffix from the values imported for a field, type the text or values in the box provided together with any additional spaces.
Replace from Substitution File Replaces a value in a field with values that are taken from a lookup table. The codes for marital status, for example, might be replaced with a term from a lookup table where S = Single, M = Married, D = Divorced.

To replace values in a field with values from a substitution file, a file must be created or available for selection.
Replace Value Replaces a value with another value that you specify. For example, you want to replace the detailed confidential information displayed in a field with the value CONFIDENTIAL.
To complete the settings to create a Replace Value action:
  1. Click the arrow on the Match value list, and select the data column value.
  2. Turn on Ignore case if the value you want to replace appears in both lowercase and uppercase.
  3. Enter the value that you want to use as the replacement value in the Replace with box.
Split Text Divides the content of a column into two parts at the first occurrence of the split character. For example, a Full Name field "Andrew Smith" might be split into two fields: Name1 "Andrew" and Name2 "Smith".
To split the text that appears within a column:
  1. Select the split character that you would like to use to divide the column. By default this uses a space, if you require a specific character, for example " | ", select Other and type the characters and spaces in the adjacent box.
  2. Choose which side of the split you would like to use.
Suffix with Another Column Adds the value from another field immediately after the value imported from the selected field. For example, you can add a Time field to a Date field to import a combined date and time.
To complete the settings for an action to Suffix with Another Column:
  1. Select a field from the Select column to add list.
  2. Select the separator that you require to be inserted between the joined data columns. If you require a specific character or set of characters, for example " at " for [Date] at [Time], select Other separator and type the characters and spaces in the adjacent box.
Trim Characters Trim unwanted characters from the beginning and end of a field, such as spaces, tabs or specific characters. For example the character " can be removed from " Jelico's Restaurant".
To complete the settings for an action to trim unwanted characters, turn on the checkboxes of the characters that you want to trim. These are tab characters, space characters, or other specific characters that you enter in the box provided.
Note: If you have turned on Trim other characters that are listed below and have entered alphabetic characters in the box provided, you can also turn on Ignore case of characters to trim both uppercase and lowercase instances of these characters.