Differences

This shows you the differences between two versions of the page.

--- transformations:splitdelimitedtext [2021/04/20 12:50] – craigt
+++ transformations:splitdelimitedtext [2021/08/14 21:32] – [Action settings] craigt
@@ Line 18: / Line 18: @@
 |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 [#] seperator|For 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)|
+|Text beyond N<sup>th</sup> 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=====
-It can be difficult to know how many columns will be needed to capture all of the split values, especially with large values.  You can use either a formula in a [[transformations:addcolumn|Calculate new column(s)]] action, or use multiple [[transformations:addcolumn|Calculate new column(s)]] to:
+After splitting, values that are interpreted as numeric by EasyMorph will be converted to the numeric format.
-  - Calculate the full length of the source column values,
-  - Remove the delimiters using either a formula ("Replace") or action,
-  - Calculate the length of the modified values,
-  - Subtract the modified length from the source length to determine the number of delimiters in the source text,
-  - Find the maximum value across all rows (using Analysis View, column profile/filter, or [[transformations:metadata|Table metadata]]), and add one.
-\\
+Two adjacent delimiters will result in an empty value in the new column.\\
-If you only need to capture values to a specific point, insert enough columns to capture the data you need, and either let the rest drop ("Ignore" text beyond [#] separator) or capture the overflow for possible later use ("New column" for text beyond the [#] separator).
+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.\\
 \\
@@ Line 50: / Line 49: @@
 \\
-**Result:**
+**Result table:**
 ^ Year ^ Month ^ Day ^
 | 2017|May | 12|
@@ Line 68: / Line 67: @@
 \\
-**Result:**
+**Result table:**
 ^ City/town ^ State/Province ^ Country ^ Continent ^
 | Mississauga | Ontario        | Canada         | North America  |
 | Cambridge   | Massachusetts  | United States  | North America  |
+\\
+====Community examples====
+  * [[https://community.easymorph.com/t//1351/1|How to order columns by a list of columns]] ([[https://community.easymorph.com/uploads/short-url/bKeuXUgwKo1alDrA03LY5z3tPJv.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Sample data//; Action position: //2//)
+  * [[https://community.easymorph.com/t//1652/2|Which technique for grouping many columns of data together (i.e. 50+ columns)]] ([[https://community.easymorph.com/uploads/short-url/ub3zV5WsGF4rWqJQMb7oEtPReqy.morph|Project]]; Module: //Main//; Group: //Tab 1//;\\ Table: //Sample data//; Action position: //2//)
+  * [[https://community.easymorph.com/t//1869/6|Coverting Cell to Separate Columns]] ([[https://community.easymorph.com/uploads/short-url/ktZECKXUuEJAEq0fobEmmoXzsGu.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Parse formulas//; Action position: //4//)
+  * [[https://community.easymorph.com/t//1926/3|Sort column in an export alphabetically (tab)]] ([[https://community.easymorph.com/uploads/short-url/b1cFUp2TMl0yEassYLlT4oO3hxp.morph|Project]]; Module: //Main//; Group: //Enum/pivot/append//; Table: //Morph test recive file 2(2)//; Action position: //3//)
+  * [[https://community.easymorph.com/t//2580/6|Parsing Exceptionally Complex Column]] ([[https://community.easymorph.com/uploads/short-url/18twYeVICxpKyR13IU4TQn2MNnm.morph|Project]]; Module: //Main//; Group: //Group 1//; Table: //Table 2 (parsed)//; Action position: //3//)
 \\