In this guide 6 sections
It is a table. Every row traces one source column to its target field in the new system, with the transform rule, the default for blanks and the person who approved it. Reviewers read it to catch errors before any data loads, so a wrong unit code or a split address surfaces on paper, not in production.
What the document is for
A mapping document is the contract between the old file and the new database, listing every column you hold with the exact field and shape where each will land.
Without it, the developer guesses. With it, the analyst reads one table and sees whether Customer Name, Ph and Addr arrive as the three fields everyone expects.
Treat it as a working sheet. It changes with every test load, so keep a version number and a date at the top, and never overwrite last week’s copy without saving it first.
The columns of a mapping sheet
Each row describes one source column. Keep the layout identical on every row so a reviewer can scan down the Target column and compare like with like across hundreds of rows.
Six columns cover nearly every case. Add a notes column for oddities, such as a header that mixes two meanings, a merged cell or a hidden sheet that only the original clerk understands.
- Source: workbook, sheet and column header, for example Orders.xlsx, Sheet1, column F
- Target: table and field in the new system, with its data type and length
- Transform rule: what changes on the way, written as a plain instruction
- Default for blanks: what to store when the cell is empty
- Owner: the named person who approved the rule
- Status: open, agreed or loaded and checked
Worked examples of transform rules
Take one address cell holding 14 Harbour Road, Eastfield, Bristol BS8 2QT, where the target wants separate street, area, city and postcode fields. Split on commas, trim the spaces and send the last block to postcode, while any row that does not fit goes to an exceptions list for a person. Nothing is dropped silently.
Take a unit column where staff typed Kg, KG, kgs and kilo, against a target that accepts only a controlled code list. One rule maps all four spellings to KG, and any value the list does not recognise is rejected, so a human can decide what the clerk really meant.
Dates deserve their own line. State whether 03/04/2026 means the third of April or the fourth of March, say which reading wins, and record the format of every date column in the source workbook.
Defaults, blanks and who decides
Empty cells cause quiet trouble. If Credit Days is blank for 300 customers, the new system reads zero days, thirty days or an error, so a manager decides and the sheet records it.
Every rule needs an owner who is not the developer, so sales owns customer fields, stores owns item codes and accounts owns tax and ledger fields. Disagreement keeps the row open. One name closes it.
Some columns are better dropped. If nobody has read the Remarks2 column in two years, note the fact on the sheet and agree its removal in writing with the owner of that data area.
- Blank means zero, blank means unknown, or blank is not allowed
- Duplicates: which record survives, and who confirms
- Retired values: map to a current one or archive
How to review one before sign-off
Read it with a sample in hand. Pick ten real rows from the source workbook and trace each cell through the table, because if you cannot say where a value ends up, the document is incomplete.
Confirm that every required target field has a source or a default and that no source column is mapped to two targets without a stated reason. Then run the load on a copy of the data and compare row counts and invoice totals against the original workbook line by line.
Sign off at zero open rows. Print the sheet, pick your ten rows and trace them this week, and do it before the developer writes a single line of the loading script.
Field mapping worksheet
Print it on one page. Each row covers one source column and records the file and header, target table and field, data type, transform rule, blank default, owner, status and notes. Two short lists sit below the table. One lists values that need a controlled code list, while the other lists the columns proposed for removal and the owner who must agree to each removal.
Open a blank worksheet to printQuestions owners ask
Who should write the field mapping document?
An analyst or developer drafts it from the source files, but the business owners of each data area approve their own rows before anything loads. An unread mapping is only a guess.
How is a mapping different from a data dictionary?
A dictionary describes the fields inside one system, while a mapping connects fields across two systems and states the rules for converting each value between them. The two documents complement each other.
Do we need one for a small spreadsheet?
Yes. A short sheet still hides mixed date formats, blank cells and duplicate customers, and they only show up when you write each rule down in plain words. It is cheap insurance.
What if the source column holds two kinds of data?
Give the column two rows, one for each meaning, and write the rule that separates them, such as a prefix or a length test. Agree with the owner how to treat values that fit neither meaning, and record the decision in the notes column before the first test load runs.