How do I merge CSV files that are too big for Excel?

Concatenate them on the command line if they share columns, or use DuckDB or pandas, which handle files larger than Excel's 1,048,576-row limit.

Can I open a merged CSV with more than a million rows in Excel?

Not on one sheet. Load it with Power Query into the Data Model and analyse it with a PivotTable.

What's the fastest way to merge large CSV files?

Command-line concatenation for identical columns; DuckDB when columns differ or you need to filter.

Should I merge large CSVs into CSV or Parquet?

Use CSV when someone needs to open the file in another tool that only reads CSV. Use Parquet for analysis: it is smaller, keeps column types and loads much faster in DuckDB, pandas and BI tools.

How do I check a large merge is complete?

Count rows per source file and compare the sum with the merged file's row count, then compare the total of a numeric column per file with the source system.

How to merge large CSV files

By the Analistable team · Updated 2026-10-07 · 5 min read

Excel sheets stop at 1,048,576 rows, so large CSVs need another route. If the files share columns, command-line concatenation handles any size. To merge files with different columns, or to filter while merging, DuckDB does it in one SQL statement: COPY (SELECT * FROM read_csv('data/*.csv', union_by_name = true, filename = true)) TO 'merged.csv'. Inside Excel, load the files with Power Query into the Data Model instead of a sheet.

Part of our guide: How to merge CSV files into one

Pick a method by size and shape

Methods for large CSV merges
Method	Size it handles	Different columns	Notes
awk / PowerShell concatenation	Any (streams line by line)	No	Same column order required
DuckDB	Larger than memory	Yes (union_by_name)	One SQL statement, very fast
pandas with chunks	Larger than memory	Yes	Needs Python
Power Query → Data Model	Tens of millions of rows	Yes	Analyse with pivot tables, can't show all rows on a sheet
Browser tools	Limited by browser memory	Varies	Fine for hundreds of thousands of rows

DuckDB

DuckDB is a free, single-file database. Install the command-line tool, then run:

COPY (
  SELECT *
  FROM read_csv('exports/*.csv', union_by_name = true, filename = true)
) TO 'merged.csv' (HEADER, DELIMITER ',');

union_by_name = true matches columns by header name and fills missing ones with NULL.
filename = true adds a column with each row's source file.
Add a WHERE clause to keep only the rows you need, or write TO 'merged.parquet' for a much smaller output file.

pandas in chunks

import glob, pandas as pd

first = True
for path in sorted(glob.glob("exports/*.csv")):
    for chunk in pd.read_csv(path, chunksize=200_000, dtype=str):
        chunk["source"] = path
        chunk.to_csv("merged.csv", mode="w" if first else "a", header=first, index=False)
        first = False

Reading in chunks keeps memory use flat. dtype=str avoids type guesses that differ between chunks (and keeps leading zeros). This version assumes every file has the same columns; align them with chunk.reindex(columns=cols) if they don't.

Excel: Power Query into the Data Model

Use Data → Get Data → From File → From Folder → Combine & Transform, then Close & Load To… → Only Create Connection and tick Add this data to the Data Model. Build a PivotTable from the Data Model to analyse all rows. Windows only; see combining CSV files in Excel for the folder steps.

Check the merged file before using it: row count (wc -l on Mac/Linux, (Get-Content merged.csv).Count in PowerShell for smaller files) should equal the sum of the inputs' data rows plus one header.

Command-line concatenation with one header

On macOS or Linux (or WSL on Windows), awk keeps the first file's header and skips the rest:

awk 'FNR == 1 && NR != 1 { next } { print }' exports/*.csv > merged.csv

FNR is the line number within the current file and NR the line number overall, so only the very first header survives. It streams one line at a time, so memory use stays tiny however big the files are. It assumes every file has the same columns in the same order and ends with a line break; check the headers first with head -n 1 exports/*.csv.

Worked example: sizes and checks

Twelve monthly exports of about 400,000 rows each add up to roughly 4.8 million rows, more than four times what one Excel sheet holds. A safe workflow:

Check the headers match: for f in exports/*.csv; do head -n 1 "$f"; done | sort | uniq -c on Mac/Linux shows how many files share each distinct header line.
Merge with awk (same columns) or DuckDB (union_by_name = true) when headers differ.
Count rows: in DuckDB, SELECT filename, COUNT(*) FROM read_csv('exports/*.csv', filename = true) GROUP BY filename; gives one count per file to compare with the merged total.
Spot-check the totals of a numeric column per file against the source system.

If the result is for analysis rather than handing on as CSV, write Parquet instead: it is compressed, keeps column types and is read by DuckDB, pandas, Power BI and most BI tools.

Troubleshooting large merges

Symptom, cause and fix
Symptom	Cause	Fix
pandas runs out of memory	Reading whole files at once	Use chunksize, select only needed columns with usecols, or switch to DuckDB
DuckDB errors on a type mid-file	Type guessed from the first rows, later rows differ	Pass all_varchar = true or set the column types explicitly
Row count lower than expected	Quoted fields with line breaks, or a file without a trailing newline	Use a CSV-aware tool (DuckDB, pandas) rather than line tools
Duplicated rows	Overlapping export date ranges	De-duplicate on the record ID after merging
Excel shows only 1,048,576 rows	The sheet limit	Load to the Data Model, or analyse outside Excel

Which method when

Identical columns, just need one file: awk or the Windows commands in merging CSV files on Windows.
Different columns, filtering or de-duplication while merging: DuckDB.
Already working in Python, extra cleaning per chunk: pandas with chunks.
The output is a pivot report in Excel: Power Query into the Data Model.
A few hundred thousand rows and no installs: a browser tool such as the CSV merger, which processes the files on your computer.

Frequently asked questions

How do I merge CSV files that are too big for Excel?: Concatenate them on the command line if they share columns, or use DuckDB or pandas, which handle files larger than Excel's 1,048,576-row limit.
Can I open a merged CSV with more than a million rows in Excel?: Not on one sheet. Load it with Power Query into the Data Model and analyse it with a PivotTable.
What's the fastest way to merge large CSV files?: Command-line concatenation for identical columns; DuckDB when columns differ or you need to filter.
Should I merge large CSVs into CSV or Parquet?: Use CSV when someone needs to open the file in another tool that only reads CSV. Use Parquet for analysis: it is smaller, keeps column types and loads much faster in DuckDB, pandas and BI tools.
How do I check a large merge is complete?: Count rows per source file and compare the sum with the merged file's row count, then compare the total of a numeric column per file with the source system.