{{ transformations:ConvertDataTypesAction.png}} ====== CONVERT DATA TYPE ====== 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. The format notation is the same as for the [[syntax:functions:format|format()]] function.\\ \\ ====Text to number==== In this mode, text values that look like a number are converted into numbers.\\ \\ ====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.\\ \\ ====Empty to text or a number==== In this mode, empty values are replaced with the static text or number value specified.\\ \\ =====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==== ^Setting^Description^ |Format*|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.| * 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==== ^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*|Enter a static text or numeric value to convert empty cells to.| * 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 | \\ ====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]]