Analistable

How to merge CSV files into one

By the Analistable team · Updated · 12 min read

To merge CSV files with the same columns, keep the header row from the first file and append only the data rows from the rest. In Excel, use Data → Get Data → From File → From Folder → Combine. On Mac or Linux, awk 'FNR==1 && NR!=1 {next} {print}' *.csv > ../merged.csv does it in one line. To skip both, drop the files into our free CSV combiner.

Methods compared

Ways to merge CSV files
MethodHandles headersDifferent columnsSkill
Excel Power Query (From Folder)YesYes (by name)Low
Windows `copy *.csv`No — repeats headersNoLow
Mac/Linux `awk`YesNoMedium
Python pandasYesYes (by name)Medium
Browser combinerYesYes (by name)None
Windows PowerShell `Import-Csv`YesPartly (columns from first file)Medium
DuckDB `read_csv`YesYes (union_by_name)Medium

In Excel with Power Query

Power Query is built into Excel 2016 and later on Windows, including Microsoft 365. It is the best choice when the files come from a system you export from every week, because the merge becomes a refresh:

  1. Put every CSV to be merged in one folder, with nothing else in it. Move the merged output somewhere else, or it will be read back in on the next refresh.
  2. Choose Data → Get Data → From File → From Folder, browse to the folder and click Open.
  3. In the preview of files, choose Combine → Combine & Transform Data.
  4. In the Combine Files dialog, check File Origin (choose 65001: Unicode (UTF-8) if accented characters look wrong) and Delimiter (comma, semicolon or tab), using the first file as the sample.
  5. In the Power Query Editor, set data types: make ID and postcode columns Text so leading zeros survive, and dates Date. Optionally rename Source.Name to source.
  6. Click Home → Close & Load to put the merged table on a new sheet. Next time, drop new files in the folder and click Data → Refresh All.

Power Query matches columns by header name, so files whose columns are in a different order line up correctly. One trap: the expand step it generates lists the columns of the sample file only. If a later file has an extra column, such as a channel field added in August, it is silently left out. Check the column list after combining; to include every column, edit the expand step in the formula bar or rebuild the combine with a newer file as the sample. Details and fixes for delimiter and encoding problems: combining CSV files in Excel.

Excel for Mac has gained Power Query features more slowly than Windows, and the options available depend on your version. If From Folder isn't offered, combining CSV files on a Mac covers Terminal and Excel routes that work on any recent macOS.

Windows: Command Prompt and PowerShell

copy *.csv merged.csv in Command Prompt concatenates the files byte for byte, so every file's header row ends up in the middle of the data, and if a file doesn't end with a line break its last row is glued to the next file's header. Delete those rows afterwards, or use PowerShell, which reads each file as CSV and writes one header:

Get-ChildItem .\data\*.csv | ForEach-Object {
  $name = $_.Name
  Import-Csv $_.FullName | Select-Object @{Name='source'; Expression={$name}}, *
} | Export-Csv .\merged.csv -NoTypeInformation -Encoding UTF8

This adds a source column with each file's name. Two points to know. Export-Csv takes its column list from the first row it receives, so a column that appears only in later files is dropped; put a file that has every column first, or use pandas or Power Query when the files differ. And Windows PowerShell 5.1 does not write UTF-8 by default, hence -Encoding UTF8; PowerShell 7 writes UTF-8 without being asked and no longer needs -NoTypeInformation. Import-Csv expects commas; add -Delimiter ';' for semicolon files. More options for Windows users are in merging CSV files on Windows.

Mac and Linux: awk

On Mac or Linux, awk skips the first line of every file except the first. Write the output to another folder so it doesn't include itself. Because awk prints each record with its own line ending, a file without a final line break doesn't glue rows together the way cat can. To add the source file name as a first column:

awk 'FNR==1 && NR!=1 {next} FNR==1 {print "source," $0; next} {print FILENAME "," $0}' data/*.csv > merged.csv

NR counts lines across all files and FNR restarts at 1 in each file, so the first rule drops every header except the first. Files are read in the order the shell expands data/*.csv, which is alphabetical, so date-stamped names such as orders_2026-07.csv come out in date order. Mac steps: combining CSV files on a Mac.

Why line-based tools fail when columns differ

awk, cat, copy and most quick scripts treat a CSV as lines of text, so they only work when every file has exactly the same columns in exactly the same order. Here are three monthly exports that look similar but aren't: July has order_id, date, amount; August adds a channel column; September's export puts date before order_id. The awk command above produces this:

source,order_id,date,amount
data/orders_2026-07.csv,A-2001,2026-07-03,42.50
data/orders_2026-07.csv,A-2002,2026-07-19,18.00
data/orders_2026-08.csv,A-2003,2026-08-02,64.00,web
data/orders_2026-08.csv,A-2004,2026-08-27,27.90,phone
data/orders_2026-09.csv,2026-09-05,A-2005,35.00

The August rows have five fields under a four-column header, and the September row has its date in the order_id column. No error is raised. A method that matches columns by header name avoids both problems, which is why the methods table above separates the two groups.

In Python

pandas reads each file as a table and aligns columns by name when it concatenates them. This version also records which file each row came from and keeps order IDs as text:

from pathlib import Path
import pandas as pd

frames = []
for f in sorted(Path("data").glob("*.csv")):
    df = pd.read_csv(f, dtype={"order_id": str})
    df.insert(0, "source", f.name)
    frames.append(df)

merged = pd.concat(frames, ignore_index=True)
merged.to_csv("merged.csv", index=False)
merged.csv from the same three files
sourceorder_iddateamountchannel
orders_2026-07.csvA-20012026-07-0342.5
orders_2026-07.csvA-20022026-07-1918
orders_2026-08.csvA-20032026-08-0264web
orders_2026-08.csvA-20042026-08-2727.9phone
orders_2026-09.csvA-20052026-09-0535

Five rows, every value in the right column, and the channel left blank for files that don't have it. The amounts total 187.4, the same as the three source files added together. Pass sep=";" or encoding="latin-1" to read_csv for exports that need them. See merging data in pandas, and merging data in Python, R and SQL for the R and SQL versions.

With DuckDB or SQL

DuckDB reads a whole folder of CSV files with one function call and can line up columns by name, which makes it a good fit for files too large to open in Excel. In the DuckDB command-line tool:

COPY (
  SELECT *
  FROM read_csv('data/*.csv', union_by_name = true, filename = true)
) TO 'merged.csv' (HEADER);

union_by_name matches columns by header rather than position, and filename adds a column with each row's source path, placed last. On the three files above it produces the same five rows as pandas. For files with millions of rows, see merging large CSV files.

Problems that are specific to CSV

  • Delimiters: some exports use semicolons (common in European locales) or tabs. Set the delimiter explicitly.
  • Encoding: accented characters turn into symbols when a UTF-8 file is read as ANSI. Choose UTF-8 (65001) as the file origin.
  • Leading zeros: Excel turns 00123 into 123 when it opens a CSV directly. Import through Power Query and set the column to Text.
  • Line breaks inside quoted fields break naive line-based tools such as cat. Use a CSV-aware method for exports that contain notes or addresses.
  • Byte order marks: some UTF-8 exports begin with an invisible BOM. Concatenated by a text tool, it can end up stuck to the first header (so order_id doesn't match), or appear as stray characters mid-file. CSV-aware readers usually strip it.
  • Date formats: 03/04/2026 is 3 April in a UK export and 4 March in a US one. When files come from systems with different locales, import dates as text and convert them explicitly, or set the locale in Power Query's Change Type → Using Locale.
  • Decimal commas: files using semicolons as delimiters often write 42,50 for 42.50. Set the locale when importing, or the amounts become text.

Before merging a batch from an unfamiliar source, open one file in a plain text editor rather than Excel. You see the real delimiter, quoting and date format, and nothing has been converted on the way in.

In Google Sheets

Google Sheets has no folder import, but for a handful of files you can append them one at a time: open the first CSV with File → Import → Upload, then import each further file with the import location set to Append to current sheet. Every appended file brings its header row with it, so filter the first column for the header text and delete those rows at the end. Appending also matches by position, so check that all files share the same column order first.

Sheets has a cell limit per spreadsheet, which large exports reach sooner than Excel's row limit; for more than a few hundred thousand rows a merge in pandas, DuckDB or a browser tool is more comfortable. If the CSVs are already imported into separate tabs or separate Sheets files, stacking them with ={Jan!A2:D; Feb!A2:D} or IMPORTRANGE is covered in combining data in Google Sheets.

Stacking or joining: check which one you need

Everything above stacks files: more rows, same columns. Sometimes two CSV files describe different things that share an ID, such as an orders export and a customers export, and you want their columns side by side. That is a join, not a merge in this sense, and stacking them produces a table that is half empty. See joining two CSV files by a column or the wider guide to joining spreadsheets.

A practical test: if most column headers are the same across the files, stack them. If the files share only one or two ID columns and otherwise hold different fields, join them.

Merging online

Most online CSV mergers upload your files to a server. The Analistable CSV merger runs in your browser instead: headers are matched by name, a source column is added, and you download one CSV.

Worked example

Three daily exports merged into one table
sourceorder_iddateamount
orders_2026-10-01.csvA-10012026-10-0142.5
orders_2026-10-01.csvA-10022026-10-0118
orders_2026-10-02.csvA-10032026-10-0264
orders_2026-10-03.csvA-10042026-10-0327.9

One header row, one source column, rows in file order. If the file names carry the date, as here, you can also turn the source column into a proper date column for sorting and grouping.

Check the merged file

  • Row count: the merged file should have the sum of each file's data rows plus one header. On Mac or Linux, wc -l *.csv lists line counts per file.
  • Header rows in the data: filter the first column for its own header text; any hit means a header was copied as data.
  • Column count: a row with more or fewer fields than the header usually means an unquoted comma inside a value.
  • Encoding: search for “Ô — it's the tell-tale sign of UTF-8 text read as ANSI.
  • Totals: add up one numeric column in each source file and in the merged file. In the pandas example, 60.5 (July) + 91.9 (August) + 35.0 (September) = 187.4, matching the merged amount column. A smaller total means rows were lost; a larger one usually means a file was read twice.
  • Duplicates across files: exports with overlapping date ranges repeat rows at the boundaries. Count the order IDs and compare with the distinct count; if they differ, remove duplicates on the ID after merging, not before.

Troubleshooting a merged CSV

Symptoms, causes and fixes
SymptomLikely causeFix
Header text appears as data rowsFiles were concatenated with copy or catUse awk, PowerShell Import-Csv or Power Query, or filter out the header rows
Everything lands in column ASemicolon or tab delimiter read as commaSet the delimiter explicitly when importing
é shows as éUTF-8 file read as ANSI / Windows-1252Import with UTF-8 (65001) as the file origin
A whole column from later files is missingPower Query expand step or Export-Csv used the first file's columnsRebuild with a full column list, or use pandas or DuckDB with union_by_name
Row count is higher than expectedThe output file was saved inside the input folder and read back inSave the merged file in a different folder
One row is very long, then rows look shiftedA file with no final line break was joined to the next file's first lineUse awk or a CSV-aware tool rather than copy or cat
IDs lost their leading zerosExcel converted the column to numbers on openingImport the column as Text in Power Query, or read it as a string in pandas

Merging the same exports every month

  1. Create one folder per series (for example orders/raw) and save each export there with the period in the file name, such as orders_2026-10.csv.
  2. Point Power Query From Folder, a saved pandas script or a DuckDB query at that folder, and keep the output in a separate orders/merged folder.
  3. After each new file arrives, refresh or rerun, then run the checks above: row count, header rows in the data, column count and encoding.
  4. Keep a one-line log of each month's row count and total. A month that is suddenly half the size of the previous one usually means a partial export, which is far easier to spot in a log than in the merged file.

If the export arrives in different shapes from month to month, add the cleaning (renaming columns, fixing types) to the query itself so it runs the same way every time, rather than fixing files by hand before merging.

Which method should you choose?

  • Same columns, many files, you're comfortable with Terminal → awk or PowerShell.
  • Files from different systems (delimiters, encodings, extra columns) → Power Query or pandas.
  • You merge the same exports every week → Power Query From Folder with Refresh.
  • One-off merge, no installation, sensitive data → a browser-local combiner.

Every guide in this topic

Frequently asked questions

How do I merge multiple CSV files into one?
Use Excel's Data → Get Data → From Folder → Combine, a one-line command (awk on Mac/Linux, PowerShell on Windows), pandas in Python, or a browser combiner that keeps a single header row.
Why does my merged CSV have header rows in the middle?
Simple concatenation (copy *.csv or cat) copies each file's header. Use a method that skips the first line of every file after the first.
Can I merge CSV files with different columns?
Yes, if the method matches columns by header name: Power Query, pandas and the Analistable combiner do. Command-line concatenation does not.
How do I add the file name as a column when merging CSV files?
Power Query adds a Source.Name column automatically. In pandas, insert the file name into each DataFrame before concatenating; in DuckDB, read_csv with filename = true; with awk, print FILENAME before each line.
Why are my merged CSV columns misaligned?
The files have different columns or a different column order, and the method joined them by position. Use a method that matches headers by name, such as Power Query, pandas or DuckDB with union_by_name.
Is there a limit to how many CSV files I can merge?
Not in the methods themselves, but Excel sheets hold at most 1,048,576 rows. For more, keep the result as CSV or load it into a database.