How to Merge Two CSV Files Using Excel and Python

Still copy-pasting CSV files like it’s 2010?
If you only have a couple hundred rows, Excel will get you there fast.
But once files grow, columns don’t match, or you repeat the task, Power Query or Python pandas will save hours and headaches.
This guide shows quick Excel copy/paste, Power Query for repeatable merges, a concise pandas workflow for larger or tricky joins, and fast command-line tricks.
You’ll also get the validation steps and common gotchas to avoid corrupting data.

Immediate Steps to Merge Two CSV Files Effectively

Merging two CSV files means you’re either stacking rows from both into one, or joining them side by side based on a shared column. Which route you take depends on file size, how the columns line up, and whether you’ll be doing this once or a hundred times.

Got small files? A few hundred rows? Excel copy/paste or a quick command-line trick will get you there in under a minute. Bigger datasets, recurring merges, or columns that don’t quite match? You’ll want Excel Power Query or Python pandas. Command-line tools like cat (macOS/Linux) or copy (Windows) are instant but they paste everything exactly as-is. That means duplicate headers and misaligned columns if your files aren’t identical.

Before you start, crack open each CSV in a text editor or spreadsheet. Check that column names match and delimiters are the same. Then:

1. Preview both files to confirm delimiter (comma, semicolon, tab), encoding (UTF-8 is safest), and whether you’ve got a header row.
2. Check column names for exact matches. Case and spacing matter if you’re planning to align data.
3. Pick a method based on file size and whether you need to clean things up or join by a key.
4. Run the merge using Excel, Python, or command line.
5. Validate output by checking row count, eyeballing the first and last rows, and making sure no duplicate headers snuck into the middle.
6. Save the merged file with a clear name (merged.csv or combined-output.csv) and keep your source files as backups.

Understanding CSV Structure to Ensure a Correct Merge

CSV stands for Comma Separated Values. Each file is just plain text where delimiters separate columns. That delimiter can be a comma, semicolon, tab, space, or pipe. Mix them across files and your columns turn into a scrambled mess.

Always open both CSVs in a text editor to confirm the same separator shows up in every row.

Encoding mismatches also break merges. One file in UTF-8 and the other in Windows-1252? Special characters turn into garbage after you combine them. A byte-order mark (BOM) at the start of a UTF-8 file can make the first column name look normal but hide invisible characters that block column alignment in pandas or Power Query.

• Delimiters to verify: comma (,), semicolon (;), tab (\t), pipe (|), space ().
• Where to preview columns: Notepad, VS Code, or run head file.csv in terminal to see the raw structure.
• Encoding signs: broken accents, question marks, or missing characters mean encoding mismatch. Re-save as UTF-8 without BOM.
• Header variations: one file might have no header row, or extra blank rows above it. Skip or remove them before merging.
• Whitespace to trim: leading or trailing spaces around column names or in cells can prevent matching and should be stripped.

Using Excel to Combine Two CSV Files Smoothly

Two small CSV files? Under a few thousand rows? Open the first file in Excel, open the second in a separate window, select all rows below the header in the second file, copy them, switch to the first workbook, click the row below the last data row, and paste. Save as CSV. Takes about 30 seconds but doesn’t scale if you’ve got many files or need to repeat the merge.

Excel’s row limit is 1,048,576 rows per worksheet. If your combined dataset goes past that, use Power Query or pandas instead. Power Query also handles repeatable merges from a folder, column alignment, duplicate removal, and type conversions without manual copy/paste.

Power Query is built into Excel 2016 and Excel 365 (desktop). It reads all CSV files in a folder, aligns columns by identical names, and loads the merged table into a worksheet or keeps a live connection you can refresh when new files show up.

Power Query Workflow

Go to the Data tab, click Get Data > From File > From Folder. Browse to the folder with both CSV files and click Open. Excel shows a list of files in that folder with name, extension, date modified, and size. Click Combine > Combine & Transform Data. Power Query opens the Combine Files dialog and picks one file as a sample to read column names and data types from the first 200 rows. Click OK to load all files into the Power Query Editor.

In the editor, you can filter the Source.Name column to exclude specific files, change column data types (set a date column to Date/Time or a number column to Text to preserve leading zeros), remove duplicate rows using Home > Remove Rows > Remove Duplicates, or reorder columns. When you’re done, click Close & Load to send the merged table to a new worksheet, or Close & Load To to pick the destination and format.

After loading, the table stays linked to the source folder. Drop a new CSV into that folder and click Refresh on the Data tab to pull it in. To break the connection and keep a static snapshot, right-click the query in the Queries & Connections pane and choose Unlink.

Merging Two CSV Files with Python (pandas)

Python’s pandas library is the go-to for merging two or more CSV files when you need column alignment, type parsing, duplicate removal, or transformations before saving. Install pandas with pip install pandas if you haven’t already. The core workflow is read both files, concatenate them into a single DataFrame, optionally clean or deduplicate, then save to CSV.

Start by importing pandas and reading each file with pd.read_csv('file1.csv') and pd.read_csv('file2.csv'). Use the parse_dates parameter to automatically convert date columns and dtype to enforce text or numeric types. Concatenate the two DataFrames with pd.concat([df1, df2], ignore_index=True), which stacks rows and resets the index from zero. If both files have the same header row, pandas aligns columns by name and fills missing columns with NaN. Save the combined DataFrame with df.to_csv('merged.csv', index=False) to exclude the row-number index from the output.

Example snippet:

import pandas as pd

df1 = pd.read_csv('file1.csv')
df2 = pd.read_csv('file2.csv')

merged = pd.concat([df1, df2], ignore_index=True)
merged.to_csv('merged.csv', index=False)

This keeps all columns from both files. If column names differ, pandas creates separate columns and fills missing cells with NaN.

Joining Two CSVs by a Key Column

If you need to align rows based on a shared column (a customer ID or transaction date, for instance), use pd.merge() instead of concat. Merge performs SQL-style joins and lets you pick which rows to keep when the key column values don’t match across files.

• Inner join (how='inner') keeps only rows where the key exists in both files. Think “a customer who placed an order and has a billing address.”
• Left join (how='left') keeps all rows from the left file and matches from the right file. Missing matches fill with NaN. Think “every order, with billing details if available.”
• Right join (how='right') keeps all rows from the right file and matches from the left. Think “every address, with order details if available.”
• Full outer join (how='outer') keeps all rows from both files, filling NaN where a match is missing. Think “every customer and every order, whether they match or not.”

Call pd.merge(df1, df2, on='CustomerID', how='inner') to join by a single column, or pass a list of column names to join on multiple: on=['CustomerID', 'OrderDate'].

Command-Line Methods to Merge Two CSV Files Quickly

Command-line concatenation is the fastest way to merge two CSV files with identical columns when you don’t need transformations or column alignment. On macOS and Linux, use the cat command. On Windows, use copy or PowerShell. Both methods paste rows from the second file directly below the first file’s rows, so you must manually remove the duplicate header from the second file before merging or strip it afterward.

Place both CSV files in the same folder and open a terminal (or Command Prompt on Windows). Navigate to the folder with cd /path/to/folder. On macOS or Linux, run cat file1.csv file2.csv > merged.csv to create a new file named merged.csv containing all rows from both files. On Windows, run copy file1.csv+file2.csv merged.csv or copy /b file1.csv+file2.csv merged.csv for binary concatenation that preserves exact bytes.

To merge files and remove the second file’s header in one step, use tail to skip the first row:

1. cat file1.csv > merged.csv to start the output with the first file (header included).
2. tail -n +2 file2.csv >> merged.csv to append all rows except the header from the second file.
3. Verify the result with head merged.csv to check that only one header row appears at the top.
4. Open merged.csv in a text editor or Excel to confirm row count and column alignment.

Caution: Command-line tools concatenate text as-is and don’t check column names, delimiters, or data types. If one file uses commas and the other uses semicolons, the merged file will have misaligned rows. If both files have a header row and you don’t strip the second header, you’ll see column names inserted mid-file as a data row, which breaks imports into Excel or pandas.

Handling Header Mismatches, Different Columns, and Duplicate Rows When Merging CSVs

Header mismatches happen when column names differ slightly. “CustomerID” vs. “Customer_ID” or “Date” vs. “OrderDate.” Tools like pandas and Power Query treat these as separate columns, giving you twice as many columns as expected with half the cells filled. Normalize column names before merging by renaming in Excel, using df.rename(columns={'old': 'new'}) in pandas, or editing the header row in a text editor.

If the two files have different numbers of columns or different column orders, pandas and Power Query align by column name and fill missing values with NaN or blank cells. Command-line concatenation doesn’t align columns at all. It just pastes rows, so differing column counts produce ragged, unparsable output. Always use pandas or Power Query when column structures differ.

Duplicate rows occur when the same record appears in both files. Remove duplicates in Power Query with Home > Remove Rows > Remove Duplicates (which keeps the first occurrence) or in pandas with df.drop_duplicates(subset=['KeyColumn'], keep='first').

Working with Large CSV Files and Avoiding Performance Issues

Excel’s row limit is 1,048,576 rows per worksheet. If your two CSV files combine to exceed that, Excel will truncate the data and you’ll lose rows. Use pandas or command-line tools for files larger than Excel’s capacity.

Pandas can handle millions of rows, but reading very large files into memory at once may cause out-of-memory errors. Use the chunksize parameter in pd.read_csv('file.csv', chunksize=100000) to read the file in batches of 100,000 rows at a time, process or filter each chunk, then concatenate the results. This keeps memory usage low and lets you merge files that are several gigabytes in size.

• Chunk large files: Read in 100,000 row chunks, filter or aggregate each chunk, then concatenate. Example: chunks = pd.read_csv('large.csv', chunksize=100000); df = pd.concat(chunks, ignore_index=True).
• Stream merges: For append-only merges with identical columns, write each chunk to the output file immediately instead of holding all data in memory. Open the output file in append mode and call chunk.to_csv('merged.csv', mode='a', header=False, index=False) after the first chunk.
• Avoid in-memory duplicates: If both files are large and you need to deduplicate, process each file separately to remove duplicates before merging, or use pandas drop_duplicates() only on the key columns instead of the entire DataFrame.
• Command-line tools for raw speed: cat and copy handle multi-gigabyte files instantly because they stream bytes without parsing rows or columns. Use this for quick concatenation when you know column structures match and you don’t need transformations.

Comparing Tools and Choosing the Best Method to Merge Two CSV Files

The best method depends on file size, how often you merge, and whether you need column alignment or transformations. Excel copy/paste is fastest for one-time small merges but doesn’t scale. Power Query is perfect for repeatable merges from a folder, especially when new files arrive regularly. Pandas offers the most control for large datasets, deduplication, type casting, and complex joins. Command-line tools are unbeatable for raw speed with identical-structure files but require manual header cleanup.

Online CSV merge tools (some free, some with paid tiers) offer a quick drag-and-drop interface for small files but typically cap free uploads at 1 MB total and may not handle encoding issues or large column counts reliably. A $4 per month pro plan on some services removes the size limit and adds duplicate removal and source-file tagging.

For production pipelines or automated workflows, prefer pandas scripts or Power Query refresh connections over manual processes. For ad-hoc tasks where you just need two files combined once, command-line or Excel copy/paste takes under a minute.

Final Words

Open the files, check headers, and pick the right tool: Excel (manual or Power Query), command line (copy/cat), or pandas, then merge.

We covered CSV structure, delimiters and encoding gotchas, header handling, duplicate rows, and scaling for big files. You also got quick command-line tricks and repeatable Power Query and pandas workflows.

When it’s time to ship, this guide shows how to merge two csv files reliably: pick the method for your data size, validate the output, and you’ll avoid late-night surprises.

FAQ

Q: How do I combine two CSV files into one CSV file?

A: The way to combine two CSV files into one CSV file is to append them, keeping only the first file’s header. Use command-line concat, pandas.concat, or Excel copy/paste for small files, and check encoding before saving.

Q: How do I import multiple CSV files into one Excel file? / How do you combine data from multiple files into one in Excel?

A: To import multiple CSV files into one Excel file, use Data > Get Data > From Folder then Combine & Load in Power Query for repeatable merges; for quick jobs open each CSV and copy/paste, watching headers and Excel’s row limit.

Q: How to merge multiple CSV files into one using CMD?

A: The way to merge multiple CSV files into one using CMD is to concatenate them: on Windows use copy /b file1.csv+file2.csv merged.csv; on macOS/Linux use cat file1.csv file2.csv > merged.csv, then remove duplicate headers and verify encoding.