deploy notes
Automation

Merge CSV files with Python without mixing up their columns

Combine matching CSV exports into one file, check headers and row counts, and stop on incompatible input while keeping the original files intact.

Beginner5 min read
In this guide

If you have several exports of the same report, you can put their rows into one CSV with a single header. The important part is checking that every file has the same columns before writing the result.

This guide appends rows: Monday’s orders, followed by Tuesday’s orders. It does not join an orders file to a customer file by an ID, add totals, or remove duplicates. You will get a new file and a row count, while keeping the originals.

Choose your exports

Use a computer with Python 3.12 or newer, a terminal, and a plain-text editor. No packages or server are needed. Check Python with py --version in Windows PowerShell or python3 --version in macOS/Linux Terminal. If it is missing, use the official Python downloads and reopen the terminal before continuing.

Create a folder named csv-merge. Put copies of your small UTF-8, comma-separated exports there. Each file must start with the same non-empty, unique column names in the same order. Completely blank lines are ignored; empty cells are preserved.

For a first run without your own exports, save these two fictional files as UTF-8 plain text. In Notepad, choose All files when saving so the names do not gain .txt.

monday.csv:

order_id,item,quantity
1001,"Notebook, blue",2
1002,Café mug,1

tuesday.csv:

order_id,item,quantity
1003,Pen,4

The result should have three records, not four: the second header is not an order.

Save one merge script

In the same folder, save this complete code as merge_csv.py:

import argparse
import csv
import sys
from pathlib import Path

sys.stdout.reconfigure(errors="backslashreplace")

parser = argparse.ArgumentParser(description="Append matching CSV exports.")
parser.add_argument("output")
parser.add_argument("inputs", nargs="+")
args = parser.parse_args()

try:
    paths = [Path(name).resolve() for name in args.inputs]
    target = Path(args.output).resolve()
    if len(paths) < 2 or len(set(paths)) != len(paths):
        raise ValueError("Choose at least two different input files.")
    if target in paths or target.exists():
        raise ValueError("Choose a new output filename, not an existing file.")

    header = None
    records = []
    counts = []
    for path in paths:
        with path.open(encoding="utf-8-sig", newline="") as source:
            reader = csv.reader(source, strict=True)
            names = next(reader, None)
            if not names or any(not name.strip() for name in names):
                raise ValueError(f"{path.name}: missing column names.")
            if len(names) != len(set(names)):
                raise ValueError(f"{path.name}: repeated column names.")
            if header is None:
                header = names
            if names != header:
                raise ValueError(f"{path.name}: columns or their order differ.")
            count = 0
            for row in reader:
                if not row:
                    continue
                if len(row) != len(header):
                    raise ValueError(f"{path.name}: wrong cell count near line {reader.line_num}.")
                records.append(row)
                count += 1
            counts.append((path.name, count))

    # Create only after all inputs pass; 'x' refuses to overwrite a file.
    with target.open("x", encoding="utf-8", newline="") as output:
        writer = csv.writer(output)
        writer.writerow(header)
        writer.writerows(records)
    for name, count in counts:
        print(f"{name}: {count} records")
    print(f"Wrote {len(records)} records to {target.name}")
except (OSError, UnicodeError, csv.Error, ValueError) as error:
    sys.exit(f"Merge failed: {error}")

The script checks every input before creating the output. It keeps the rows in memory, so use it for small exports that comfortably fit your computer’s available memory, not multi-gigabyte datasets.

Run the merge and check the result

Open your terminal and enter cd "your folder path", replacing the quoted text with the full path to csv-merge. Then run:

python3 merge_csv.py combined.csv monday.csv tuesday.csv

On Windows, use py instead of python3. The first filename is the new output; the remaining filenames are inputs in the order you want their rows. For your own files, replace the names and add further input filenames at the end. Quote any filename containing spaces.

Expected report for the example:

monday.csv: 2 records
tuesday.csv: 1 records
Wrote 3 records to combined.csv

Open combined.csv in your text editor. It should have one header and orders 1001, 1002, and 1003, in that order. Check that Notebook, blue remains quoted as one cell. Compare the reported total with the sum of the input counts before using the file elsewhere.

A header-only input contributes zero records; a completely empty file is rejected because it has no header. If the total is unexpectedly zero, investigate your exports rather than treating the new file as a completed report.

Fix an input problem without hiding it

If the script reports different columns, compare the first row of each input. Even the same names in a different order are rejected. Export the same report layout again, or deliberately put columns into the same order in a spreadsheet and export a new CSV copy. Do not just rename the header: that leaves the values under the wrong names.

For a wrong cell count, check quotation marks around values containing commas. For an encoding error, export UTF-8. If unsure, check one CSV’s columns first.

If combined.csv already exists, choose a fresh output name such as combined-v2.csv. This avoids accidentally overwriting a previous result or feeding it back into the merge. The script does not choose files automatically, so list only the original exports you intend to combine.

If saving fails or you interrupt the program before its success message, the new output may be incomplete. Leave the originals alone and rerun with a new output name after fixing the cause, such as insufficient disk space.

Merging does not prove that every order is unique. If your date ranges overlap, check duplicates using a clear record key before importing the result.

← Back to automation guides