Magic ETL Tiles: Text

​

Intro

• Combine Columns tile
• Replace Text tile
• Split Column tile
• String Operations tile
• Text Formatting tile

​

Combine Columns Tile

1. Click the Combine Columns tile in the canvas.
2. (Optional) Rename the tile by clicking , then entering the name you want.
3. Enter the name of the new column.
4. Specify the character to use to separate the combined values.
5. (Optional) Remove the original columns after they are combined.
6. Select the columns to combine.

​

Replace Text Tile

1. Click the Replace Text tile in the canvas.
2. (Optional) Rename the tile by clicking , then entering the name you want.
3. Select the column to search in.
4. Enter the text value you want to find. For example, “Jumbo Box”.
5. (Option) Specify the find settings to use by clicking in the field.

   Option	Description
   Whole words	Searches for whole words.
   Case sensitive	Searches with case-sensitive values.
   Use RegEx	Searches and replaces using Java regular expressions.

6. Enter the text value you want to replace with. For example, “Ginormous”.
   Tip: You can preview the data transformed by a tile by running a preview, clicking the action in the canvas, then clicking the Preview tab.

​

Split Column Tile

1. Click the Split Column tile in the canvas.
2. (Optional) Rename the tile by clicking , then entering the name you want.
3. Select the column you want to split.
4. Choose the delimiter on which the string should be separated.
5. Next, decide whether to keep the extra splits in the last column specified in step 4. For example, in row 1 in the table above, the extra split Ben exists in the last column Watcher Split 1.
6. Lastly, add and name as many columns as needed. In the example above, you can see there are some rows that did not have 3 values to split, so those values remain blank in those columns.
   Note: If you have more values to split than columns created and choose to not keep the extra splits, the values are not placed in any column.

​

String Operations Tile

1. Click the String Operations tile in the canvas.
2. (Optional) Rename the tile by clicking , then entering the name you want.
3. Type the name of your new column.
4. Then, choose the type of operation you want to perform.
5. Select the column you want to perform this action on.
6. Lastly, specify the beginning and ending character position you want from your string.
7. (Optional) If you want to add another column, click Add String Operation .
   Tip: You can preview the data transformed by a tile by running a preview, clicking the tile in the canvas, then clicking the Preview tab.

​

Text Formatting Tile

1. Click the Text Formatting tile in the canvas.
2. (Optional) Rename the tile by clicking , then entering the name you want.
3. For each column you want to format, do the following:
   1. Select the text column you want.
   2. Select the type of letter-case format you want.
   3. Select whether to remove numbers or remove everything except numbers.
4. (Conditional) If you want to add another column, click Add Column .
   Tip: You can preview the data transformed by a tile by running a preview, clicking the tile in the canvas, then clicking the Preview tab.