Skip to main content

Convert column type

Supported Options

The following options are supported by convert column operation.

  1. Convert: Specify the column you want to convert.
  2. To type: The type to convert to (Numeric/Text/Date)
  3. Current Format: This appears only when converting to date. You can specify the format which Mammoth should use when converting to date. This is automatically detected. Set this manually only if auto detection is unsuccessful. The format for doing this is explained down below
  4. Convert more columns: To add more columns to be converted into this rule.
note
  • If the column conversion is unsuccessful for a given set of values, it will be set to NULL.
  • If you would like to keep the original column and , you can copy the column before
  • Converting a date to number yields UNIX timestamps.

Format Guide (Text/Numeric to Date)

If you want to convert a text or numeric column into date, you can use the following strings to match the specific date components.

PatternDescription
HHhour of day (01-12)
HH12hour of day (01-12)
HH24hour of day (00-23)
MIminute (00-59)
SSsecond (00-59)
AM, am, PM or pmmeridiem indicator (without periods)
A.M., a.m., P.M. or p.m.meridiem indicator (with periods)
Y,YYYyear (4 or more digits) with comma
YYYYyear (4 or more digits)
YYYlast 3 digits of year
YYlast 2 digits of year
Ylast digit of year
BC, bc, AD or adera indicator (without periods)
B.C., b.c., A.D. or a.d.era indicator (with periods)
MONTHfull upper case month name
Monthfull capitalized month name
monthfull lower case month name
MONabbreviated upper case month name
Monabbreviated capitalized month name
monabbreviated lower case month name
MMmonth number (01-12)
DAYfull upper case day name
Dayfull capitalized day name
dayfull lower case day name
DYabbreviated upper case day name
Dyabbreviated capitalized day name
dyabbreviated lower case day name
DDday of month (01-31)
TZupper case time-zone abbreviation
OFtime-zone offset from UTC
"T", "Z"Letters T, Z
UNIX TIMESTAMPTreat as UNIX timestamp
note
  • The system will auto detect dates for the most popular date formats. In most cases you will never have to manually enter the format for the date. If your format is not getting detected, contact the Mammoth support team.
  • Dates can be partially matched when doing column conversion. For example, you can match only "2012-01-31" part of text "2012-01-31something" by using the format "YYYY-MM-DD". The system ignores the word something while doing converting the date.
  • If the dates are incomplete, you can use combine column column task to add the part of the date that is missing before converting to column.

Format Guide (Text to Numeric)

When converting a text column into numeric, following points need to be considered.

  • Sign of the number will be retained, if it is a negative number.
  • If a currency symbol (supported in mammoth) or a percentage symbol is found at a proper place, it will be trimmed off and number will be retained.
  • A negative number can also have a percentage symbol and thus will not make the number in-valid.
  • If commas (inserted at proper places) are found, those will be trimmed off and number will be retained.
  • Any in-valid characters (e.g. any character other than currency, percentage, comma or a decimal point, more than one currency symbols or decimals points) will make the number in-valid and therefore will result in an empty value.

For better clarification, following table depicts text to numeric mapping examples.

Text (Source)Numeric (Converted)
-35-35
$232.33232.33
$$232.33Empty
5.54%5.54
5.54%%Empty
-5.54%-5.54
12,345,123.0112345123.01
120,345,12.01Empty
1.2.3.4Empty
11
abc123Empty