How to Merge Two CSV Files: 5 Methods (Excel, Python, SQL, Command Line & Fuzzy Matching)

"Merge two CSV files" is one of those tasks that sounds trivial until you actually try it. The right method depends on three things: what "merge" means for your data, how big the files are, and how clean the join key is.

This post walks through five practical methods, ordered roughly by increasing power — from a 30-second copy-paste in Excel all the way to a fuzzy matching workflow that handles the messy real-world case where the join column doesn't line up. For each method, you'll see the shortest working code (or steps), what it's good at, and where it breaks.

Stack vs Join: Pick the Right Operation

Ninety percent of the confusion around "merging CSVs" comes from mixing these up.

Stacking (concatenating, appending)

You have two files with the same columns and you want one bigger file with all the rows.

File A:                     File B:                     Stacked:
id | name | email           id | name | email           id | name | email
--------------------        --------------------        --------------------
1  | Bob  | bob@a.com       4  | Dan  | dan@d.com       1  | Bob  | bob@a.com
2  | Ann  | ann@b.com       5  | Eve  | eve@e.com       2  | Ann  | ann@b.com
3  | Cam  | cam@c.com                                   3  | Cam  | cam@c.com
                                                        4  | Dan  | dan@d.com
                                                        5  | Eve  | eve@e.com

Joining (matching, lookup)

You have two files with different columns but a shared key, and you want to pair each row in A with the matching row in B.

File A:                     File B:                     Joined:
id | name                   id | email                   id | name | email
------------                -----------------            ----------------------
1  | Bob                    1  | bob@a.com               1  | Bob  | bob@a.com
2  | Ann                    2  | ann@b.com               2  | Ann  | ann@b.com
3  | Cam                    3  | cam@c.com               3  | Cam  | cam@c.com

Stacking is what most command-line tools and Excel copy-paste do. Joining is what VLOOKUP, pandas merge, SQL JOIN, and fuzzy matching tools do. Keep the distinction in mind through the rest of this guide.

Method 1: Excel (or Google Sheets) Copy-Paste

Best for: stacking two small files (<100k rows) with identical columns. One-off.

1. Open both files in Excel or Google Sheets.
2. In file B, select all rows below the header.
3. Copy.
4. In file A, click the empty cell right below the last row.
5. Paste. Save.

For joining on a shared key, use VLOOKUP or, better, XLOOKUP:

=XLOOKUP(A2, FileB!A:A, FileB!B:B, "no match")

This pulls the value from column B of file B for the matching key in file A.

Where it breaks: above ~100k rows Excel gets sluggish. If the join key is a company name, product description, or address, VLOOKUP will return #N/A on the majority of rows — not because they don't match, but because the formatting differs. That's what we cover in why VLOOKUP can't match company names.

Method 2: Excel Power Query

Best for: repeatable stack or join in Excel without writing code. Files up to ~1M rows.

Power Query is the "professional" version of Excel data operations. It handles both stacking (Append) and joining (Merge Queries), and once you build the query you can refresh it against new files.

1. Excel → Data → Get Data → From File → From Text/CSV. Load file A.
2. Repeat for file B.
3. For stacking: Home → Append Queries → pick both.
4. For joining: Home → Merge Queries → pick the shared key columns and a join kind (Left / Inner / Outer).
5. Click Close & Load to write results to a sheet.

Power Query also has a Fuzzy Merge checkbox for join operations. It works in simple cases but has real limits — single-column matching only, one threshold for the whole join, no domain-specific normalization. We cover the gotchas in Excel Power Query Fuzzy Merge Not Working? Here's Why.

Method 3: Command Line (cat / PowerShell)

Best for: stacking many files fast, especially when they're too big to open in Excel. Not for joins.

On Mac or Linux, if all files have the same header:

# Preserve one header, drop the rest, concatenate the bodies
head -1 file_a.csv > merged.csv
tail -n +2 -q file_a.csv file_b.csv >> merged.csv

# Or, if you want to keep both headers stripped and combine many files:
awk 'FNR==1 && NR!=1 { next } { print }' *.csv > merged.csv

On Windows PowerShell:

Get-ChildItem *.csv | ForEach-Object -Begin { $first = $true } -Process {
    if ($first) { Get-Content $_; $first = $false }
    else        { Get-Content $_ | Select-Object -Skip 1 }
} | Set-Content merged.csv

This is the fastest possible method for stacking. On a laptop, it'll merge a hundred 100MB CSVs in a few seconds.

Where it breaks: command-line tools can't join. If your CSV has quoted fields with embedded commas or newlines, cat will happily corrupt them — use a real CSV parser (Python, Miller, xsv) in that case.

Method 4: Python pandas

Best for: anything you'll do more than once, files above ~1M rows, or joins with logic beyond a simple key match.

Stacking:

import pandas as pd

df_a = pd.read_csv("file_a.csv")
df_b = pd.read_csv("file_b.csv")

merged = pd.concat([df_a, df_b], ignore_index=True)
merged.to_csv("merged.csv", index=False)

Joining on a shared key:

merged = pd.merge(
    df_a, df_b,
    on="customer_id",
    how="left",   # left, right, inner, outer
    suffixes=("_a", "_b"),
)
merged.to_csv("merged.csv", index=False)

Joining on two keys (say, name + zip):

merged = pd.merge(df_a, df_b, on=["name", "zip"], how="left")

Handling messy headers or encoding issues:

df = pd.read_csv(
    "file.csv",
    encoding="utf-8-sig",       # strip BOM if present
    dtype=str,                  # keep everything as text to prevent int/date coercion
    keep_default_na=False,      # don't auto-convert 'NA' strings to NaN
)

pandas handles anything up to a few million rows on a laptop. Above that, look at Polars or DuckDB.

Method 5: SQL (SQLite / DuckDB)

Best for: joining CSVs with complex conditions, or when the files are big enough that pandas is running out of memory.

DuckDB is an in-process analytical database that reads CSVs directly. Install with pip install duckdb. Then, from Python or its CLI:

-- Stack
COPY (
  SELECT * FROM read_csv_auto('file_a.csv')
  UNION ALL
  SELECT * FROM read_csv_auto('file_b.csv')
) TO 'merged.csv' (HEADER, DELIMITER ',');

-- Left join on a key
COPY (
  SELECT a.*, b.email, b.phone
  FROM read_csv_auto('file_a.csv') a
  LEFT JOIN read_csv_auto('file_b.csv') b
    ON a.customer_id = b.customer_id
) TO 'merged.csv' (HEADER, DELIMITER ',');

DuckDB will happily join 100M-row CSVs on a laptop. It's the tool most data engineers reach for now instead of spinning up Postgres for one-off CSV work.

Where it breaks: like pandas, SQL joins are strict — the key values must match byte-for-byte. If your join column is a company name, that's not going to happen.

Method 6: Fuzzy Matching (When Keys Don't Line Up)

Best for: joining two CSVs when the shared column is text a human typed — company names, product descriptions, addresses, person names.

This is the case where all five methods above fail. You have two files that should join, and every one of these pairs represents the same entity:

VLOOKUP returns #N/A on every row. pandas merge drops every row. SQL JOIN gives you an empty result set. What you need is a similarity score and a threshold.

Option A: Fuzzy join in Python

Using RapidFuzz (details in our RapidFuzz vs FuzzyWuzzy vs Dedupe guide):

import pandas as pd
import numpy as np
from rapidfuzz import process, fuzz

df_a = pd.read_csv("file_a.csv")
df_b = pd.read_csv("file_b.csv")

scores = process.cdist(
    df_a["company"].tolist(),
    df_b["company"].tolist(),
    scorer=fuzz.token_sort_ratio,
    workers=-1,
)
best_idx  = scores.argmax(axis=1)
best_score = scores.max(axis=1)

df_a["b_company"] = df_b["company"].values[best_idx]
df_a["b_email"]   = df_b["email"].values[best_idx]
df_a["score"]     = best_score

# Keep only confident matches
merged = df_a[df_a["score"] >= 85]
merged.to_csv("merged.csv", index=False)

Option B: No-code fuzzy join

Upload both CSVs to a fuzzy matching tool, pick the join columns (with weights if you have more than one), set a threshold, and export. This is what DedupFuzzy is built for, and it does the same job as the Python code above in about 90 seconds without writing any code. Full walkthrough here: How to Compare Two Excel Files and Find Matching Rows.

The workflow either way:

1. Normalize the join columns in both files (lowercase, trim, strip legal suffixes).
2. Score every pair with a similarity metric.
3. For each row in A, take the best-scoring row in B.
4. Bucket by score: 95+ auto-merge, 75-95 review, below 75 unmatched.
5. Export the merged file with a score column for auditability.

Comparison Table & Picking Guide

Which one should you use?

• Same columns, small file, one-off: Excel copy-paste. Two minutes.
• Same columns, many files, repeatable: command line (or Power Query if you like a UI).
• Different columns, exact key match: Power Query Merge or pandas merge.
• Different columns, files above 5M rows: DuckDB.
• Join key is company names / addresses / product descriptions: fuzzy matching (Python or a UI tool). Nothing else works.

FAQ

Why does my CSV have weird characters after merging?

Almost always an encoding mismatch. File A was saved as UTF-8, file B as Windows-1252, and whatever tool you used didn't reconcile them. In pandas, read both with encoding="utf-8-sig" and re-save the ones that fail. In Excel, use File → Import instead of double-clicking, and pick UTF-8 explicitly.

My files have different column names for the same field. What now?

Rename before merging. In pandas: df_b = df_b.rename(columns={"email_address": "email"}). In Power Query, right-click the header and rename. In SQL, alias with SELECT b.email_address AS email.

How do I merge many CSVs at once, not just two?

Command line handles hundreds of files trivially (awk 'FNR==1 && NR!=1 { next } { print }' *.csv > merged.csv). In pandas: pd.concat([pd.read_csv(f) for f in glob.glob("data/*.csv")]). In DuckDB: read_csv_auto('data/*.csv') with a glob.

What if I need to update a target CSV with rows from a source CSV (upsert)?

That's a join + concat combined. In pandas: left-join to find existing rows and update in place, then concat the unmatched source rows onto the target. In SQL, that's what INSERT ... ON CONFLICT UPDATE or MERGE does. If the join key is fuzzy (company name), do the fuzzy match first to resolve which source rows are updates vs new records.

How do I know if my merge worked?

Row-count check: merged rows == expected. Column-count check. Null-count check on the join key columns. Spot-check 10 random rows. And if it's a fuzzy merge, keep the score column so you can audit it later.

Wrapping Up

Merging two CSVs isn't a single problem — it's a family of problems with very different right answers. The 30-second Excel copy-paste is the right tool for one job. A Python + RapidFuzz pipeline is the right tool for another. DuckDB is the right tool for a third. The trick is matching the method to your actual situation instead of forcing the first tool you know onto every merge.

The trap most teams fall into is trying to make VLOOKUP or pandas merge work on a join column that was never going to line up. If you catch yourself debugging #N/A results row by row, that's the signal to stop and switch to fuzzy matching — either with RapidFuzz or with a UI tool.

Related Reading

• How to Compare Two Excel Files and Find Matching Rows
• How to Match and Merge Two Company Lists
• Why VLOOKUP Can't Match Company Names
• Fuzzy Matching in Python: FuzzyWuzzy vs RapidFuzz vs Dedupe
• The Ultimate Guide to Data Deduplication (2026)