How to Merge CSV Files

Append vs join: pick the right merge type

Getting this wrong is the most common merge mistake. Appending files with different columns creates blank cells and misaligned data. Joining without a reliable key duplicates or drops rows.

Appending in Excel: the manual way

1. Open the first file in Excel.
2. Open each additional file, select all data rows (not the header), copy and paste them below the last row of the first file.
3. Check that columns align — the same field must be in the same column across all files.
4. Remove any duplicate header rows that were accidentally included.

This works for two or three small files. For dozens of monthly files or files with mismatched columns, it becomes impractical.

Joining in Excel with VLOOKUP

1. Put the main file in Sheet1 and the file with additional columns in Sheet2.
2. In Sheet1, add a new column and enter =VLOOKUP(A2,Sheet2!A:D,2,FALSE) where A is the key column and 2 is the column number you want to pull from Sheet2.
3. Repeat for each column you want to bring in, changing the column number.
4. Fill the formulas down and paste as values to remove the formula dependency.

VLOOKUP returns the first match only, so duplicate keys in Sheet2 will silently use the wrong row. INDEX/MATCH is more flexible but equally prone to this problem.

Common merge pitfalls

• Column mismatch — files from different months may have added, removed or renamed columns. Always check headers before appending.
• Duplicate keys — if the key column has duplicates in either file, a join will silently match the wrong rows. Deduplicate first.
• Encoding differences — one file in UTF-8 and another in Windows-1252 will produce garbled characters when combined. Convert both to the same encoding first.
• Data type inconsistency — one file stores phone numbers as text with leading zeros, another as numbers without them. Standardise before merging.
• Missing key values — rows with a blank key column won't match anything and will be silently dropped in a join, or duplicated in an outer join.

When you have many files

If you regularly merge more than a handful of files — weekly exports, regional sales data, multi-team reports — Excel's manual approach doesn't scale. Power Query (Data → Get Data → Combine Queries) can automate multi-file appends, but setting it up requires understanding the M query language and handling schema differences between files.

Merge with TheSpreadFix

TheSpreadFix lets you upload multiple CSV files and choose between append and join. For appends, it aligns columns by header name even when the order differs, and flags columns that exist in some files but not others. For joins, it matches rows on a key column you select and lets you choose which columns to bring in. The merged file is processed in your browser and ready to download.