CSV Merge: Combine Spreadsheets Without Losing Data

Combining CSV files should be simple: copy rows from one file below rows of another. In practice it almost never works that cleanly. Column names differ ("Email" vs "email_address" vs "E-mail"). Some files have extra columns. Date formats vary. Character encodings conflict. Duplicate rows appear when the same data is in multiple sources.

The problems multiply when you merge files from different systems, departments, or time periods. A monthly report split across 12 CSVs, a customer export from two CRMs, or survey results from several tools all create the same headache.

This guide covers practical techniques for merging CSVs reliably, from simple stacking to multi-key joins. Same principles apply whether you use a spreadsheet, command line, Python, or an online tool.

Simple Concatenation: Stacking Rows

The simplest merge is vertical concatenation: stacking rows from multiple files into one. This works when all files have identical columns in the same order.

Command line (fastest for large files): `bash head -1 file1.csv > merged.csv tail -n +2 file1.csv >> merged.csv tail -n +2 file2.csv >> merged.csv tail -n +2 file3.csv >> merged.csv `

head -1 keeps the header from the first file. tail -n +2 skips the header from subsequent files.

Python (handles encoding and edge cases): `python import pandas as pd import glob

files = glob.glob('data/*.csv') df = pd.concat([pd.read_csv(f) for f in files], ignore_index=True) df.to_csv('merged.csv', index=False) `

Pandas automatically aligns columns by name, fills missing columns with NaN, and handles most encoding issues.

Before merging, convert your CSVs to JSON for easier inspection and manipulation with the CSV to JSON converter. JSON format makes it easier to spot structural differences between files.

Common pitfalls in simple concatenation: - Different line endings (Windows \r\n vs Unix \n) - BOM (byte order mark) characters at the start of some files - Quoted fields containing commas or newlines - Inconsistent quoting (some files quote every field, others only quote when necessary)

Column Matching: When Headers Do Not Agree

Real-world CSVs rarely have identical headers. The same data appears under different column names, in different orders, and with different capitalization.

Step 1: Inventory all column names across all files. Create a mapping table:

| File 1 | File 2 | File 3 | Standard Name | |--------|--------|--------|---------------| | First Name | first_name | FirstName | first_name | | Email | email_address | E-mail | email | | Phone | phone_number | - | phone |

Step 2: Rename columns before merging: `python column_map = { 'First Name': 'first_name', 'FirstName': 'first_name', 'Email': 'email', 'email_address': 'email', 'E-mail': 'email', 'Phone': 'phone', 'phone_number': 'phone', }

df = pd.read_csv('file.csv') df = df.rename(columns=column_map) `

Step 3: Handle missing columns. If File 3 has no phone column, Pandas fills it with NaN automatically during concatenation. Decide whether to drop the column (if most files lack it) or keep it (if the data is valuable).

For recurring merges (like monthly reports), save your column mapping as a configuration file. This prevents re-mapping every time and ensures consistency.

Deduplication: Removing Repeated Rows

When merging data from overlapping sources, duplicates are almost guaranteed. The challenge is defining what counts as a duplicate.

Exact duplicates: every field in the row is identical. These are easy to detect and safe to remove: `python df = df.drop_duplicates() `

Key-based duplicates: the same entity appears with slightly different data. An email address appears in both files, but the phone number differs. You need to decide which version to keep: `python # Keep the first occurrence df = df.drop_duplicates(subset=['email'], keep='first')

# Keep the last occurrence (most recent file wins) df = df.drop_duplicates(subset=['email'], keep='last') `

Fuzzy duplicates: "John Smith" and "Jon Smith" might be the same person. "123 Main St" and "123 Main Street" are the same address. Fuzzy deduplication requires string similarity matching: `python from fuzzywuzzy import fuzz

def is_likely_duplicate(row1, row2): name_ratio = fuzz.ratio(row1['name'], row2['name']) email_ratio = fuzz.ratio(row1['email'], row2['email']) return name_ratio > 85 and email_ratio > 90 `

Fuzzy matching is computationally expensive for large datasets. For files with more than 10,000 rows, consider blocking (only comparing rows that share the same first letter of last name, for example) to reduce the number of comparisons.

Format your merged data as JSON with the JSON Formatter for API ingestion or database import. Clean, well-formatted JSON is easier to validate before loading into production systems.

Horizontal Merges: Joining on a Key Column

Horizontal merging (SQL-style joins) combines columns from different files based on a shared key. This is common when different systems export different attributes of the same entities.

Example: file1.csv has customer names and emails. file2.csv has customer emails and purchase history. You want one file with names, emails, and purchases, joined on the email column.

`python df1 = pd.read_csv('customers.csv') df2 = pd.read_csv('purchases.csv')

# Inner join: only rows where email exists in both files merged = pd.merge(df1, df2, on='email', how='inner')

# Left join: all rows from df1, matched data from df2 where available merged = pd.merge(df1, df2, on='email', how='left')

# Outer join: all rows from both files merged = pd.merge(df1, df2, on='email', how='outer') `

Choosing the right join type:

• Inner join: you only want records that exist in both files. Drops unmatched rows from both sides.
• Left join: you want all records from the primary file, enriched with data from the secondary file where available. Most common for enrichment use cases.
• Outer join: you want everything from both files. Produces the largest result with the most missing values.

Before joining, check for duplicate keys. If an email appears twice in the purchase file (multiple purchases), the join will create multiple rows for that customer. Decide whether you want this (one row per purchase) or whether you need to aggregate first (total purchases per customer).

Convert the merged result back to CSV with the JSON to CSV converter if you processed the data in JSON format during the merge.

Handling Data Type Conflicts

Merging files from different sources often reveals data type inconsistencies that cause silent errors.

Dates: "08/25/2026" (US), "25/08/2026" (Europe), "2026-08-25" (ISO). When merging files with different date formats, parse each file's dates explicitly: `python df1['date'] = pd.to_datetime(df1['date'], format='%m/%d/%Y') df2['date'] = pd.to_datetime(df2['date'], format='%d/%m/%Y') `

Numbers: "1,234.56" (US) vs "1.234,56" (Europe). Pandas guesses the format, and sometimes guesses wrong. Specify the decimal separator: `python df = pd.read_csv('european_data.csv', decimal=',') `

ZIP codes and IDs: leading zeros get stripped when CSV tools interpret these as numbers. "07302" becomes "7302". Read these columns explicitly as strings: `python df = pd.read_csv('data.csv', dtype={'zip_code': str, 'product_id': str}) `

Boolean values: "TRUE," "true," "1," "Yes," "Y" all mean the same thing but are represented differently. Normalize before merging: `python bool_map = {'TRUE': True, 'true': True, 'Yes': True, 'Y': True, '1': True} df['active'] = df['active'].map(bool_map).fillna(False) `

Encoding: UTF-8 is the standard, but you will encounter Latin-1 (ISO-8859-1), Windows-1252, and Shift-JIS. If a file has garbled characters, try different encodings: `python df = pd.read_csv('data.csv', encoding='latin-1') `

FAQ

What is the maximum size of CSV files I can merge?

The limit depends on your tool. Excel maxes out at about 1 million rows. Google Sheets at 10 million cells. Pandas in Python can handle files limited only by your RAM (a 16GB machine can merge files totaling 2 to 4 GB). For very large files (10GB+), use command-line tools like csvkit or awk, which process data in streams without loading everything into memory.

How do I merge CSV files in Google Sheets?

Open the first file in Google Sheets. Use File, then Import, then Upload to add the second file. Choose "Append to current sheet" as the import option. This works for simple concatenation but does not handle column mapping or deduplication. For those, use the QUERY function or Apps Script.

Should I use a database instead of merging CSVs?

If you are merging the same sources regularly, yes. Import each source into a database table and use SQL joins. This is more reliable, repeatable, and auditable than manual CSV merging. SQLite is free and requires no setup. Import CSVs with .import command and query with standard SQL.

How do I validate the merged file?

Check the row count (should equal the sum of source rows minus duplicates). Verify column count matches expectations. Spot-check 10 to 20 random rows against the source files. Check for unexpected null values in columns that should be complete. Run a quick summary (min, max, count) on numeric columns to catch obvious data corruption.