# Export mappings > The ExportMapping JSON schema that turns TimeTracking pay lines into a payroll import file: fields, every transform with input/output examples, code tables, grouping, validation, and how to create and use mappings. URL: https://timetracking.tech/developers/data-out/export-mappings Last updated: 2026-10-24 An **export mapping** is a JSON recipe. It turns pay lines into the exact CSV or TXT file a payroll system imports. Switching payroll provider means writing a new mapping, not new code. ``` approved days ─► rule pack ─► pay lines ─► export mapping ─► file (+ issues) ``` ## Where mappings live | Mapping | `mappingId` | Editable | |---|---|---| | SD Worx concept preset | `preset-sdworx` | No (copy it) | | Generic CSV preset | `preset-generic` | No (copy it) | | Your org's mappings | UUID | Yes. Admins use "Export" → "Exportformaten beheren" (export formats) in the dashboard: start from a preset ("Beginnen vanaf"), edit columns, code tables ("Codetabel") and validation ("Validatie vóór export"), check the live preview ("Live voorbeeld"), set a default ("Maak standaard"). Max 50 per org | To use a mapping, pass its id as `mappingId` to `POST /exports`. The id of an org mapping is the UUID in its dashboard URL (`/export/mappings/`). > **Planned:** creating, reading and updating mappings over the API. Today the dashboard is the only editor, and `POST /exports` with an unknown id returns 404 `Export mapping not found`. Committed runs keep a full `mapping_snapshot` (`GET /exports/runs/{id}`), so you can always read the exact mapping a file was produced with. ## Pay line (the input) Each pay line is one person × date × hour type: | Field | Type | Example | Notes | |---|---|---|---| | `employeeNumber` | string | `"1004"` | `people.employee_number`, `""` when missing | | `employeeName` | string | `"Sanne Janssen"` | first + last name | | `clientName` | string | `"Bakkerij Ruijters"` | `""` for leave lines | | `placementRef` | string | `"PL-2026-104"` | placement `ref`, `""` for leave | | `date` | `YYYY-MM-DD` | `"2026-12-01"` | | | `hourType` | string | `"normal"`, `"overtime_125"`, `"expense_km"`, `"leave_vacation"` | see [hour types](https://timetracking.tech/developers/concepts/hour-types-and-rule-packs) | | `minutes` | integer | `480` | `0` for money-only lines | | `amountCents` | integer \| absent | `750` | allowances and expenses | | `componentType` | `"hours"` \| `"allowance"` | `"hours"` | | Real pay lines are stored on every export run (`GET /exports/runs/{id}` → `pay_lines`): ```json [ { "date": "2026-12-01", "minutes": 480, "hourType": "normal", "clientName": "Bakkerij Ruijters", "employeeName": "Sanne Janssen", "placementRef": "PL-2026-104", "componentType": "hours", "employeeNumber": "1004" }, { "date": "2026-12-02", "minutes": 450, "hourType": "normal", "clientName": "Bakkerij Ruijters", "employeeName": "Sanne Janssen", "placementRef": "PL-2026-104", "componentType": "hours", "employeeNumber": "1004" } ] ``` ## ExportMapping schema ```ts interface ExportMapping { id: string; // "preset-…" or the org mapping's UUID (set by the server) name: string; // 2–120 chars; appears in export.completed as data.mapping format: "csv" | "txt" | "fixed" | "xml"; // csv/txt = delimited; fixed = fixed-width .txt; xml = .xml delimiter: ";" | "," | "\t" | "|"; includeHeader: boolean; // first line = column headers encoding: "utf-8" | "utf-8-bom" | "windows-1252"; lineEnding: "crlf" | "lf"; groupBy?: PayLineField[]; // ≤ 9 fields; sum minutes and amountCents per group columns: ExportColumn[]; // 1–60; headers must be unique tables: Record>; // code tables for "map" validation: ValidationRule[]; // ≤ 60 xml?: { root: string; record: string; declaration?: boolean }; // format "xml" only } interface ExportColumn { header: string; // 1–80 chars source: PayLineField | `const:${string}`; // a pay line field, or a constant ("const:NL01") transform?: Transform; width?: number; // fixed: field width 1–200 (default 10); longer values are cut align?: "left" | "right"; // fixed: default left padChar?: string; // fixed: one character, default space ("0" for zero-padded numbers) xmlName?: string; // xml: element name (default: header made XML-safe) } interface ValidationRule { column: string; // must be an existing header required?: boolean; // empty → issue pattern?: string; // JavaScript regex source, ≤ 200 chars min?: number; // numeric check ("7,50" is read as 7.5) max?: number; } type PayLineField = "employeeNumber" | "employeeName" | "clientName" | "placementRef" | "date" | "hourType" | "minutes" | "amountCents" | "componentType"; ``` The same rules as JSON Schema (draft 2020-12), for validating mappings in your own tooling: ```json { "$schema": "https://json-schema.org/draft/2020-12/schema", "$id": "https://timetracking.tech/schemas/export-mapping.json", "type": "object", "required": ["name", "format", "delimiter", "includeHeader", "encoding", "lineEnding", "columns", "tables", "validation"], "properties": { "id": { "type": "string", "maxLength": 80 }, "name": { "type": "string", "minLength": 2, "maxLength": 120 }, "format": { "enum": ["csv", "txt", "fixed", "xml"] }, "delimiter": { "enum": [";", ",", "\t", "|"] }, "includeHeader": { "type": "boolean" }, "encoding": { "enum": ["utf-8", "utf-8-bom", "windows-1252"] }, "lineEnding": { "enum": ["crlf", "lf"] }, "groupBy": { "type": "array", "maxItems": 9, "items": { "$ref": "#/$defs/field" } }, "columns": { "type": "array", "minItems": 1, "maxItems": 60, "items": { "type": "object", "required": ["header", "source"], "properties": { "header": { "type": "string", "minLength": 1, "maxLength": 80 }, "source": { "anyOf": [{ "$ref": "#/$defs/field" }, { "type": "string", "pattern": "^const:.{0,200}$" }] }, "transform": { "$ref": "#/$defs/transform" } } } }, "tables": { "type": "object", "additionalProperties": { "type": "object", "additionalProperties": { "type": "string", "maxLength": 60 } } }, "validation": { "type": "array", "maxItems": 60, "items": { "type": "object", "required": ["column"], "properties": { "column": { "type": "string", "minLength": 1, "maxLength": 80 }, "required": { "type": "boolean" }, "pattern": { "type": "string", "maxLength": 200 }, "min": { "type": "number" }, "max": { "type": "number" } } } } }, "$defs": { "field": { "enum": ["employeeNumber", "employeeName", "clientName", "placementRef", "date", "hourType", "minutes", "amountCents", "componentType"] }, "sep": { "enum": [".", ","] }, "transform": { "oneOf": [ { "type": "object", "required": ["kind"], "properties": { "kind": { "const": "hours_decimal" }, "decimals": { "type": "integer", "minimum": 0, "maximum": 4 }, "decimalSeparator": { "$ref": "#/$defs/sep" } } }, { "type": "object", "required": ["kind"], "properties": { "kind": { "const": "hours_hm" } } }, { "type": "object", "required": ["kind"], "properties": { "kind": { "const": "minutes" } } }, { "type": "object", "required": ["kind", "format"], "properties": { "kind": { "const": "date" }, "format": { "enum": ["YYYY-MM-DD", "DD-MM-YYYY", "DD/MM/YYYY", "YYYYMMDD"] } } }, { "type": "object", "required": ["kind", "table"], "properties": { "kind": { "const": "map" }, "table": { "type": "string", "minLength": 1, "maxLength": 60 }, "fallback": { "type": "string", "maxLength": 60 } } }, { "type": "object", "required": ["kind"], "properties": { "kind": { "const": "cents_decimal" }, "decimalSeparator": { "$ref": "#/$defs/sep" } } }, { "type": "object", "required": ["kind"], "properties": { "kind": { "const": "upper" } } }, { "type": "object", "required": ["kind", "length"], "properties": { "kind": { "const": "pad" }, "length": { "type": "integer", "minimum": 1, "maximum": 40 }, "char": { "type": "string", "maxLength": 1 } } } ] } } } ``` The dashboard editor also checks three cross-field rules: column headers are unique, every `map` transform names an existing table, and every validation rule names an existing column. ## Transforms Without a transform, the raw value is printed (`null`/absent → empty). | `kind` | Options | Input → output | |---|---|---| | `hours_decimal` | `decimals` (default 2), `decimalSeparator` `"."` (default) or `","` | `minutes: 450` → `7.50`; with `{"decimals":2,"decimalSeparator":","}` → `7,50`; `{"decimals":0}` → `8` (rounded) | | `hours_hm` | — | `450` → `7:30`; `480` → `8:00` | | `minutes` | — | `450` → `450` | | `date` | `format`: `YYYY-MM-DD`, `DD-MM-YYYY`, `DD/MM/YYYY`, `YYYYMMDD` | `2026-12-01` → `2026-12-01` / `01-12-2026` / `01/12/2026` / `20261201` | | `map` | `table` (key into `tables`), `fallback` (default `""`) | `hourType: "overtime_125"` with `tables.wageCodes.overtime_125 = "1125"` → `1125`; unmapped → fallback | | `cents_decimal` | `decimalSeparator` | `amountCents: 750` → `7.50` / `7,50`; absent → empty | | `upper` | — | `"pl-2026-104"` → `PL-2026-104` | | `pad` | `length`, `char` (default `"0"`) | `"1004"` with `{"length":8}` → `00001004` (left-padded; longer values are not cut) | Constants: `"source": "const:NL01"` always prints `NL01`. Transforms apply to constants too. ## Grouping `groupBy` sums `minutes` and `amountCents` of lines with equal values in the listed fields. For example, `["employeeNumber","hourType"]` produces one line per employee per hour type for the whole export range. All fields not in `groupBy` (including `date`) are taken from the **first** line of the group. Captured from the SD Worx preset: two days (8.00 + 7.50 h) became one row `1004;Sanne Janssen;[CODE_NORMAAL];15,50;;01-12-2026`. The date column then shows the first day, not the period. Add `date` to `groupBy` for per-day lines, or use `const:` for a period column. ## Fixed-width and XML files `format: "fixed"` writes one line per row with every column padded or cut to its `width` (no delimiter, no quoting; tabs and newlines in values become spaces). With `includeHeader` the first line holds the headers in the same widths. `align: "right"` with `padChar: "0"` gives zero-padded numbers. Example (preset `preset-generic-fixed`, widths 8/30/20/8/8): ```text 00001001Jan de Vries normal 15.50 20261102 ``` `format: "xml"` writes `value……`, indented two spaces, with the mapping's line ending. Element names come from `xmlName` or the header (`"Aantal uren"` → `Aantal_uren`; names starting with a digit or `xml` get a leading `_`). Values are escaped (`&`, `<`, `>`, `"`). The declaration states `UTF-8`, or `windows-1252` for that encoding; `delimiter` and `includeHeader` are ignored. Example (preset `preset-generic-xml`): ```xml 1001 Jan de Vries VDL Montage PL-2026-101 2026-11-02 normal 8.00 ``` Validation, grouping and code tables work the same for every format. `GET /exports/runs/{id}/file` serves the file as `text/plain` (fixed, txt), `application/xml` (xml) or `text/csv`; `?as=json` and `?as=xlsx` convert any format (XML and fixed-width runs are rebuilt from the run's frozen pay lines). ## Validation Rules run on the **formatted** cell values. Every violation is an issue `{row, column, message}` (`row` is 1-based, without the header). Messages: `Required value missing`, `Does not match `, `Below `, `Above `. Any issue sets `ok: false`, and a commit then fails with 422 `Export has N validation issue(s)`. Typical rules: ```json [ { "column": "Werknemersnummer", "required": true, "pattern": "^\\d+$" }, { "column": "Looncomponent", "required": true }, { "column": "Aantal", "required": true, "min": 0, "max": 400 } ] ``` ## Encoding and line endings - `utf-8-bom` prefixes the file with a UTF-8 BOM (U+FEFF). Excel then opens accented names correctly. - `windows-1252` is applied when you download the committed file with `GET /exports/runs/{id}/file`. The response is `text/csv; charset=windows-1252`, without BOM. Characters outside U+0000–U+00FF (`€`, curly quotes, em dash, `ł`, `ő` …) become `?`, so keep codes and headers plain. Preview `content`, the commit response and the dashboard download remain UTF-8. Convert those yourself if needed (`iconv -f UTF-8 -t WINDOWS-1252`). - `crlf` is recommended for Windows-based payroll importers. - Values containing the delimiter, quotes or newlines are quoted CSV-style (`"` doubled). - Cells that start with `=`, `+`, `-`, `@`, a tab or CR, and are not plain numbers, get a leading `'` (protection against spreadsheet formula injection). Negative numbers like `-7,50` are left alone. ## Complete example: a custom mapping A per-week file for a payroll system that wants employee number padded to 6, a wage code, decimal hours with a comma, an amount, and the week's Monday: ```json { "name": "Loket — variabele gegevens per week", "format": "csv", "delimiter": ";", "includeHeader": true, "encoding": "utf-8-bom", "lineEnding": "crlf", "groupBy": ["employeeNumber", "hourType"], "columns": [ { "header": "Werknemer", "source": "employeeNumber", "transform": { "kind": "pad", "length": 6 } }, { "header": "Looncomponent", "source": "hourType", "transform": { "kind": "map", "table": "codes" } }, { "header": "Aantal", "source": "minutes", "transform": { "kind": "hours_decimal", "decimals": 2, "decimalSeparator": "," } }, { "header": "Bedrag", "source": "amountCents", "transform": { "kind": "cents_decimal", "decimalSeparator": "," } }, { "header": "Periode", "source": "const:2026-W49" } ], "tables": { "codes": { "normal": "1000", "overtime_125": "1125", "ort_night": "2100", "holiday": "2300", "expense_km": "8010", "leave_vacation": "3000" } }, "validation": [ { "column": "Werknemer", "required": true, "pattern": "^\\d{6}$" }, { "column": "Looncomponent", "required": true } ] } ``` The `const:` period above is static. Change it per run in the dashboard, or leave it out and let the payroll import assign the period. Wage codes here are placeholders: get the real codes from your payroll system or consultant. See the per-system guides, e.g. [Loket](https://timetracking.tech/developers/integrations/payroll/loket). ## Using a mapping ```sh # Preview (no changes) curl -s -X POST "$TT/exports" -H "Authorization: Bearer $KEY" -H "content-type: application/json" \ -d '{"mappingId":"","startsOn":"2026-11-30","endsOn":"2026-12-06","mode":"preview"}' ``` Check `ok`, `issues`, `warnings` and `pendingCount`, then commit. See [payroll files](https://timetracking.tech/developers/data-out/payroll-files).