Differences

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

--- transformations:subfield [2018/07/03 20:49] – dmitry
+++ transformations:subfield [2023/10/11 20:54] (current) – [Remarks] craigt
@@ Line 1: / Line 1: @@
-===== Split delimited text into rows =====
+{{ transformations:SubfieldAction.png}}
+====== SPLIT DELIMITED TEXT INTO ROWS ======
+Category: Transform / Advanced \\
-Split column values delimited with a separator (e.g. comma) into multiple rows -- one row per value. Values in other columns are duplicates for the new rows.
+\\
+=====Description=====
+This action splits column values delimited with a separator (e.g. comma) into multiple rows -- one row per value.
-For instance, a value with 3 words delimited with semicolons will be split into three rows:
+Values in other columns are duplicated in the new rows.\\
-^Row no ^Numbers ^
+\\
-| 1|one;two;three |
+=====Use cases=====
+This action can be useful in cases where individual values are concatenated, with a delimiter, in a single column that should be broken out into individual records.
-Delimiter: Semicolon
+\\
+=====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//,//Line break//, and\\ //Custom// (and select the character from the dialog that displays). |
+|Ignore quoting |When selected, delimiting characters contained within paired quotation marks will be used for splitting text. |
+\\
+=====Remarks=====
+Unlike the [[transformations:splitdelimitedtext|Split delimited text into columns]] action, in which you have to define the new columns to receive the split values, this action will create as many new rows as necessary to output all split values in the selected column.
+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:** A value with 3 words, delimited with semicolons, will be split into three rows:\\
+**Source table:**
+^Row no ^Numbers ^
+| 1|one;two;three |
+\\
+**Action parameters:**
+> Column: "Numbers"
+> Delimiter: Semicolon
+\\
+**Result table:**
 ^ Row no ^ Numbers ^
 | 1|one   |
 | 1|two   |
 | 1|three |
-Another example:
-A column with multi-line text values.
+\\ \\
+**Example 2:** A column with multi-line text values.\\
+**Source table:**
 ^ Location No ^ Location ^
 | 1| Mississauga \\ Ontario \\ Canada \\ North America |
 | 2| Cambridge \\ Massachusetts \\ United States \\ North America |
+\\
-Delimiter: Line break
+**Action parameters:**
+> Column: "Location"
+> Delimiter: Line break
+\\
+**Result table:**
 ^ Location No ^ Location ^
 | 1| Mississauga    |
@@ Line 34: / Line 70: @@
 | 2| North America  |
-===See also===
+\\ \\
+**Example 3:** Split out Order IDs into rows to create unique Customer-Order ID records.\\
+**Source table:**
+^Customer ID^Order IDs^
+|CUST001  |ORD001, ORD005, ORD010  |
+|CUST002  |ORD002, ORD003, ORD009  |
+|CUST003  |ORD004, ORD006, ORD007, ORD008  |
+\\
+**Action parameters:**
+> Column: "Order IDs"
+> Separator: Comma
+\\
+**Result table:**
+^Customer ID^Order IDs^
+|CUST001  |ORD001  |
+|CUST001  |ORD005  |
+|CUST001  |ORD010  |
+|CUST002  |ORD002  |
+|CUST002  |ORD003  |
+|CUST002  |ORD009  |
+|CUST003  |ORD004  |
+|CUST003  |ORD006  |
+|CUST003  |ORD007  |
+|CUST003  |ORD008  |
+\\
+====Community examples====
+  * [[https://community.easymorph.com/t//1268/4|Parsing JSON/XML from web responses]] ([[https://community.easymorph.com/uploads/short-url/6ftyNHFkbIwRqPFtqQtKHHBu3jl.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Table 1//; Action position: //3//)
+  * [[https://community.easymorph.com/t//1332/3|String manipulation (REVERSE)]] ([[https://community.easymorph.com/uploads/short-url/67c52KIJt323kknw2Q4vOvlAJ05.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Using actions//; Action position: //3//)
+  * [[https://community.easymorph.com/t//1803/3|Create dynamic list from delimited text (dynamic split/dynamic generate list)]] ([[https://community.easymorph.com/uploads/short-url/vd59gCPQiP8m4hiFPZmYiRDKW31.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Output//; Action position: //4//)
+  * [[https://community.easymorph.com/t//1882/3|Transforming Single Row of Data from CSV into Multiple Rows]] ([[https://community.easymorph.com/uploads/short-url/mEw4PYroNSO8QmJx0ykBvIuP6Ns.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Table 1//; Action position: //2//)
+\\
+=====See also=====
+  * [[transformations:importtext|Import delimited text file]]
   * [[transformations:splitdelimitedtext|Split delimited text into columns]]
-  * [[transformations:splitfixedwidthtext|Split fixed width text into columns]]