User Tools

Site Tools


transformations:splitdelimitedtext

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

SettingDescription
ColumnSelect the column containing the delimited text to be split.
SeparatorSelect 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.
DecimalSelect the character to represent the decimal point for columns determined to be containing numeric values, after splitting. Options: Point or Comma.
Ignore quoting When selected, delimiting characters contained within paired quotation marks will be used for splitting text.
Keep original columnWhen selected, the source column of the text to be split is kept in the dataset.
ColumnsFor 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 seperatorFor any "overflow" text that falls after the last column defined above, determine how it will be handled.
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.


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:

Year Month Day
2017May 12
2018Sep 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:

City/town State/Province Country Continent
Mississauga Ontario Canada North America
Cambridge Massachusetts United States North America


See also

transformations/splitdelimitedtext.txt · Last modified: 2021/04/21 15:34 by craigt