Table of Contents
SPLIT DELIMITED TEXT INTO COLUMNS
Category: Transform / Advanced
Description
This action splits text values that are separated by delimiting characters into multiple columns.
Use cases
Use this action on any text-based data files using non-standard delimiters (e.g. pipe, tilde) that Easymorph doesn't automatically detect, and imports all values into a single column.
Action settings
| Setting | Description |
|---|---|
| Column | Select the column containing the delimited text to be split. |
| Separator | Select the delimiting character to split the text by. Options: Comma, Semicolon, Tab, Pipe, Tilde, Space, Custom (and select the character from the dialog that displays), or No separator. |
| Decimal | Select the character to represent the decimal point for columns determined to be containing numeric values, after splitting. Options: Point or Comma. |
| Thousand | Select the character to represent thousands places for columns determined to be containing numeric values, after splitting. Options: Default, Comma, Point or Space. |
| Ignore quoting | When selected, delimiting characters contained within paired quotation marks will be used for splitting text. |
| Keep original column | When selected, the source column of the text to be split is kept in the dataset. |
| Columns | For each column of data you expect there to be, based on the number of delimiters within the source text, create a column, and provide a column name, to hold that portion of the data. |
| Text beyond Nth seperator | Determine how to handle any "overflow" text that falls after the last column defined above. Options: Ignore (overflow text is dropped) or New column (enter the column name to hold the remaining overflow text) |
Remarks
After splitting, values that are interpreted as numeric by EasyMorph will be converted to the numeric format.
Two adjacent delimiters will result in an empty value in the new column.
in cases where more columns have been defined than there are delimiters in the source value, the extra columns will be empty.
Leading and trailing whitespace are automatically removed from values when the new columns are created.
Import 2 to 3 times faster (experimental feature). This feature affects the "Split delimited text…" actions as well. To enable this feature, check on the "Twice faster CSV import" option in Application Settings (in the About toolbar).
Examples
Example 1: Separate the following input into individual columns based on the "-" (dash) character.
Input table:
| Date |
|---|
| 2017-May-12 |
| 2018-Sep-30 |
Action parameters:
Delimiter: "-" (dash)(Custom character 2d)
Result table:
| Year | Month | Day |
|---|---|---|
| 2017 | May | 12 |
| 2018 | Sep | 30 |
Example 2: Separate the following input into individual columns based on the "line break" character.
Input table:
| Location |
|---|
| Mississauga Ontario Canada North America |
| Cambridge Massachusetts United States North America |
Action parameters:
Delimiter: line break (Custom character 10)
Result table:
| City/town | State/Province | Country | Continent |
|---|---|---|---|
| Mississauga | Ontario | Canada | North America |
| Cambridge | Massachusetts | United States | North America |
Community examples
- How to order columns by a list of columns (Project; Module: Main; Group: Tab 1; Table: Sample data; Action position: 2)
- Which technique for grouping many columns of data together (i.e. 50+ columns) (Project; Module: Main; Group: Tab 1;
Table: Sample data; Action position: 2) - Coverting Cell to Separate Columns (Project; Module: Main; Group: Tab 1; Table: Parse formulas; Action position: 4)
- Sort column in an export alphabetically (tab) (Project; Module: Main; Group: Enum/pivot/append; Table: Morph test recive file 2(2); Action position: 3)
- Parsing Exceptionally Complex Column (Project; Module: Main; Group: Group 1; Table: Table 2 (parsed); Action position: 3)