Data mapping and formats: a practical guide

Every integration, however it is built, ends up doing the same thing in the middle: it takes data shaped the way one system likes it and reshapes it the way another system expects. The e-shop sends an order as JSON, the ERP wants XML, the warehouse reads a fixed-width text file, and the accountant opens a CSV in Excel. That reshaping is data mapping, and it is where most of the quiet, expensive bugs in integration live.

This guide covers the formats you will meet, what a mapping actually consists of, the pitfalls that catch almost every team (especially with Greek data), and how to compare mapping tools.

The formats you will meet

JSON

The default for modern REST APIs and e-shops. Objects, arrays, strings, numbers, booleans and null. It is easy to read, but it has no native date type and no standard way to say “this number has two decimals”, so every API invents its own conventions.

XML

Still everywhere in ERPs, public-sector services and older SOAP APIs. More verbose than JSON, but it comes with mature tooling: XSD schemas to validate structure and types, namespaces to avoid name clashes, and XSLT to transform it. Watch for namespaces: two elements that look identical can be different if their namespace differs.

CSV and delimited files

The format of exports, imports and “can you just send me a file”. Simple until it isn’t: which delimiter (comma or semicolon, which Greek-locale Excel uses by default), whether fields are quoted, what happens when a product description contains the delimiter, and whether there is a header row.

Fixed-width flat files

Each field occupies fixed positions in the line, for example characters 1-10 are the customer code. Common in banking, older ERPs and warehouse systems. Fragile, because one extra character shifts every field after it.

EDI

EDIFACT and X12 are standardised business documents (orders, invoices, despatch advices) with segments and elements separated by special characters. They are a format and a set of business rules at the same time. We cover them in depth in B2B and EDI: a practical guide.

Schemas: write down the shape

A schema describes what a valid document looks like: which fields exist, which are required, their types, lengths and allowed values. JSON Schema, XSD, EDI implementation guides and even a well-written spreadsheet for a CSV all play this role.

Schemas matter for mapping because they turn assumptions into facts. “The ERP expects ItemCode up to 20 characters” is something you can check automatically. “I think it’s short” isn’t. If a system doesn’t publish a schema, collect a few real samples and write one yourself; most mapping tools can generate a first draft from a sample.

What a mapping is made of

A mapping is a set of rules, one per target field. Almost every rule falls into one of these types:

Rule type	Example
Direct copy	`order.id` → `SalesOrder/ExternalRef`
Rename/restructure	Nested `customer.address.city` → flat `ShipCity`
Format conversion	`2026-10-23T14:05:00Z` → `23/10/2026`
Combine/split	`firstName` + `lastName` → `CustomerName`
Lookup	Payment method `card` → ERP code `07`
Default/constant	`Company` = `01` on every order
Calculation	Line net amount = quantity × unit price − discount
Conditional	If `country` is not `GR`, set VAT category to “exempt intra-EU”
Repeat/aggregate	One target line per source line, or a total summed from all lines

Lookups deserve special attention. Code translations (VAT categories, payment methods, warehouse codes, units of measure) change over time and often differ between test and production. Keep them in a table that can be maintained outside the mapping logic, not hardcoded in it.

The pitfalls that break integrations

Character encoding and Greek text

Greek text is the classic trap. Modern APIs use UTF-8, but many Greek ERPs and older systems export in Windows-1253 (or ISO-8859-7). Read a Windows-1253 file as UTF-8 and “Αθήνα” turns into garbage or question marks. Rules of thumb:

Find out the encoding of every file-based interface explicitly. Don’t guess from one sample.
Convert once, at the edge, and work in UTF-8 inside the integration.
Watch for a byte-order mark (BOM) at the start of UTF-8 files; some systems choke on it, others need it (Excel, for instance, relies on it to detect UTF-8 in CSV files).
Check field lengths in characters vs bytes. A Greek letter is two bytes in UTF-8, so a 40-byte field fits only 20 Greek characters.
Decide what to do with accents and case when matching: “ΑΘΗΝΑ”, “Αθήνα” and “αθηνα” may need to be treated as the same city.

Decimal comma vs decimal point

In Greece, 1.250,40 means one thousand two hundred fifty and forty cents. In JSON it is 1250.40. A CSV from Greek Excel and a JSON from an e-shop can disagree on both the decimal and thousands separators. Parse numbers with an explicit format, never by “trying”, and never store money in floating-point types if you can avoid it. Rounding should happen once, at a defined point, usually per line and to two decimals, matching how the ERP rounds.

Dates and time zones

03/04/2026 is 3 April in Greece and March 4 in the US. A timestamp without a time zone is ambiguous. And Greece moves between EET (UTC+2) and EEST (UTC+3), so an order placed at 00:30 local time on 1 April belongs to a different day in UTC. This matters for invoice dates, daily batches and anything with a cut-off.

Exchange timestamps in ISO 8601 with an offset (2026-04-01T00:30:00+03:00) or in UTC.
Convert to local dates only when a business rule needs a local date (an invoice date, a delivery day).
Be explicit about date-only fields: a birth date or an invoice date has no time zone, and shouldn’t get one.

Empty, missing and null

“The field is missing”, “the field is present but empty” and “the field is null” can mean three different things to the target system. Decide for each important field whether an empty value means “clear it” or “leave it unchanged”. This is the source of many “the update wiped the customer’s phone number” incidents.

Lengths, truncation and types

A product name of 120 characters going into a 50-character ERP field will be truncated, rejected, or silently cut differently by each system. Leading zeros disappear when a code like 00123 is treated as a number. Long numeric IDs lose precision in some JSON parsers. Treat codes as strings unless you will do arithmetic with them.

Repeating structures

Order lines, multiple addresses, multiple phone numbers. Know whether the target accepts many, one, or a fixed number of each, and what you do with the rest.

Document your mappings

A mapping that lives only inside a tool is hard to review, hard to hand over and impossible for the business to check. Keep a mapping specification next to it: a table with source field, target field, rule, example values and the owner of each lookup table. It doesn’t need to be fancy, a spreadsheet is fine, as long as it is kept up to date and reviewed when the mapping changes.

Two habits help a lot:

Keep real sample documents (anonymised) for every interface, including the awkward ones: Greek characters, long names, zero-value lines, credit notes.
Test mappings against those samples automatically. Our integration testing guide explains how to build that into your workflow.

Common mistakes

Mapping from field names instead of meaning. date in the e-shop may be the order date, the payment date or the last-modified date. Ask.
Hardcoding codes. VAT and payment codes in the middle of a mapping become a maintenance problem the first time they change.
Ignoring the reverse direction. If data flows back (status, invoice numbers), map that too and make sure the IDs line up.
Testing only the happy path. One clean sample order proves very little.
Point-to-point mappings everywhere. With many systems, mapping each pair directly multiplies the work. A shared intermediate format can help; see the canonical data model.

How to compare tools

Which formats do you need? If EDI or fixed-width files are in scope, check native support, not “possible with a script”.
Graphical, code, or both? Graphical mappers are easier to review with non-developers. Code-based mappers are easier to diff and version. Many teams want both.
How are lookups maintained? Can code tables be edited without redeploying, and set differently per environment?
Encoding and locale control. Can you set character sets, number formats and date masks explicitly?
Testing. Can you run a mapping against a sample and inspect the result quickly?
Where does it run? Standalone design tool, part of an integration platform, or a library inside your own code.

Tools in this space

In alphabetical order. Each fits a different way of working.

Altova MapForce. A dedicated graphical mapping tool with broad format support (XML, JSON, databases, flat files, EDI, Excel). Popular with teams that want a strong visual mapper and can generate code or run mappings on a server product.
Azure Logic Apps Data Mapper. A visual mapper in Logic Apps (Standard) for XML and JSON transformations, with functions for common conversions. A natural option if your integrations already run on Azure.
Boomi maps. In the Boomi integration platform, a Map component connects a source profile to a destination profile, with built-in functions for dates, numbers, strings and lookups, and Cross Reference Tables for code translations. It fits best when the mapping is part of a wider integration flow you also build in Boomi, because connectors, mapping and error handling sit in the same process.
DataWeave in MuleSoft. A functional transformation language built into MuleSoft Anypoint. Developers like its expressiveness for JSON, XML and CSV, and the fact that mappings are code that can be versioned and unit-tested.
XSLT and jq. Open standards and tools rather than products: XSLT for XML, jq for JSON. Free, powerful and widely supported, but they need developers, and complex mappings can become hard for others to maintain.

If you already have an integration platform, its own mapper is usually the right starting point. A standalone tool earns its place when mapping is a large part of the work by itself, for example many EDI partners.

Where to go next

Two practical articles show these ideas in Boomi:

Mapping a JSON order to XML with Boomi maps and profiles: profiles from samples, date formats, lookups and testing.
Translating codes between systems with Boomi Cross Reference Tables: VAT categories, payment methods and warehouse codes without hardcoding.