Building a Python utility to merge CSV files with different schemas

I created this utility after running into a familiar data problem: several CSV files represented the same kind of records, but none used quite the same column layout. One export called a field customer_name, another used name, and a third split the value into first_name and last_name. Combining the files by simply appending their lines produced a dataset that looked complete but was difficult to trust.

The tool was designed as a small, dependable command-line program rather than a large data-processing framework. It reads files from a directory, compares their headers, maps equivalent fields, fills missing values, and writes one consolidated CSV file. Python was a natural choice because its standard library handles text files, dictionaries, command-line arguments, and CSV parsing without adding installation overhead.

This sort of utility is useful for Australian developers and small teams who receive exports from councils, suppliers, CRMs, spreadsheets, or older internal systems. A business in Melbourne might combine a modern CRM export with a workbook maintained in Brisbane, while a Perth contractor may receive monthly reports from several clients. The data represents the same records, but the schemas have evolved independently.

I also wanted the program to be transparent. A merge should report which files it opened, which columns it discovered, how fields were matched, and whether malformed rows were skipped. A quiet script that produces an incorrect file is far more dangerous than one that takes a few seconds to explain its decisions.

Defining the schema problem

The first design decision was to distinguish between a source schema and a canonical schema. Each input file has its own header row, while the canonical schema describes the columns that should appear in the output. For example, email_address, email, and contact_email can all map to email.

A simple configuration dictionary makes those relationships explicit:

FIELD_ALIASES = {
    "name": {"name", "customer_name", "full_name"},
    "email": {"email", "email_address", "contact_email"},
    "postcode": {"postcode", "post_code", "zip"},
}

In practice, I normalise headers before comparing them. Lowercasing, trimming whitespace, replacing spaces with underscores, and removing punctuation prevents superficial differences from creating separate fields. A header such as Customer Name then becomes comparable with customer_name.

The canonical order matters as well. I keep important fields first, followed by additional columns discovered in the source files. This gives users a predictable output while preserving information that has not yet been added to the alias configuration.

Parsing files safely with Python

The csv module is preferable to splitting lines on commas. Fields can contain commas, quotation marks, or embedded line breaks, and manually parsing those cases quickly becomes unreliable. I open files with newline="" and specify an encoding, usually UTF-8 with a fallback for older exports.

import csv

def read_rows(path):
    with path.open("r", encoding="utf-8-sig", newline="") as handle:
        reader = csv.DictReader(handle)
        return reader.fieldnames or [], reader

The utf-8-sig option handles a byte-order mark sometimes added by spreadsheet applications. That small detail prevents the first header from being interpreted as something like \ufeffname. For files supplied by older Windows systems, an optional encoding flag can support cp1252, which is still encountered in Australian office workflows.

I also validate the presence of a header row and give each file a useful error message. A blank file, an unreadable path, or a duplicate header should be reported with its filename rather than causing a vague traceback. This matters when an operator is merging dozens of monthly exports on a busy arvo.

Normalising and mapping columns

After reading a file, the utility converts each source row into the canonical representation. It first normalises the source header, looks for a known alias, and assigns the source value to the corresponding output field. Unmatched columns can either be retained using their normalised names or reported for review.

When two source columns map to the same canonical field, the program needs a deterministic rule. I use a preference order and preserve the first non-empty value, while recording a warning when conflicting values are found. Silent overwriting can hide genuine data quality problems, especially when an old spreadsheet and a current CRM disagree about a phone number.

Australian data introduces a few practical details. Dates commonly arrive as dd/mm/yyyy, and postcodes such as 0800 for Darwin must remain text so the leading zero is not lost. I avoid converting every value to a number and treat identifiers, phone numbers, postcodes, ABNs, and account codes as strings. That keeps exports usable for organisations operating across Sydney, regional New South Wales, and remote areas.

Merging rows without losing information

The merger maintains a set of output columns and writes each transformed row through a csv.DictWriter. Missing fields are emitted as empty strings, while extra fields can be appended after the canonical columns. This creates a rectangular file that spreadsheet programs and downstream scripts can read consistently.

def canonical_row(source_row, header_map, columns):
    result = {column: "" for column in columns}
    for source_name, value in source_row.items():
        target = header_map.get(source_name)
        if target and not result[target]:
            result[target] = (value or "").strip()
    return result

The tool can merge files in sorted filename order, which makes repeated runs reproducible. I include the source filename as an optional column because it helps trace records back to a particular supplier or reporting period. Duplicate detection is handled separately, using a configured key such as email, customer ID, or a combination of name and postcode.

For Australian clients, this can be useful when data arrives from a local council, a national retailer, and a third-party service provider. A customer in Adelaide may appear in two systems with slightly different spelling, so the program should distinguish schema alignment from identity matching. Combining columns is safe; deciding that two people are the same requires a more deliberate rule.

Reporting, testing, and distributing the utility

A command-line interface keeps the utility easy to automate:

csvmerge input/*.csv --output merged.csv --config fields.json

The program reports the number of files read, rows written, unknown columns, duplicate keys, and rejected records. I write diagnostics to standard error so the merged CSV remains clean when the command is used in a shell pipeline or scheduled job.

Testing uses small fixtures for different header orders, missing fields, quoted commas, blank values, duplicate aliases, UTF-8 byte-order marks, and malformed rows. I also test postcode preservation and Australian date strings without forcing the merger to reinterpret them. The goal is schema consolidation, not accidental data transformation.

The same attention to useful diagnostics appears in my Apache log analyser, where input files and their results need to remain understandable to the person running the tool. A utility earns trust through predictable behaviour, clear licensing, and output that can be inspected without reverse-engineering the code.

The final program stays intentionally modest: standard-library Python, explicit configuration, repeatable ordering, and warnings that explain what happened. That combination is enough to turn a pile of inconsistent CSV exports into a practical dataset without pretending that messy source data has become perfect.