How to Merge CSV Files with Different Headers

Appending CSV files by row position is safe only when their columns mean the same thing and appear in the same order. If one file begins Name,Email,Phone and another begins Email,Name,City, a simple copy-and-paste puts valid values under the wrong headings.

The safer approach has two stages: map equivalent headers to a common schema, then align the files by column name.

Decide what kind of merge you need

Suppose one export contains:

Name,Email,Phone
Ada,ada@example.com,08000000000

and another contains:

Full name,Email address,City
Ben,ben@example.com,Abuja

A union keeps every column. The result has name,email,phone,city, with blanks where a source did not supply a value.

An intersection keeps only columns shared by every file. Here that would retain name and email after the headers are mapped. It produces a narrower file but discards phone and city.

Neither operation deduplicates people. It only stacks rows using a defined schema.

Build a header map

Header matching should be explicit. These pairs may be equivalent in your data:

Source header	Standard header
Full name	name
Customer Name	name
Email address	email
E-mail	email
Mobile	phone

Do not merge columns merely because their labels look similar. Order date and Delivery date are both dates but represent different events. Confirm meanings with the source system or data owner.

Merge safely with Pandas

from pathlib import Path
import pandas as pd

HEADER_MAP = {
    "full name": "name",
    "customer name": "name",
    "email address": "email",
    "e-mail": "email",
    "mobile": "phone",
}

def normalize_header(value: str) -> str:
    cleaned = " ".join(value.strip().lower().split())
    return HEADER_MAP.get(cleaned, cleaned.replace(" ", "_"))

frames = []
for path in Path("exports").glob("*.csv"):
    frame = pd.read_csv(path, dtype=str, keep_default_na=False)
    frame.columns = [normalize_header(column) for column in frame.columns]

    if frame.columns.duplicated().any():
        duplicates = frame.columns[frame.columns.duplicated()].tolist()
        raise ValueError(f"{path.name} maps multiple columns to {duplicates}")

    frame["source_file"] = path.name
    frames.append(frame)

merged = pd.concat(frames, ignore_index=True, sort=False)
merged.to_csv("merged.csv", index=False)

Reading as strings preserves values such as account numbers with leading zeros. The duplicate-column check catches a dangerous case: a source may contain both Email and Email address, which your map turns into the same output header. Decide which field wins instead of letting one disappear silently.

The source_file column is optional, but it makes errors traceable. Remove it only after validation if the destination does not accept extra columns.

A manual spreadsheet method

For two small files, create a new workbook with one standard header row. Copy each source column underneath its matching destination column rather than pasting entire rows. Leave cells blank when a source lacks a field.

Before export, format identifier columns as text. Save the workbook as a working copy, then export the combined sheet to CSV. This is slower than a scripted merge, but the mapping remains visible.

Problems to resolve before merging

Repeated headers inside a file often appear when several exports were pasted together. Remove those rows. Normalize trivial differences such as surrounding spaces and capitalization, but keep genuinely different fields separate.

Check value conventions as well as headers. Two date columns may use MM/DD/YYYY and DD/MM/YYYY. A price field may contain different currencies. A status field may use Complete in one system and Closed in another. Matching labels do not guarantee matching data.

Validate the combined file

Add the source row counts and compare the total with the merged output. Profile blank rates by source, inspect a sample from every file, and confirm that important columns contain plausible values. Email columns should not suddenly contain cities; phone columns should not contain dates.

Finally, import a small copy into the destination system before sending the full dataset. A structurally valid CSV can still fail business rules such as required fields, accepted codes or unique identifiers.

Save the mapping as part of the workflow

If this merge will happen again, store the header mapping in a small configuration file or documented table rather than recreating it from memory. Include the source system, source header, destination header, transformation rule and whether the field is required. Version the mapping when either system changes.

Reject unexpected headers instead of quietly dropping them. A new column may represent an important product change, while a missing column may indicate that the export configuration changed. Producing an exception report is safer than generating a plausible but incomplete merged file.

Treat values independently from labels. Renaming mobile to phone does not normalize country codes, and renaming created_at to date does not resolve time zones. Header alignment is the first stage, not proof that the data is semantically compatible.

For files whose headers already match, the HappyCSV merge tool provides a quick browser-based workflow. When headers have different meanings, define the mapping first.