User Tools

Site Tools


transformations:convert

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
transformations:convert [2018/01/23 00:05] – [Everything to text] dmitrytransformations:convert [2025/01/15 17:05] (current) craigt
Line 1: Line 1:
-===== Convert Data Types =====+{{ transformations:ConvertDataTypesAction.png}} 
 +====== CONVERT DATA TYPE ====== 
 +Category: Transform / Basic\\
  
-This transformation converts values of one type (e.g. textto another type (e.g. number). There are 3 modes:+\\  
 +=====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).\\ 
 +\\ 
  
-====Text to date==== +=====Use cases===== 
-In this mode text values that look like a date are converted into number datesA date format must be specified. All text values that match the specified format are converted to number datesFor instance:+  * 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.
  
-FormatM/d/yyyy+\\  
 +=====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]].\\ 
 +\\ 
  
-^  Before (text)  ^  After ^ Comment ^ +==== Date-Time format string components ==== 
-| 12/31/2017  |   43100 | Corresponds to December 31st, 2017+//format string// defines a formatting template for dates and timesIts components can be put together in any combination. If a character doesn'belong to a format component then it's inserted verbatim.
-| 12-31-2017  | 12-31-2017  | No conversion happened as the text value didn'match the specified format. |+
  
-The format notation is the same as for the [[syntax:functions:format|format()]] function.+:!: 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.  |
  
-====Text to number==== +^  Time format specifier^  Description 
-In this mode text values that look like number are converted into numbersFor instance:+|  h  |The hour, using a 12-hour clock from 1 to 12.  | 
 +|  hh  |The hour, using 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 |
  
-Decimal separator: Point +\\  
-^  Before (text)   After Comment +====Text to Number settings==== 
-1000    1000 |   +In this mode, text values that look like a number are converted into numbers.\\ 
-1,000  1,000 | No conversion happened as the text value has different decimal separator. | +^Setting^Description
-| 10.00  |    10 |   | +|Decimal separator|Select what character is used to define decimal places in the source text values.  Options:  //Point// or //Comma//.
-$1000  $1000 No conversion happened as the text value is not recognized as number. |+|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 textNumbers 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 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.
  
-====Everything to text==== +\\  
-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:+=====Examples=====
  
-Number format: 100%+====Example #1==== 
 +>Convert text values in the "Date" to date values. (Text to date example)
  
-^  Before  ^  After (text) ^ Comment +===Before (source table)=== 
-   10  | 1000% |   | +^Date  ^Salesperson ^Daily Sales  ^ 
-$1000  $1000 | No conversion happened as the value is not a number. | +|12/31/2017 |Sam  |  8900|  
-| ABcd   | ABcd  | No conversion happened as the value is not a number. +|12/31/2017 |Oksana  |  5750|  
- TRUE  TRUE  Booleans are converted to text.  | +|01/01/2017 |Marisol  |  7200
-|#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**+===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:types|Type system in EasyMorph]]
   * [[syntax:functions:astext]]   * [[syntax:functions:astext]]
   * [[syntax:functions:asnumber]]   * [[syntax:functions:asnumber]]
   * [[syntax:functions:format]]   * [[syntax:functions:format]]
transformations/convert.1516683909.txt.gz · Last modified: 2018/01/23 00:05 by dmitry

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki