Data types

How a column's values are cleaned up before they are compared or written — zero-padded IDs, names written two ways, numbers, dates, phone numbers and more.

A data type decides how values are transformed before they are compared (in Compare) or written to the output file (in Convert and Load). It applies to both sides.

Set a column's type in the Data Type column of the Mapping tab. The ? beside it shows this same reference inside the app.

Basic

STRING

The default data type, for text.

• A new comparison makes every column STRING unless its name says otherwise — see Assigned automatically below.
• Spaces at the start and end are removed.
• In Compare, capital letters are ignored: Smith matches SMITH. The value itself is not changed.

Example: Smith → Smith, which matches SMITH

Note: Spaces inside a value still count. Use TRIM only if values differ by doubled spaces inside the text.

TRIM — Trim spaces

STRING, with the spacing inside the text tidied too.

• Everything STRING does: ends trimmed, and capital letters ignored in Compare, in values and in keys.
• Every run of spaces, tabs or non-breaking spaces inside the text becomes one space, like Excel's TRIM().

Example: Jane Doe → Jane Doe

Note: Use it only when values differ by doubled spaces inside the text; otherwise leave the column STRING. Line breaks inside a value are kept.

DATE

Dates, compared as YYYY-MM-DD.

• Reads common formats — 1/5/2024, 2024-01-05, Jan 5 2024 — and Excel date numbers.
• An ambiguous date is read in US order, month first (MM/DD/YYYY): 01/02/2024 is 2 January.
• The time of day is dropped.
• Workday's end of time, 9999-12-31, is written as 1900-01-01 on both sides.

Example: 1/5/2024 → 2024-01-05

Caution: A value that is not a date becomes blank — it is not flagged — so N/A or a mistyped date matches an empty cell on the other side.

NUMBER

Numbers, compared by value: 35, 35.00 and $35 match.

• Thousands separators, currency signs and percent signs are removed.
• (3) is read as -3.
• Every digit is kept.

Example: $1,234.50 → 1234.5

Caution: Blank cells and text that is not a number become 0, so they match a real 0 or 0.00. In a column whose name contains PERCENT, the whole column is divided by 100 when any value is over 1: 45 and 0.45 match, but a column already written as fractions is divided too if any value is above 1 (150% written 1.5).

LIST

Codes from a fixed set, compared with capital letters ignored.

Example: Active matches ACTIVE

Note: Unlike STRING, spaces at the start and end are not removed.

BOOL — Boolean

Yes/no values.

• Y, Yes, True, T and 1 are true; N, No, False, F and 0 are false — in any capitals.
• Any other value is left as it is, so it shows as a difference.

Example: Yes → True; n → False

Caution: A blank counts as false, so it matches N — the usual shape when a report writes Yes and leaves the negative empty.

ID / Personal

EEID

Employee IDs, padded with leading zeros to one width on both sides.

• 123 and 000000123 match.
• The width is 9 unless changed under Data Type Options.
• A value containing an uppercase E or P is cut to its last 7 characters first.

Example: 123 → 000000123

Note: All zeros becomes blank. Letters are padded too (abc → 000000abc), and a value longer than the width is never shortened.

SSN

US Social Security numbers, reduced to their 9 digits.

• 123-45-6789 and 123456789 match.
• A shorter number is zero-padded at the front: 12345678 → 012345678.

Example: 123-45-6789 → 123456789

Note: All zeros becomes blank.

PHONE

Phone numbers, reduced to their digits.

• (555) 123-4567 and 555.123.4567 match.
• A leading +1 (with its space) is removed first.

Example: +1 (555) 123-4567 → 5551234567

Note: +1 with no space after it keeps the 1 (+15551234567 → 15551234567). An extension's digits stay on the end (… x99 → …456799); use PHONEX to compare extensions.

PHONEX — PhoneExt

A phone number reduced to its extension, for a full number on one side and an extension field on the other.

• A value that is already just digits (up to 6) is left as it is, so it works whichever side holds which.
• Without a marker (x, ext), trailing digits are an extension only when at least 7 digits come before them.

Example: 555-123-4567 x99 → 99

Note: A number with no extension becomes blank, to match an empty extension field.

POSTAL — Postal code

Postal codes, put into one shape on both sides.

• Trimmed, repeated spaces collapsed, and uppercased (Canadian and UK codes carry letters).
• A US ZIP+4 is written 12345-6789 whether it arrived as 123456789, 12345 6789 or already hyphenated.
• Decided by the value, not by a country column, which is often unmapped or on the other side.

Example: 123456789 → 12345-6789

Note: Codes that are not a US ZIP+4 keep their own form; only spacing and case are tidied.

HASH

Each value replaced by a hash of itself, so a sensitive column can be checked without its values appearing in the report.

Example: 123-45-6789 → 11490817395509504796

Note: Only exact matches survive hashing: capital letters and spaces count, and a difference shows as two unrelated numbers, not the values. Blanks stay blank.

HTML

Rich text, compared as plain text.

• Tags removed, and entities such as & decoded.
• All whitespace — line breaks included — collapsed to single spaces.

Example: <p>Fish &amp; Chips</p> → Fish & Chips

Note: Capital letters count.

Rounding

RND0 — Round 0

Rounds to a whole number before comparing.

• Nearest, ties away from zero: 2.5 → 3, -2.5 → -3.
• Cleaned like NUMBER first.

Example: 2.5 → 3

Note: Anything below a whole unit stops being reported as a difference — use it when the two systems agree on the unit but not the fraction. Blank and non-numeric text become 0, as with NUMBER.

RND2 — Round 2

Rounds to 2 decimal places before comparing — the usual choice for currency.

• Nearest, ties away from zero: 10.005 → 10.01.
• Cleaned like NUMBER first.

Example: 10.005 → 10.01

Note: Values differing only below the cent compare as equal. RNDUP2 rounds every remainder up instead. Blank and non-numeric text become 0, as with NUMBER.

RND6 — Round 6

Rounds to 6 decimal places before comparing — for rates and factors.

• Nearest, ties away from zero.
• Cleaned like NUMBER first.

Example: 0.12345678 → 0.123457

Note: For two systems that carry different trailing precision but agree to six places. Blank and non-numeric text become 0, as with NUMBER.

RNDUP2 — Round-up 2

Rounds every value up — away from zero — to 2 decimal places.

• 10.001 → 10.01; -10.001 → -10.01.
• Cleaned like NUMBER first.

Example: 10.001 → 10.01

Note: Values differing only below the cent compare as equal. For ordinary rounding to the nearest cent use RND2. Blank and non-numeric text become 0, as with NUMBER.

Reference / EIB

REF_ID

Marks a Workday reference ID field. It converts nothing.

• Set from an EIB template's Format row (a format ending _ID or _Code).

Note: Compared exactly, capital letters and spaces included.

ISO-2

Marks a two-letter ISO 3166 country code field (US, GB). It converts nothing.

• Set from an EIB template's Format row.

Note: Compared exactly, capital letters included. It does not convert between two- and three-letter codes or from country names; use a translate for that.

ISO-3

Marks a three-letter ISO 3166 country code field (USA, GBR). It converts nothing.

• Set from an EIB template's Format row.

Note: Compared exactly, capital letters included. It does not convert between two- and three-letter codes or from country names; use a translate for that.

Name splitting

FULL2PREFIX — Full → Prefix

The title from a full name: Mr, Mrs, Ms, Miss, Dr or Prof.

• Reads Last, First Middle or First Middle Last.
• In the second form only the final word is the last name: Jane Van Doe → last name Doe.
• A suffix is recognised as Jr., Sr., II, III, IV or V; Jr without its period is read as the last name.
• Capital letters count.

Example: Dr. Jane Doe → Dr.

Caution: Used as a key, different people can reduce to the same value, and only the first of them is compared.

FULL2FIRST — Full → First

The first name from a full name.

• Reads Last, First Middle or First Middle Last.
• In the second form only the final word is the last name: Jane Van Doe → last name Doe.
• A suffix is recognised as Jr., Sr., II, III, IV or V; Jr without its period is read as the last name.
• Capital letters count.

Example: Doe, Jane L → Jane; Jane L Doe → Jane

Caution: Used as a key, different people can reduce to the same value, and only the first of them is compared.

FULL2MIDDLE — Full → Middle

The middle name or names from a full name — everything between the first and last.

• Reads Last, First Middle or First Middle Last.
• In the second form only the final word is the last name: Jane Van Doe → last name Doe.
• A suffix is recognised as Jr., Sr., II, III, IV or V; Jr without its period is read as the last name.
• Capital letters count.

Example: Doe, Jane L → L

Caution: Used as a key, different people can reduce to the same value, and only the first of them is compared.

FULL2LAST — Full → Last

The last name from a full name.

• Reads Last, First Middle or First Middle Last.
• In the second form only the final word is the last name: Jane Van Doe → last name Doe.
• A suffix is recognised as Jr., Sr., II, III, IV or V; Jr without its period is read as the last name.
• Capital letters count.

Example: Doe, Jane L → Doe; Jane L Doe → Doe

Caution: Used as a key, different people can reduce to the same value, and only the first of them is compared.

FULL2SUFFIX — Full → Suffix

The suffix from a full name.

• Reads Last, First Middle or First Middle Last.
• In the second form only the final word is the last name: Jane Van Doe → last name Doe.
• A suffix is recognised as Jr., Sr., II, III, IV or V; Jr without its period is read as the last name.
• Capital letters count.

Example: Jane Doe Jr. → Jr.

Caution: Used as a key, different people can reduce to the same value, and only the first of them is compared.

FULL2LFM — Full → Last,First Middle

A full name rewritten as Last,First Middle — PeopleSoft's layout — whichever way it was written.

• Doe,Jane L and Jane L Doe match.
• Periods and the spaces around the comma are ignored.
• A title is dropped, and a suffix goes after the last name: Doe Jr,Jane L.

Example: Jane L. Doe → Doe,Jane L

Note: A last name of more than one word written first-name-first (Jane Van Doe) reads as Doe,Jane Van. Capital letters count.

Assigned automatically

A new comparison picks each column's type from its name. The rules are checked in this order and the first that applies wins. A type can always be changed afterwards.

1. Ends _DATE or _DT → DATE
2. Ends _AMOUNT, _AMT, _HOURS, _HRS, _PRICE, _COST, _PERCENT or _PCT → NUMBER
3. Ends _SSN → SSN
4. Ends _PHONE → PHONE
5. Ends in DATE run together (HIREDATE), when its values look like dates — or, with no values to check, unless it is a word such as UPDATE or CANDIDATE → DATE
6. Ends _LPAD or _LPADnn — zero-pads to nn digits; not offered in the picker → LPAD
7. Starts DATE_ → DATE
8. Ends _ID, or is ID → STRING
9. Contains CREATED, UPDATED or TIMESTAMP, when its values look like dates — or, with no values to check, when the name ends with the word (LAST_UPDATED, CREATED_ON, UPDATED_AT). CREATED_BY stays STRING → DATE
10. Contains HTML; or contains DESCRIPTION or CONTENT and its values carry HTML tags → HTML
11. Anything else → STRING

• A Load (EIB) template's Format row sets DATE, NUMBER, REF_ID, ISO-2 or ISO-3 for its fields.
• Map with AI, with a TypeSafe provider, can suggest a type when the name gives nothing away — never over a type the name decided or one you chose.