How to merge multiple CSV files into one

If the files have identical columns in identical order, the fastest correct merge is a shell one-liner that keeps the first header and drops the rest:

head -n 1 first.csv > merged.csv
tail -q -n +2 *.csv >> merged.csv

Everything else on this page exists because that assumption of identical columns in identical order is false far more often than people expect, and the failure is silent. A merge that misaligns two columns produces a file that opens fine, looks fine, and is wrong.

First, check whether the headers match

Do this before merging, not after. Every subsequent problem is a variation of this one.

for f in *.csv; do echo "$f: $(head -n 1 "$f")"; done

Read the output for four things:

• Different column order. name,email,date in one file and name,date,email in another. Concatenation puts dates in the email column, and nothing complains.
• Different names for the same field. Email, email, E-mail, Email Address. Common when the files came from different exports, or the same export in different years.
• Extra or missing columns. A later export gained a field. The rows are now different widths.
• A byte order mark on the first file. name is not name. It is invisible in every editor and breaks joins by column name.

If all the headers are byte-identical, use the two-line version above and stop reading. If they aren't, the concatenation approach is the wrong tool and you need something that merges by column name.

Merging by column name

DuckDB, if the files are large or the headers differ

One command handles both problems, differing column orders and missing columns, because it aligns on name rather than position:

duckdb -c "COPY (SELECT * FROM read_csv('*.csv', union_by_name=true, filename=true)) TO 'merged.csv' (HEADER)"

union_by_name=true is the part doing the work: columns are matched by header, and a file missing a column gets NULL for it rather than a shifted row. filename=true adds a column recording which file each row came from, which you will want more often than you expect. It is the only way to answer "which export did this bad row arrive in" after the fact.

DuckDB streams, so file size is bounded by disk rather than RAM. A hundred files totalling several gigabytes is unremarkable.

Python, if you need to reconcile the names first

When the same field is called Email in one file and E-mail Address in another, no tool can guess. Normalise explicitly:

import glob, pandas as pd

rename = {"E-mail Address": "email", "Email": "email"}
frames = []
for path in glob.glob("*.csv"):
    df = pd.read_csv(path, dtype=str)
    df.columns = [c.strip().lstrip("") for c in df.columns]
    frames.append(df.rename(columns=rename).assign(source=path))

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

Two details matter more than the merge itself. dtype=str stops pandas inferring types per file. Without it, a customer ID column that is numeric in one file and alphanumeric in another comes out as a mix of 1024 and "01024", and the leading zeros are gone for good. And .assign(source=path) is the same provenance column DuckDB gives you for free.

Power Query, on a desktop without a terminal

Excel's Data → Get Data → From Folder points at a directory and combines every file in it, matching on header. It is good at this, it refreshes when new files land in the folder, and it is the right answer for anyone who will be repeating this merge monthly.

Its limit is Excel's limit: the merged result still has to fit in 1,048,576 rows to land on a sheet. You can load to the data model instead and skip the sheet entirely, which most people don't know and which raises the ceiling considerably.

The append-versus-join distinction

"Merge" means two different operations, and picking the wrong one wastes an afternoon.

Everything above is append. A join needs a shared key and a decision about what happens to unmatched rows, and it is where duplicates get created, because a key that isn't unique on both sides multiplies rows silently. If your row count after a join is larger than either input, that is what happened.

For a join, SQL is the honest tool:

duckdb -c "COPY (SELECT c.*, o.order_total FROM read_csv('customers.csv') c LEFT JOIN read_csv('orders.csv') o USING (customer_id)) TO 'merged.csv' (HEADER)"

On a phone

The realistic phone scenario is a handful of files sitting in Mail or Files, three regional exports or a few months of statements, and no laptop until Monday.

Two things help here. Appending files with identical headers is a copy and paste, provided the app can paste a block of rows rather than one cell at a time. And the check that matters, comparing headers, is faster on a phone than anywhere else: open each file, read the top row.

CSV Editor - Smart CSViPhone & iPad

Opens each export in seconds, pastes a range of rows into another sheet, and writes back every row on save, the free version included. Column and row insertion means reconciling a file that gained a field is a real edit rather than a workaround.

What it does

For more than a handful of files, wait for a desktop. Merging thirty exports by hand is not a thing to do on a touchscreen, and the error rate is the reason rather than the tedium.

After the merge, four checks

Run these every time. They take a minute and they catch essentially every merge failure that matters.

1. Row count. The merged total should be the sum of every input's rows, minus one header per file. wc -l *.csv before, wc -l merged.csv after.
2. Header count. grep -c "^name," merged.csv, or whatever the first column is. More than one means a header ended up as a data row.
3. Column width. Every row should have the same number of fields. Uneven widths mean an unquoted comma somewhere, not a merge bug.
4. Spot-check the boundary. Look at the last row of file one and the first row of file two in the merged output. Misalignment shows up there first.

The second check has a subtlety: if a header line ends up in the data, sorting will scatter it into the middle of the file and it becomes very hard to find later. Catch it now.

CSV File Viewer - Smart CSViPhone & iPad · Android

Runs SQL against the merged file directly, so a GROUP BY on the source column tells you each file contributed the number of rows it should have, which is the whole verification in one query.

What it does

Encoding, the failure that survives every check

Files from different systems are often in different encodings, and concatenation does not convert. A UTF-8 file appended to a Windows-1252 one produces a merged file that is neither: names render correctly in the first half and as é in the second.

Convert before merging, not after:

iconv -f WINDOWS-1252 -t UTF-8 old.csv > old-utf8.csv

file -I *.csv will guess at what you have, though it guesses badly on short files. The reliable tell is opening each file and looking at a row containing a non-ASCII character.