Both sides previous revisionPrevious revisionNext revision | Previous revision |
transformations:splitdelimitedtext [2021/04/20 12:50] – craigt | transformations:splitdelimitedtext [2023/10/11 20:53] (current) – [Remarks] craigt |
---|
|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//. | | |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//.| | |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. | | |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. | | |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. | | |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===== | =====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.\\ |
| |
| **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). |
| |
\\ | \\ |
| |
\\ | \\ |
**Result:** | **Result table:** |
^ Year ^ Month ^ Day ^ | ^ Year ^ Month ^ Day ^ |
| 2017|May | 12| | | 2017|May | 12| |
| |
\\ | \\ |
**Result:** | **Result table:** |
^ City/town ^ State/Province ^ Country ^ Continent ^ | ^ City/town ^ State/Province ^ Country ^ Continent ^ |
| Mississauga | Ontario | Canada | North America | | | Mississauga | Ontario | Canada | North America | |
| Cambridge | Massachusetts | United States | 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//) |
| |
\\ | \\ |