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