How to Merge CSV Files That Have Different Columns

You have twelve monthly exports to turn into one file. Eleven of them look the same, but somebody added a discount_code column in March, and in July the reporting tool swapped city and country around. Stack the files on top of each other and nothing errors — you just end up with cities filed as countries for half the year.

Why Stacking the Files Silently Corrupts Them

Every quick method for gluing CSVs together works on position: the third value in a line is the third column, whatever the header said. That assumption is what breaks.

The gluing problem is worth seeing once. A file ending 2,Bob with no final newline, followed by a file starting id,name, concatenates to the line 2,Bobid,name. One row destroyed, one header buried in the data, and the file still opens fine.

Matching by Column Name Instead

The fix is to read each file’s header and line the columns up by name. The CSV merge tool does this in your browser, and it helps to know exactly how:

• The output header is every column from every file, in order of first appearance, so a column that first turns up in the March file is appended after the first file’s columns.
• Each row is rebuilt into that order. Column names are compared after trimming spaces from the ends, so country and country are treated as the same column.
• A column a file does not have is left empty for that file’s rows. Nothing is invented and nothing is dropped.
• Each file’s own header line is read, then discarded, so it never lands in the data.
• When the files disagree you are told before you run: the setup screen counts how many of the selected files have different columns from the first.

Name matching is literal apart from that trimming. Revenue and revenue are two different columns, and so are Order ID and order_id. If a system renamed a column rather than moving it, fix that header in the odd file first; it is only the first line of a text file.

Name or Position: How to Choose

Position mode keeps the first file’s header and passes each row through as its original text, which is fast and lossless — but it trusts you. If a later file has an extra column, its rows simply have more values than the header has names, and nothing will complain.

Recording Which File Each Row Came From

Once twelve files are one file, “which export did this row come from?” becomes unanswerable — unless you wrote it down during the merge. Ticking Add a source_file column appends one extra column at the end holding each row’s original file name.

That column earns its place quickly:

• Monthly exports. If your files are named sales-2026-03.csv, the source column is also a month column. You can group or filter by it without adding a date field to the export.
• Per-store or per-region exports. Same idea: the file name is the dimension, and it survives the merge.
• Finding the file to go back to. When one row looks wrong, you know which export to re-pull.
• Catching a double upload. If one month contributed twice as many rows as it should, the source column makes that visible, and the extra rows can then be cleaned with the duplicate remover — removing duplicate emails from a CSV list works through the options on a contact file.

Since the value is the file name, rename the files to something meaningful before you merge. export (3).csv tells you nothing later.

Files with Different Delimiters or Encodings

Exports collected over a year often are not even the same kind of text file. The merge tool detects each file’s encoding and delimiter separately, then writes one consistent result: UTF-8, using the first file’s delimiter. A byte-order mark is added when any input had one or was not UTF-8, which keeps accented characters readable if the merged file is later opened in Excel.

If one file is detected wrongly — usually visible as é where é should be — convert that file on its own with the encoding fixer first, then merge, because a merged file with two encodings inside it cannot be repaired in one pass afterwards. UTF-8, UTF-16 and Windows-1252 lists what the detection covers and how to recognise each mistake from its symptom.

Check the Merge Before You Rely on It

1. Check the row total. A correct merge has as many data rows as the sum of the parts, minus one header per file. The summary reports it in the form “Merged 12 files into one file with 4,318,902 rows and 19 columns”, and that count excludes headers. To get the per-file numbers, count the rows in each file first.
2. Check the column count. It should equal the number of distinct column names across all files, plus one if you added source_file. A number you cannot explain usually means a typo in one file’s header.
3. Spot-check one row per file. Filter on source_file, take a row, and compare it field by field with the same row in the original export.
4. Check the late column. discount_code should be empty for January and February rows and filled from March onwards. If it is empty everywhere, the names did not match.

Two Alternatives Worth Knowing

Power Query, If You Want the Result in Excel

Our tools write CSV text only — they do not produce an .xlsx file and they cannot pivot, chart or sort. If the merged data is going to live in a workbook and be refreshed every month, Power Query is the better home for it. In Excel for Windows, go to Data › Get Data › From File › From Folder, point it at the folder, and choose Combine › Combine & Load. The Combine Files dialog lets you set the File Origin, Delimiter and Data Type Detection for the sample file. The result keeps the file name in a Source.Name column — Microsoft’s own walkthrough tells you to filter that column to check every file was included — which is the same idea as the source_file option here.

Two cautions. Microsoft’s guidance is that you can combine files “as long as they have the same file type and structure (including the same columns)”, because the generated query “expands the resulting data extraction as top-level columns” based on one example file. That is a documented description of the mechanism, not a documented warning, but it is the reason to check the final expand step when a column first appears partway through the year. And Excel for Mac is the weaker option: the data sources Microsoft lists for Power Query there do not include From Folder.

DuckDB, If You Are Happy with One Line of SQL

DuckDB is free, installs as a single executable, and reads CSVs straight off disk. Its union_by_name option does exactly what this guide is about:

SELECT * FROM read_csv('exports/*.csv', union_by_name = true, filename = true);

Missing values come through as NULL, and filename = true adds a column with the source file — the equivalent of source_file. Write the result out with COPY (SELECT …) TO 'all.csv' (HEADER);. The documentation warns that union_by_name increases memory use, so watch it on very large sets. For the wider comparison, see opening a CSV that is too large for Excel.

Two related jobs have their own guides: merging CSV files from the command line tests what copy, PowerShell and awk really do, and combining CSV files into one workbook covers keeping each file on its own sheet instead.