BeginnerLesson 4 of 305 min read

Moving Data Between Steps: Mapping and Transforms

Map fields from an email into a sheet row, tidy values on the way and keep a visible error list for the records that do not fit.

In this lesson you will

  • Map source fields to target columns
  • Tidy values with small transforms
  • Handle a missing field without crashing
  • Avoid date confusion

About 20 minutes

The row that came out wrong

Ravi’s first automation copies each new email into a spreadsheet so he can see all enquiries in one place. On day one the sheet looks like a mess. Sam’s email address is shown in capitals with spaces around it, so the sheet treats it as different from sam@example.com. One subject has two spaces and a gap at the start. The date reads as a long string of numbers and letters. And one row has an empty customer name. Nothing is broken in the platform. The data simply arrived in one shape and the sheet wanted another. Fixing that gap is called mapping, and tidying values on the way is called transforming.

Mapping and transforming

Mapping means saying which source field feeds which target column: the sender’s address goes into the Email column. Transforming means changing the value on the way: lower-casing an address, trimming spaces, turning an ISO date into a short date. Real platforms offer these as little formula or text tools, often called formatters. The idea is always the same: take a value, apply a small function, pass on the result.

Incoming data often has a nested shape, where one field sits inside another, such as the sender’s name inside a ‘from’ block. Platforms usually show this as a path with dots, and the program below reads such paths too.

A field mapper with a safety net

Below, a mapping table lists each target column with a source path and an optional transform after a bar. The loop reads the value, applies the transform and builds a row. If a source field is missing, as the second email has no sender name, the loop does not crash. It leaves the cell empty and writes the problem into an error list, so that a person can see exactly which row and which field failed.

from datetime import datetime

records = [
    {"from": {"name": "Sam Lee", "address": " SAM@Example.com "}, "subject": "  Quote for flyers ", "date": "2026-11-02T09:15:00"},
    {"from": {"address": "ana@example.org"}, "subject": "Where is my order?", "date": "2026-11-02T10:40:00"},
    {"from": {"name": "Kim Ray", "address": "kim@example.net"}, "subject": "Invoice 118", "date": "2026-11-03T08:05:00"},
]
transforms = {
    "lower": lambda v: v.lower().strip(),
    "strip": lambda v: " ".join(v.split()),
    "short_date": lambda v: datetime.fromisoformat(v).strftime("%d %b"),
}
mapping = {"customer": "from.name", "email": "from.address|lower",
           "subject": "subject|strip", "received": "date|short_date"}

def get(rec, path):
    for part in path.split("."):
        rec = rec[part]
    return rec

rows, errors = [], []
for i, rec in enumerate(records, 1):
    row = {}
    for target, spec in mapping.items():
        path, _, fn = spec.partition("|")
        try:
            value = get(rec, path)
            row[target] = transforms[fn](value) if fn else value
        except KeyError as missing:
            row[target] = ""
            errors.append((i, target, str(missing).strip("'")))
    rows.append(row)
print("rows built:", len(rows))
print("errors:", errors)
print("row 1 email:", rows[0]["email"])
print("row 1 subject:", rows[0]["subject"])
print("row 3 received:", rows[2]["received"])
rows built: 3
errors: [(2, 'customer', 'name')]
row 1 email: sam@example.com
row 1 subject: Quote for flyers
row 3 received: 03 Nov

The program built 3 rows and reported one problem: [(2, ‘customer’, ‘name’)]. That means row 2 had no customer name, which is the missing name from the sample data. Row 1’s email came out as sam@example.com, with the capitals and spaces removed, and its subject as Quote for flyers, with the extra spaces squeezed out. Row 3’s date, which started as a long ISO timestamp, became 03 Nov. The safety net matters most. Without it, one odd email would stop the whole run, and every later email would wait in a queue unprocessed.

Habits for clean data

  • Trim spaces and lower-case email addresses before comparing them.
  • Pick one date style and convert every date to it.
  • Decide what to do when a field is missing: skip, fill a default or flag it.
  • Keep a column for errors or notes so problems stay visible.
  • Test with at least one messy sample, not only a perfect one.

Dates deserve extra care. A date written 03/11 means the third of November in some countries and the eleventh of March in others. If your emails or sheets mix styles, convert to an unambiguous format such as the year, month and day order, then display it however your readers expect.

Common mistake: trusting that data is complete

Assuming every field is always present. Senders leave names blank, forms skip answers and attachments go missing. Decide in advance what your workflow does with an empty value.

Made-up sample data

The emails, names and dates here are made up, and example.com addresses are reserved for examples. Real platforms offer formatting tools with their own names and options, so look for text and date formatters in yours.

Take five real or invented messages and write down, for each target column, where the value comes from and what tidying it needs. Include one message with something missing.

Practice for this lesson

Check what you learned

Five quick questions. Pick an answer and you will see straight away whether it is right and why.

What does mapping mean in an automation?

  1. Drawing a picture of the office
  2. Sending a message
  3. Saying which source field feeds which target column
  4. Deleting old data

Mapping connects a source field to a target field.

What is a transform?

  1. A new trigger
  2. A kind of password
  3. A backup
  4. A small change applied to a value on the way

Examples are lower-casing, trimming and reformatting dates.

Why did the program write the problem into an error list?

  1. To stop all runs
  2. So a person can see which row and field failed
  3. To hide it
  4. To change the date

Visible errors let one bad record be fixed without halting the rest.

Why trim spaces and lower-case email addresses?

  1. To make them longer
  2. To send them faster
  3. So the same address always looks the same
  4. To avoid using a sheet

Otherwise the same person can look like two different people.

What is a safe way to handle a date that could be read two ways?

  1. Convert to an unambiguous format first
  2. Guess
  3. Delete the row
  4. Use capital letters

A year-month-day format cannot be mistaken for another order.

Summary

  • Mapping connects fields; transforming tidies values.
  • A missing field should be flagged, not crash the run.
  • Trim, lower-case and standardise before comparing.
  • Use an unambiguous date format inside the workflow.

Next lesson

Lesson 5 adds an AI step that classifies and drafts, and shows how to use confidence safely.

Questions

Do I need formulas to transform data?

Many platforms give point-and-click formatters. The lesson shows the logic so that you recognise what those tools do.

What if a field is sometimes empty?

Decide in advance: skip the row, use a default, or flag it for a person. Flagging is the safest choice when you are unsure.

Are those names and emails real?

No. They are invented, and example.com is a domain reserved for examples.

Progress is saved in this browser.