How to Merge Multiple CSV Files (And Common Pitfalls)

Merging multiple CSV files is a daily task for data analysts, accountants, and anyone who gets data in pieces. The simple case is "stack them on top of each other" (same columns, append the rows). The complex case is "join them on a common column" (like a SQL JOIN). This guide covers both, the common pitfalls, and the right tools.

Case 1: stacking (same columns, append the rows)

The simplest case. You have 5 CSV files, each with the same columns. You want one CSV file with all the rows in sequence.

The basic approach

1. Take the first file as the base. Keep the header row.
2. For each subsequent file, drop the header row and append the rest.
3. Result: one file with one header and all the rows.

This is the right approach for: monthly reports with the same schema, exports from the same tool over time, data dumps from multiple regions of the same product.

How to do it in command line

The Unix cat command does this in one line:

cat file1.csv file2.csv file3.csv > merged.csv

But this duplicates the header (each file has one). To skip headers from the second file onward:

cat file1.csv <(tail -n +2 file2.csv) <(tail -n +2 file3.csv) > merged.csv

Or in Python with pandas:

import pandas as pd
files = ['file1.csv', 'file2.csv', 'file3.csv']
df = pd.concat([pd.read_csv(f) for f in files], ignore_index=True)
df.to_csv('merged.csv', index=False)

How to do it in a spreadsheet

Excel and Google Sheets do not have a native "stack CSVs" command, but the workaround works:

1. Open the first CSV. Note the columns.
2. Open the second CSV in a new sheet. Delete the header row.
3. Copy the data rows and paste at the bottom of the first sheet.
4. Repeat for the remaining files.

This is fine for a one-off. It is not a sustainable workflow for many files.

Case 2: joining (different schemas, join on a key)

The more complex case. You have two CSVs with different columns but a common key. You want to combine them, matching rows on the key.

Example: one CSV has customer_id and customer_name. Another has customer_id and order_total. You want one CSV with customer_id, customer_name, and order_total, joined on customer_id.

How to do it in pandas

import pandas as pd
customers = pd.read_csv('customers.csv')  # customer_id, customer_name
orders = pd.read_csv('orders.csv')       # customer_id, order_total

# Inner join (only customers with orders)
merged = pd.merge(customers, orders, on='customer_id', how='inner')

# Left join (all customers, with or without orders)
merged = pd.merge(customers, orders, on='customer_id', how='left')

merged.to_csv('merged.csv', index=False)

The how parameter controls the join type:

• inner — only rows with matching keys in both files.
• left — all rows from the first file, with matches from the second (or NaN if no match).
• right — all rows from the second file.
• outer — all rows from both, with NaN where there is no match.

How to do it in a spreadsheet

Excel has VLOOKUP and XLOOKUP. Google Sheets has VLOOKUP. Both can join data on a key column:

1. Open both CSVs in the same workbook (two sheets).
2. In the destination sheet, add a new column for the joined value.
3. Use VLOOKUP to pull the value from the second sheet based on the key column.

This works for small datasets and one-off merges. It is fragile for many keys or repeated merges.

Common pitfalls

Mismatched column order

The most common stacking pitfall. The CSVs have the same columns in a different order. Stacking them naively produces a CSV where the same data is in different columns. The fix: sort the columns to a canonical order before stacking.

Mismatched encodings

One file is UTF-8, another is Latin-1, a third is Windows-1252. The non-ASCII characters get garbled in some files. The fix: detect the encoding of each file (with file on Unix or Notepad++ on Windows), convert to UTF-8, then merge.

Inconsistent date formats

One file has dates as "2026-08-15" (ISO), another as "08/15/2026" (US), another as "15/08/2026" (EU). Stacking them produces a CSV with mixed date formats. The fix: parse and reformat all dates to a canonical format (ISO 8601: "2026-08-15") before stacking.

Excel corrupting CSVs (BOM, sep=, etc.)

Excel-saved CSVs have a UTF-8 BOM (byte order mark) at the start, which the parser may treat as part of the first column name. The result: the first column is "id" instead of "id". The fix: strip the BOM. The Uttir CSV/JSON converter handles this correctly.

Quoting and embedded commas

CSV is text with a simple rule: fields with commas, quotes, or newlines are wrapped in double quotes; quotes inside fields are escaped by doubling them. Most CSVs follow this rule. The exception: a CSV from a system that uses different quoting (single quotes, no quotes, semicolon as separator) will not merge correctly with a standard CSV. The fix: detect the delimiter (comma, semicolon, tab) and the quoting style before merging.

Mismatched line endings

Unix files end lines with LF. Windows files end with CRLF. Most modern tools handle both. Some older tools do not. The fix: normalize to LF before merging.

Empty cells and NaN

Some systems write empty cells as nothing, others as "NA", others as "null", others as "NaN". When stacked, the result has mixed representations of the same concept. The fix: normalize empty cells to a single representation (usually nothing, or "NULL") before stacking.

Numeric formatting

Some CSVs use "1,234.56" (US format with comma thousands separator), others use "1.234,56" (European format with period thousands separator), others use "1234.56" (no thousands separator). The fix: parse with locale awareness and output in a canonical format.

The right tool for the job

For ad-hoc merges (one time, small files)

A spreadsheet works. The cost is tedious, but the result is correct.

For repeated merges (same files, every month)

A script. Python with pandas, R with dplyr, or even a bash script. The script can be re-run and handles edge cases consistently.

For large files (over 100 MB)

Spreadsheets will not open them. Pandas will load them but will use a lot of memory. The right tool: a streaming tool like xsv (Rust-based, fast), or a database (SQLite, DuckDB). DuckDB is the modern choice: it reads CSVs directly with SQL syntax.

For the "inspect and reshape" step

Before merging, you usually want to look at the data, check the columns, and make sure the schemas match. The Uttir CSV/JSON converter does this in your browser. Drop in the CSV, get a JSON view, inspect the columns, then download back as CSV (or as JSON for piping into a tool).

What to do after merging

The merged CSV is rarely the final form. Common follow-ups:

• Validate the data — count rows, check for duplicates, verify column types. The Uttir JSON Validator can be used after converting the merged CSV to JSON.
• Compute statistics — sum, mean, median, standard deviation. The Uttir Statistics Calculator handles this in your browser.
• Convert to a database — for repeated queries, load into SQLite or DuckDB. The CSV becomes a table.
• Convert to JSON for an API — the Uttir CSV/JSON converter does this directly.

Privacy: why "no upload" matters for CSVs

The data in CSVs is often sensitive: customer lists, financial records, employee data, sales figures. The privacy model of "everything runs in your browser" is the right model for these. The Uttir CSV/JSON converter reads the CSV, processes it, and outputs the result — all locally. Your data never leaves your device.

When to use a different format

CSV is great for tabular data. It is the wrong format for:

• Hierarchical data — JSON or XML. CSVs flatten hierarchies by repeating parent data in each row.
• Data with rich types — JSON, Parquet, or a database. CSVs are text; everything is a string.
• Large datasets — Parquet or a database. CSVs are uncompressed; a 1 GB CSV is 1 GB on disk.
• Data with formulas or formatting — Excel (XLSX). CSVs cannot store formulas or cell formatting.

For pure tabular data with the same schema across all sources, CSV is the right answer. For anything more complex, the format should change.

Bottom line

Merging CSVs is either a one-liner (for simple stacking) or a real script (for joins). Watch out for the pitfalls: column order, encoding, date format, BOM, quoting. The Uttir CSV/JSON converter helps with the inspect-and-reshape step in your browser. For repeated merges, write a script. For one-off, a spreadsheet is fine. For data you do not want to upload, stay client-side.