What the Data Mapping Transformer does
This tool converts rows from one layout to another with rules you set per output field: copy a field under a new name, join several fields, look codes up in a table, fill a default when a value is empty, change letter case, and cast to text, whole number, decimal, true/false or an ISO date. You see the transformed rows and every value that failed to convert, then download the result as CSV or JSON and save the mapping itself as portable JSON.
It is the small, repeatable job that sits between two systems - a CRM export that has to become a billing import, or a nested API response that someone needs as a flat sheet. The rules are declarative data, never code, and your file is processed in the browser without being uploaded.
How to use it
- Load your source: drop a CSV or JSON file, paste it, or load an example. CSV needs a header row. JSON can be an array of objects, an object holding one array, or JSON Lines; nested values are addressed with dotted names such as
billing.city. - Set up the output fields. "One field per source column" creates a starting mapping that copies every column under a cleaned-up name. Then, for each field, set the target name, the rule (copy, join, look up or fixed value), the source field(s), a default, the letter case and the output type.
- Say how dates in the source are written - ISO, day first or month first.
05/03/2024is 5 March in the UK and 3 May in the US, and no tool can guess which one a file means. - Choose Transform. Check the value problems list: every failed conversion and every lookup miss is listed with its row and field, and failed values are left empty rather than guessed.
- Download the CSV or JSON output, and download the mapping so the same conversion can be repeated next month or handed to a colleague.
Reading the results
Each field applies its steps in a fixed order: read the source, trim spaces, look up, apply the default if the value is still empty, change case, then cast. The order matters - a default of 0 is applied before the cast, so an empty Orders cell becomes the number 0, not a failure.
"Rows with a failed value" counts rows where at least one cast failed: a date that does not exist, text in a number column, a decimal in a whole-number column. Those cells are empty in the output, so the count is the number of rows you need to fix at source or accept as incomplete.
Lookup misses are warnings, not failures. The fallback decides what happens: keep the original code, use the default, or leave the cell empty. Every miss is listed either way, so an unmapped code cannot slip through silently.
Worked example: a CRM export turned into a billing import
The CRM example has five customers with columns Customer ID, First Name, Last Name, Country, Signup Date (day first), Orders and Newsletter. The billing system wants account_ref, full_name, country_name, signed_up_on as YYYY-MM-DD, order_count as a whole number, marketing_opt_in as true or false, and a fixed source_system tag.
Row 1 has ada and LOVELACE: trimmed, joined with a space and title-cased, that becomes Ada Lovelace. GB looks up to United Kingdom, 10/12/2023 read day-first becomes 2023-12-10, and yes becomes true.
Row 3 shows the failures. 31/02/2024 is not a real date, and 3.5 is not a whole number, so both cells are left empty and reported. The empty Last Name is skipped when joining, so the name is just Lin rather than "Lin " with a trailing space, and the empty Newsletter falls back to the default false. Row 4's empty Orders becomes 0 through its default. Row 5's country XX is not in the lookup table, so it gets the default Unknown and a warning.
The result: 5 rows transformed, 1 row with failed values (two failures, both in row 3) and 1 lookup miss - three problems in all, each listed with its row.
Why the mapping is JSON, not a script
A mapping file is a list of plain rules such as {"target": "order_count", "op": "copy", "sources": ["Orders"], "default": "0", "cast": "integer"}. There are only four operations and five types, and nothing in the file is ever evaluated. That makes a mapping safe to receive from someone else, easy to review in a pull request, and stable: the same mapping on the same data always gives the same output.
The trade-off is reach. Conditional logic, arithmetic between fields and one-to-many reshaping are deliberately out of scope; they belong in a proper ETL tool, a SQL query or a script you control and test.
Casting rules, stated exactly
Whole numbers accept an optional sign and digits only, and refuse values beyond 9,007,199,254,740,991 because JavaScript cannot hold them exactly. Decimals use a dot and allow an exponent; thousands separators such as 1,234 are refused rather than guessed, because in much of Europe that means one point two three four. True/false accepts true, false, yes, no, y, n, 1 and 0 in any letter case.
Dates are checked against the real calendar, including the leap-year rules: 29 February exists in 2024 and 2000 but not in 2023 or 1900. ISO timestamps such as 2026-03-05T10:00:00Z keep their date part as written, without converting time zones.
Limitations: what the result does not prove
- It maps one row to one row. It does not join files, split one record into many, aggregate, or de-duplicate - use the CSV join planner or a database for those.
- A successful transform proves the values have the declared types, not that they are right. A plausible but wrong country code that happens to be in your lookup table converts cleanly.
- Date handling covers ISO dates and numeric day-first or month-first dates. Month names, two-digit years and time-zone conversion are not interpreted.
- The preview shows the first 500 rows; downloads include every transformed row up to the 200,000-row limit.
Privacy: where your data goes
Everything you paste, type or drop is processed in this browser tab. It is not uploaded, logged, stored or sent to analytics. Session recording and tag-manager scripts are switched off on this page.
Standards and sources
- OWASP CSV Injection - checked 19 Sep 2026
- RFC 4180 - Common Format and MIME Type for CSV Files
- RFC 8259 - The JSON Data Interchange Format
- RFC 3339 - Date and Time on the Internet: Timestamps
Frequently asked questions
How do I map CSV columns to a different set of field names?
Load the CSV, choose "One field per source column" to get a copy rule for every column, then rename each target and delete the ones you do not need. Reorder with Up. Transform shows the result, and Download CSV writes the new header row with your target names in your order.
Can I combine first and last name into one field?
Yes. Set the rule to "Join several fields", list the sources separated by commas (for example First Name, Last Name) and choose what to join them with. Empty parts are skipped, so a missing last name does not leave a trailing space. Add Title Case if the source mixes capitals.
How does the lookup rule handle codes that are not in the table?
You choose. Keep leaves the original code in place, Use the default replaces it with the field's default, and Leave empty blanks it. Whichever you pick, every unmatched code is listed as a lookup miss with its row number so you can extend the table.
Why did a value come out empty instead of converted?
It failed its cast - for example 3.5 in a whole-number field, 1,234 in a decimal field, or 31/02/2024 as a date. The tool leaves the cell empty rather than guessing, and the value problems table gives the row, field and reason, so you can correct the source or change the rule.
Can I reuse the same mapping on next month's file?
Yes. Download the mapping, or copy it from the Mapping as JSON section. Next time, load the new file, paste the mapping and choose Apply mapping JSON. The file contains rules only - target names, sources, lookup tables and types - never any rows of your data.
Does it handle nested JSON input?
Yes. Use dotted names to reach nested values, such as user.email or billing.city. The source summary lists the dotted names it found. For JSON output you can also nest the result again by giving targets dotted names and ticking the nested-objects option.
Is it safe to open the CSV output in Excel?
The export neutralises formula injection: any text cell beginning with =, +, - or @ is prefixed with an apostrophe, as OWASP recommends, so values from an untrusted source cannot run as formulas. It is also written as UTF-8 with a byte order mark so accented names and emoji display correctly.
Last reviewed by the A2Z.Tools team against the sources listed above.
