Skip to content
CSVTidy

How to Merge Multiple CSV Files Into One

3 min read

Combine CSV files with the command line, Python, Power Query, or a browser tool — and handle the mismatched headers that break naive approaches.

You have twelve monthly exports and need one file. Every method below works when the files are identical in structure. They are almost never identical in structure, and that's the part worth getting right.

The command line, when headers truly match

On macOS or Linux:

# Keep the header from the first file only
head -n 1 jan.csv > merged.csv
tail -n +2 -q *.csv >> merged.csv

tail -n +2 skips the first line of each file; -q suppresses the filename banners tail prints when given multiple files.

On Windows PowerShell:

Get-ChildItem *.csv | ForEach-Object { Import-Csv $_ } | Export-Csv merged.csv -NoTypeInformation

The PowerShell version is safer — it parses properly rather than treating lines as text, so quoted fields containing newlines survive.

The classic mistake is cat *.csv > merged.csv. That keeps every header row, so you end up with name,email scattered through the middle of your data. Some of those rows will then be imported as records.

Python with pandas

import glob
import pandas as pd

files = sorted(glob.glob("*.csv"))
df = pd.concat((pd.read_csv(f) for f in files), ignore_index=True)
df.to_csv("merged.csv", index=False)

To track where each row came from — worth doing more often than people do:

frames = []
for f in files:
    part = pd.read_csv(f)
    part["source_file"] = f
    frames.append(part)
df = pd.concat(frames, ignore_index=True)

pd.concat already handles mismatched columns by taking the union and filling gaps with NaN. Convert those to empty strings before writing if your destination dislikes the literal text nan:

df.fillna("").to_csv("merged.csv", index=False)

Excel Power Query

For a folder of files, without writing code:

  1. Data → Get Data → From File → From Folder
  2. Select the folder, then Combine & Load
  3. Power Query builds a query that appends every file and adds a Source.Name column automatically

The real advantage is that it's repeatable — drop next month's file into the folder and hit Refresh. For a recurring monthly merge this beats any script you'd have to remember how to run.

The problem nobody warns you about: headers drift

Your January export has:

id,name,email

Your June export has:

id,name,email,phone

And September, after someone renamed a field:

id,full_name,email,phone

Naive concatenation produces a file where column four is sometimes phone and sometimes nothing, and name and full_name are two half-empty columns describing the same thing.

Before merging, diff the headers. One line:

import csv, glob

for f in sorted(glob.glob("*.csv")):
    with open(f, newline="", encoding="utf-8-sig") as fh:
        print(f, next(csv.reader(fh)))

Thirty seconds of reading that output saves an afternoon of confusion later.

Then decide deliberately:

  • Column added later → union is correct; old rows get empty cells.
  • Column renamed → rename before merging, or you get two columns that should be one.
  • Column order differs → merge by header name, never by position. Any tool that merges positionally will silently put emails in the phone column.

Duplicates across files

Overlapping exports produce duplicate records. Merge first, then deduplicate — but normalize before you deduplicate, or near-matches survive:

df["email"] = df["email"].str.strip().str.lower()
df = df.drop_duplicates(subset=["email"], keep="last")

keep="last" retains the most recent version when files are in chronological order, which is usually what you want. There's more on this in removing duplicate rows.

Encoding and delimiter mismatches

Files from different sources often differ in ways that aren't visible:

  • One export is UTF-8, another is Windows-1252 — merging produces mojibake in half the rows
  • One uses commas, another semicolons — the semicolon file merges as a single column

Check both before merging. If a file opens as one wide column, it's a delimiter mismatch, not a corrupt file.

Checklist

  • Print every file's header row first and compare them.
  • Merge by header name, never by column position.
  • Rename drifted columns before merging, not after.
  • Add a source_file column — you will want it when something looks wrong.
  • Normalize, then deduplicate.
  • Verify the merged row count equals the sum of the parts, minus any duplicates you intended to remove.

Keep reading