Differences

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

--- transformations:convert [2018/01/23 00:05] – [Everything to text] dmitry
+++ transformations:convert [2023/10/05 17:01] (current) – [Text to Date settings] dmitry
@@ Line 1: / Line 1: @@
-===== Convert Data Types =====
+{{ transformations:ConvertDataTypesAction.png}}
+====== CONVERT DATA TYPE ======
-This transformation converts values of one type (e.g. text) to another type (e.g. number). There are 3 modes:
+Category: Transform / Basic\\
+\\
+=====Description=====
+This action converts values of one type (e.g. text) to another type (e.g. number). There are 4 modes:\\
+\\
 ====Text to date====
-In this mode text values that look like a date are converted into number dates. A date format must be specified. All text values that match the specified format are converted to number dates. For instance:
+In this mode, text values that look like a date are converted into number dates. A date format must be specified. All text values that match the specified format are converted to number dates.
+The format notation is the same as for the [[syntax:functions:format|format()]] function.\\
-Format: M/d/yyyy
+\\
+====Text to number====
+In this mode, text values that look like a number are converted into numbers.\\
-^  Before (text)  ^  After ^ Comment ^
+\\
-| 12/31/2017  |   43100 | Corresponds to December 31st, 2017. |
+====Everything to text====
-| 12-31-2017  | 12-31-2017  | No conversion happened as the text value didn't match the specified format. |
+In this mode, text values remain unchanged and non-text values are converted into text. Numbers are formatted as per the specified format.\\
-The format notation is the same as for the [[syntax:functions:format|format()]] function.
+\\
+====Empty to text or a number====
+In this mode, empty values are replaced with the static text or number value specified.\\
-====Text to number====
+\\
-In this mode text values that look like a number are converted into numbers. For instance:
+=====Use cases=====
+  * Convert text-based dates to numeric date values for use in date-based calculations downstream.
+  * Convert numbers originally captured as text to true numeric values.
+  * Recode "blank" values as specific text or number values.
-Decimal separator: Point
+\\
-^  Before (text)  ^  After ^ Comment ^
+=====Action settings=====
-| 1000  |   1000 |   |
+^Setting^Description^
-| 1,000  | 1,000 | No conversion happened as the text value has different decimal separator. |
+|Columns to convert|Select the column(s) from the dataset to apply the value conversion to.|
-| 10.00  |    10 |   |
+|Conversion|Select the conversion mode to use.  Options:  //Text to Date//, //Text to Number//, //Everything to Text//, and //Empty to text or number//.|
-| $1000  | $1000 | No conversion happened as the text value is not recognized as a number. |
+|If conversion fails|Select how EasyMorph will respond when the selected conversion type cannot be applied to values.\\  Options:  //Keep original value// (the original, unmodified value is kept), //Generate error// (an error message is inserted\\ into the cells where conversion failed), or //Halt execution// (the action will display an error flag).|
+\\
+====Text to Date settings====
+^Setting^Description^
+|Format<sup>*</sup>|Enter the date format the current text dates are using (e.g. yyyy-MM-dd).  Use the //Detect// button to populate this field with the date format found within the column. If the auto-detection didn't work, compose the format string manually using the table below.|
+<sup>*</sup> Setting can be specified using a [[:parameters|parameter]].\\
+\\
+==== Date-Time format string components ====
+A //format string// defines a formatting template for dates and times. Its components can be put together in any combination. If a character doesn't belong to a format component then it's inserted verbatim.
-====Everything to text====
+:!: The specifiers below are case-sensitive.
-In this mode text values remain unchanged and non-text values are converted into text. Numbers are formatted as per the specified format. For instance:
+^  Date format specifier^  Description  ^
+|  d  |The day of the month, from 1 through 31.  |
+|  dd  |The day of the month, from 01 through 31.  |
+|  ddd  |The abbreviated name of the day of the week.  |
+|  M  |The month, from 1 through 12.  |
+|  MM  |The month, from 01 through 12.  |
+|  MMM  |The abbreviated name of the month.  |
+|  MMMM  |The full name of the month.  |
+|  y  |The year, from 0 to 99.  |
+|  yy  |The year, from 00 to 99.  |
+|  yyyy  |The year as a four-digit number.  |
+^  Time format specifier^  Description  ^
+|  h  |The hour, using a 12-hour clock from 1 to 12.  |
+|  hh  |The hour, using a 12-hour clock from 01 to 12.  |
+|  H  |The hour, using a 24-hour clock from 0 to 23.  |
+|  HH  |The hour, using a 24-hour clock from 00 to 23.  |
+|  m  |The minute, from 0 through 59.  |
+|  mm  |The minute, from 00 through 59.  |
+|  s  |The second, from 0 through 59.  |
+|  ss  |The second, from 00 through 59.  |
+|  t  |The first character of the AM/PM designator.  |
+|  tt  |The AM/PM designator.  |
+\\
+====Text to Number settings====
+^Setting^Description^
+|Decimal separator|Select what character is used to define decimal places in the source text values.  Options:  //Point// or //Comma//.|
+|Thousand separator|Select what character is used to define thousands places in the source text values.  Options:  //Default//, //Comma//,\\ //Point// or //Space//.|
+\\
+====Everything to Text settings====
+^Setting^Description^
+|Number format|Select the output format for the converted text values (e.g., selecting "100%" will multiply numbers by 100 and append "%").|
+\\
+====Empty to text or number settings====
+^Setting^Description^
+|Value<sup>*</sup>|Enter a static text or numeric value to convert empty cells to.|
+<sup>*</sup> Setting can be specified using a [[:parameters|parameter]].\\
+\\
+=====Examples=====
+**Text to date example:**
+>  Format: M/d/yyyy
+^Before (text)  ^After ^Comment ^
+|12/31/2017 |  43100 | Corresponds to December 31st, 2017. |
+|12-31-2017 |12-31-2017  | No conversion happened as the text value didn't match the specified format. |
+\\
+**Text to number example:**
+>  Decimal separator: Point
+^Before (text)  ^After ^Comment ^
+| 1000  |  1000 |   |
+| 1,000  |1,000 | No conversion happened as the text value has different decimal separator. |
+| 10.00  |  10 |   |
+| $1000  |$1000 | No conversion happened as the text value is not recognized as a number. |
+\\
+**Everything to text example:**
+^Format  ^Before ^After ^
+|  100%  |  10|1000% |
+|  1'000.00  |  5500 |5,500.00 |
+|  yyyy/MM/dd  |  22335 |1961/02/23 |
-Number format: 100%
+\\
+====Community examples====
+  * [[https://community.easymorph.com/t//1587/7|How to format the duration in more than 24h]] ([[https://community.easymorph.com/uploads/short-url/w6CnxwiidYjcJHNRrmUIXI74NC2.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Table 1//; Action position: //3//)
+  * [[https://community.easymorph.com/t//2166/2|Date condition for specific dates]] ([[https://community.easymorph.com/uploads/short-url/hEAQZ5dAEMQk2xUZVJucAo2kJTz.morph|Project]]; Module: //Main//; Group: //Group 1//; Table: //TR101.xlsx//; Action position: //2//)
+  * [[https://community.easymorph.com/t//2292/2|Assign value from conditions based on a table]] ([[https://community.easymorph.com/uploads/short-url/hsFachkdfbcKFoA2ZK8WcmOUfA9.morph|Project]]; Module: //Main//; Group: //Group 1//; Table: //Table 1//; Action position: //4//)
+  * [[https://community.easymorph.com/t//2297/2|Excel: Import Date Headings as number]] ([[https://community.easymorph.com/uploads/short-url/ppgiXxeiQbK0O3owwca3pxAsOyH.morph|Project]]; Module: //Main//; Group: //Group 1//; Table: //New header//; Action position: //3//)
-^  Before  ^  After (text) ^ Comment ^
+\\
-|    10  | 1000% |   |
+=====Shortcuts=====
-| $1000  | $1000 | No conversion happened as the value is not a number. |
+Fast ways to create the action:\\
-| ABcd   | ABcd  | No conversion happened as the value is not a number. |
+  *Right-click on a column heading, and select "Convert data type", then select the conversion mode from the submenu.
-|  TRUE  | TRUE  | Booleans are converted to text.  |
+  *Click the drop-down arrow in a column heading and select "Convert data type", then select the conversion mode.
-|#Error  | Error | Errors can be converted into text too.  |
-|  43100 | 4310000%  | This value is probably a date (#2017-12-31) but since the specified number format is percentage the value was converted into percentage formatted text. A date format should've been used.  |
-**See also**
+\\
+=====See also=====
   * [[syntax:types|Type system in EasyMorph]]
   * [[syntax:functions:astext]]
   * [[syntax:functions:asnumber]]
   * [[syntax:functions:format]]