EasyMorph Help

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
{{ transformations:ConvertDataTypesAction.png}}
====== CONVERT DATA TYPE ======
Category: Transform / Basic\\

\\ 
=====Description=====
This action converts values of one type to another (Text to date, Text to number, Everything to text, Empty to text, or a number).\\
\\ 

=====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.

\\ 
=====Action settings=====
^Setting^Description^
|Columns to convert|Select the column(s) from the dataset to apply the value conversion to.|
|Conversion|Select the conversion mode to use.  Options:  //Text to Date//, //Text to Number//, //Everything to Text//, and //Empty to text or 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====
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.\\
^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.

:!: The specifiers below are case-sensitive.
^  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====
In this mode, text values that look like a number are converted into numbers.\\
^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====
In this mode, text values remain unchanged, and non-text values are converted into text. Numbers are formatted as per the specified format.\\
^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====
In this mode, empty values are replaced with the static text or number value specified.\\
^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]].\\

\\
=====Remarks=====
When using the //Text to number// mode, and choosing the Decimal separator and Thousand separator settings, be careful of values from countries using commas (,) as decimal delimiters and/or points (.) as thousands delimiters.

\\ 
=====Examples=====

====Example #1====
>Convert text values in the "Date" to date values. (Text to date example)

===Before (source table)===
^Date  ^Salesperson ^Daily Sales  ^
|12/31/2017 |Sam  |  8900| 
|12/31/2017 |Oksana  |  5750| 
|01/01/2017 |Marisol  |  7200| 

===After (result table)===
^Date  ^Salesperson ^Daily Sales  ^
|  **43100**|Sam  |  8900| 
|  **43100** |Oksana  |  5750| 
|  **42736** |Marisol  |  7200| 

===Action parameters===
>Columns to convert: Date
>Convert: Text to Date
>Input format:MM/dd/yyyy
>If conversion fails: Keep original value

\\
====Example #2====
>Convert the text values in "Daily Sales" to numeric values. (Text to number example)

===Before (source table)===
^Date  ^Salesperson ^Daily Sales  ^
|12/31/2017 |Sam  |$8900.00  | 
|12-31-2017 |Oksana  |5750.80  | 
|1/1/2017 |Marisol  |7,200  | 

===After (result table)===
^Date  ^Salesperson ^Daily Sales  ^
|12/31/2017 |Sam  |**$8900.00**  | 
|12-31-2017 |Oksana  |  **5750.80**| 
|1/1/2017 |Marisol  |  **7200**| 

===Action parameters===
>Columns to convert: Daily sales
>Convert: Text to Number
>Decimal separator: Point
>Thousand separator: Default
>If conversion fails: Keep original value

\\
===Example #3===
>Convert "Sales Goal Pct" values to text.  (Everything to text example).

===Before (source table)===
^Salesperson ^Sales  ^Sales Goal Pct ^
|Sam  |  8900.30|  0.18|
|Oksana  |  5750.80|  0.2525| 
|Marisol  |  7200.00|  1|

===After (result table)===
^Salesperson ^Sales  ^Sales Goal Pct ^
|Sam  |  8900.30|**18.00%**  |
|Oksana  |  5750.80|**25.25%**  | 
|Marisol  |  7200.00|**100.00%**  |

===Action parameters===
>Columns to convert: Sales Goal Pct
>Convert: Everything to Text
>Number format: 100.00%
>If conversion fails: Keep original value


\\ 
=====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//)

\\ 
=====Shortcuts=====
Fast ways to create the action:\\
  *Right-click on a column heading, and select "Convert data type", then select the conversion mode from the submenu.
  *Click the drop-down arrow in a column heading and select "Convert data type", then select the conversion mode.

\\ 
=====See also=====
  * [[syntax:types|Type system in EasyMorph]]
  * [[syntax:functions:astext]]
  * [[syntax:functions:asnumber]]
  * [[syntax:functions:format]]