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.

Pattern Description
HH hour of day (01-12)
HH12 hour of day (01-12)
HH24 hour of day (00-23)
MI minute (00-59)
SS second (00-59)
AM, am, PM or pm meridiem indicator (without periods)
A.M., a.m., P.M. or p.m. meridiem indicator (with periods)
Y,YYY year (4 or more digits) with comma
YYYY year (4 or more digits)
YYY last 3 digits of year
YY last 2 digits of year
Y last digit of year
BC, bc, AD or ad era indicator (without periods)
B.C., b.c., A.D. or a.d. era indicator (with periods)
MONTH full upper case month name
Month full capitalized month name
month full lower case month name
MON abbreviated upper case month name
Mon abbreviated capitalized month name
mon abbreviated lower case month name
MM month number (01-12)
DAY full upper case day name
Day full capitalized day name
day full lower case day name
DY abbreviated upper case day name
Dy abbreviated capitalized day name
dy abbreviated lower case day name
DD day of month (01-31)
TZ upper case time-zone abbreviation
OF time-zone offset from UTC
“T”, “Z” Letters T, Z
UNIX TIMESTAMP Treat 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.33 232.33
$$232.33 Empty
5.54% 5.54
5.54%% Empty
-5.54% -5.54
12,345,123.01 12345123.01
120,345,12.01 Empty
1.2.3.4 Empty
1 1
abc123 Empty