Format guide—column type conversions
When you convert columns between TEXT, NUMERIC, and DATE types in Mammoth, format codes tell Mammoth how to interpret your data. This guide covers the formats Mammoth supports for each conversion type, with practical examples and troubleshooting tips.
TEXT to DATE conversion
When converting text values to dates, you can let Mammoth auto-detect the format or specify a custom format using PostgreSQL-style format codes.
Auto-detection (recommended for standard formats)
If you select Source - Auto, Mammoth samples the values in your column and detects the date formats present. Auto-detection works for most real-world data. If your dates are in an uncommon format or mixed formats, use custom format codes instead.
Format codes reference
Mammoth converts your text using these PostgreSQL-style format codes:
| Code | Meaning | Example | Notes |
|---|---|---|---|
| YYYY | 4-digit year | 2025 | Use YY for 2-digit years (25) |
| MM | Month (01-12) | 01 | — |
| DD | Day of month (01-31) | 15 | — |
| HH24 | Hour (24-hour, 00-23) | 14 | Use HH12 or HH for 12-hour format (01-12) |
| MI | Minute (00-59) | 30 | — |
| SS | Second (00-59) | 45 | — |
| MON / MONTH | Month name | Jan / January | MON = abbreviated, MONTH = full. The code can be written in upper, title, or lower case: MON, Mon, mon, MONTH, Month, month |
| DAY / DY | Weekday name | Wednesday / Wed | DAY = full name, DY = 3-letter abbreviation. The code can be written in upper, title, or lower case |
| AM / PM | AM/PM indicator | AM | The code can be written AM, am, PM, pm, A.M., a.m., P.M. or p.m. |
| TZ | Timezone name | UTC | Timezone abbreviation (e.g., PST, EST, UTC) |
| OF | Timezone offset | +00:00 | Format: ±HH:MM or ±HHMM |
| Literal "T" | Literal T character | T | Use "T" to match literal T in ISO format (2025-01-15T14:30:00) |
| Literal "Z" | Literal Z character | Z | Use "Z" to match ISO Zulu timezone indicator (2025-01-15T14:30:00Z) |
Common format examples
ISO format (international standard)
Format code: YYYY-MM-DD
Input: 2025-01-15
Output: January 15, 2025
ISO with time (ISO 8601)
Format code: YYYY-MM-DD"T"HH24:MI:SS"Z"
Input: 2025-01-15T14:30:45Z
Output: January 15, 2025 2:30:45 PM (UTC)
US format (month/day/year)
Format code: MM/DD/YYYY
Input: 01/15/2025
Output: January 15, 2025
UK format (day/month/year)
Format code: DD/MM/YYYY
Input: 15/01/2025
Output: January 15, 2025
Long format with month name
Format code: DD MONTH YYYY
Input: 15 January 2025
Output: January 15, 2025
European format (day.month.year)
Format code: DD.MM.YYYY
Input: 15.01.2025
Output: January 15, 2025
Handling ambiguous dates
When your dates contain only numbers (e.g., 01/02/2025), Mammoth can't tell whether it's January 2nd (US) or February 1st (UK). Use these options:
| Option | Interpretation | Use when |
|---|---|---|
| Source - Auto | Detects formats from your data; may guess incorrectly | Your data is mostly unambiguous (has names or clear patterns) |
| Source - DD/MM/YYYY (Day First) | 01/02/2025 = February 1st | You know dates are always DD/MM/YYYY |
| Source - MM/DD/YYYY (Month First) | 01/02/2025 = January 2nd | You know dates are always MM/DD/YYYY |
| Source - Custom (multiple formats) | You specify the format code | You have mixed formats or non-standard patterns |
Best practice: If your data has ambiguous dates, always choose Day First or Month First explicitly rather than Auto. This prevents silent parsing errors.
Multiple formats
If your text column contains dates in different formats, you can specify multiple format codes separated by semicolons:
Format: YYYY-MM-DD; MM/DD/YYYY; DD.MM.YYYY
Mammoth tries each format in order until one succeeds. This is useful for:
- Imported data from multiple sources
- Historical data where the format changed over time
- Data entry that was inconsistent
TEXT to NUMERIC conversion
When converting text values to numbers, Mammoth extracts numeric values from formatted text and handles currency symbols, thousand separators, and percentages.
Supported currency symbols
Mammoth recognizes these 5 currency symbols when they appear before the number, and strips them during conversion:
| Symbol | Currency | Example input | Parsed as |
|---|---|---|---|
| $ | US Dollar | $1,234.56 | 1234.56 |
| € | Euro | €1,234.56 | 1234.56 |
| £ | British Pound | £1,234.56 | 1234.56 |
| ¥ | Japanese Yen / Chinese Yuan | ¥1234 | 1234 |
| ₹ | Indian Rupee | ₹1,234.56 | 1234.56 |
Thousand separator handling
Mammoth removes comma thousand separators before converting. Both standard grouping (1,234,567) and Indian-style grouping (12,34,567) are accepted:
| Format | Input | Parsed As |
|---|---|---|
| Comma as thousand separator | 1,234,567.89 | 1234567.89 |
| No separator | 1234567.89 | 1234567.89 |
European format is not supported. Numbers that use a period as the thousand separator and a comma as the decimal (e.g.
1.234.567,89) are not recognized and will convert to a blank value. Use Find & Replace to convert the separators to the comma-thousands/period-decimal convention first (e.g.1.234.567,89→1,234,567.89), then convert to Numeric.
Supported formats
| Input type | Example | Output |
|---|---|---|
| Plain number | 1234 | 1234 |
| With decimals | 1234.56 | 1234.56 |
| Currency prefix | $1,234.56 | 1234.56 |
| Negative with currency | -$1,234.56 | -1234.56 |
| Negative (leading) | -1,234.56 | -1234.56 |
| Negative (after the symbol) | $-1,234.56 | -1234.56 |
| Percentage | 45% | 45 |
| Leading/trailing spaces | " $1,234.56 " | 1234.56 |
Precision limits
Mammoth stores converted numbers with up to 8 decimal places (NUMERIC(38,8)):
- Input: 1234.56789012345
- Stored as: 1234.56789012
Numbers are rounded to 8 decimal places.
Examples
Currency conversion
Input: $5,000.25
Output: 5000.25
Percentage parsing
Input: 25.5%
Output: 25.5
European-style currency (not supported)
Input: €1.234,56 (period = thousand sep, comma = decimal)
Output: (blank — this format is not recognized)
Convert the separators first (for example with Find & Replace) if your source data uses this convention.
NUMERIC to DATE conversion
When converting numbers to dates, Mammoth automatically detects the time unit (seconds, milliseconds, microseconds, or nanoseconds) based on the magnitude of the input value.
Auto-detection logic
Mammoth examines the numeric value and determines its unit:
| Value range | Detected unit | Interpretation | Example input | Output |
|---|---|---|---|---|
| -62,135,596,800 to 253,402,300,799 | Seconds | Unix timestamp (standard) | 1735689600 | 2025-01-01 00:00:00 |
| -62,135,596,800,000 to 253,402,300,799,000 | Milliseconds | Divide by 1,000 | 1735689600000 | 2025-01-01 00:00:00 |
| -62,135,596,800,000,000 to 253,402,300,799,000,000 | Microseconds | Divide by 1,000,000 | 1735689600000000 | 2025-01-01 00:00:00 |
| -62,135,596,800,000,000,000 to 253,402,300,799,000,000,000 | Nanoseconds | Divide by 1,000,000,000 | 1735689600000000000 | 2025-01-01 00:00:00 |
| Outside all ranges | — | NULL (invalid) | 99999999999999999999999 | (empty cell) |
Why auto-detection?
You don't need to specify the unit because Unix timestamps from different systems (JavaScript, Python, Java, etc.) use different units by default:
- JavaScript / milliseconds:
Date.now()returns 1735689600000 - Python / seconds: time.time() returns 1735689600
- Java / milliseconds: System.currentTimeMillis() returns 1735689600000
Mammoth figures out which one you have and converts correctly.
Common examples
JavaScript timestamp (milliseconds)
Input: 1735689600000
Auto-detected as: Milliseconds
Output: 2025-01-01 00:00:00
Unix timestamp (seconds)
Input: 1735689600
Auto-detected as: Seconds
Output: 2025-01-01 00:00:00
Epoch boundary (zero)
Input: 0
Auto-detected as: Seconds
Output: 1970-01-01 00:00:00 (Unix epoch start)
Negative timestamp (before 1970)
Input: -86400
Auto-detected as: Seconds
Output: 1969-12-31 00:00:00 (One day before epoch)
Common conversion errors and troubleshooting
When a conversion fails, the cell becomes empty (NULL). Here's how to diagnose and fix issues:
TEXT to DATE issues
| Problem | Cause | Fix |
|---|---|---|
| All cells become empty | Format code doesn't match your data | Check the actual date format in your source data. Try Auto-detection first. |
| Some cells are empty | Some values don't match the format | Check for typos, extra spaces, or inconsistent formatting in those rows. |
| "01/02/2025" parses wrong | Ambiguous date format (US vs UK) | Explicitly choose "Day First" or "Month First" instead of Auto. |
| Extra text after date (e.g., "2025-01-15 extra text") | Format string doesn't account for trailing content | The format only needs to match the date portion. Extra characters are ignored. |
| "2025-01-15 14:30:00" doesn't parse | Missing time portion in format | Use YYYY-MM-DD HH24:MI:SS to include time. |
Debug tip: Duplicate your column and try converting it with Auto-detection first. If that fails, the data itself may have issues (typos, inconsistent formats, or truly invalid dates).
TEXT to NUMERIC issues
| Problem | Cause | Fix |
|---|---|---|
| All cells become empty | No numeric values found in text | Check that your text actually contains numbers, not just words. |
| Some cells are empty | Unsupported currency or format | Mammoth only supports €$¥£₹. Other symbols aren't recognized. |
| "1,234.56€" fails | Currency symbol after the number | Move the symbol in front of the number: "€1,234.56". Only a leading symbol is recognized. |
| "1.234,56" fails | European format (period = thousand separator) | Mammoth only recognizes comma as the thousand separator. Use Find & Replace to convert the value to comma-thousands/period-decimal format first (e.g. "1.234,56" → "1,234.56"), then convert to Numeric. |
| "25%" outputs 25 | Percentage sign is stripped | The % is removed; you get the number. Format as percentage in the output if needed. |
NUMERIC to DATE issues
| Problem | Cause | Fix |
|---|---|---|
| All cells become empty | Values outside valid timestamp range | Check your numbers. Valid range: -62,135,596,800 to 253,402,300,799 seconds. |
| Date is in 1970 when it should be 2025 | Unit auto-detection chose wrong unit | Your number might be in seconds when system expects milliseconds. Check the source system's convention. |
| Very old or future dates | Timestamp interpreted as different unit | Verify the input is actually a Unix timestamp. Non-timestamp numbers will parse incorrectly. |
Best practices
-
Test with a sample first. Before converting an entire column, test your format code on a few rows to confirm it works.
-
Use Auto-detection when possible. For standard date formats (ISO, US, UK), let Mammoth detect the format. Specify a custom format only if Auto fails.
-
Be explicit with ambiguous dates. Don't use Auto for DD/MM/YYYY vs MM/DD/YYYY ambiguity—explicitly choose one.
-
Preserve originals. Copy a column before converting it, so you can compare the original and converted values if something goes wrong.
-
Check edge cases. After converting, spot-check a few values:
- First few rows
- Last few rows
- Any that had conversion errors (empty cells)
-
Currency and locale matter. TEXT-to-NUMERIC assumes a comma is the thousand separator and a period is the decimal point. If your data uses other symbols or conventions, you may need to clean it first.