How to merge CSV files on Windows
By the Analistable team · Updated · 5 min read
In the folder that holds the files, Command Prompt's copy /b *.csv ..\combined.csv joins them in seconds, but every file's header ends up in the data. To keep only the first header, use PowerShell: read the first file whole, then append every other file with its first line skipped. Excel's Power Query From Folder is the no-command alternative.
Part of our guide: How to merge CSV files into one
Command Prompt: fastest, but repeats headers
cd C:\Exports\September
copy /b *.csv ..\combined.csv/bcopies in binary mode. Without it,copycan add an end-of-file character (Ctrl+Z) that shows up as a stray symbol in the last row.- Writing to
..\(the parent folder) stops the output from being picked up as an input. - Each file's header row appears where that file starts. Filter them out afterwards, or use PowerShell.
- If a file doesn't end with a line break, its last row is glued to the next file's first row.
PowerShell: one header row
Open the folder in File Explorer, type powershell in the address bar and press Enter. Then run:
$files = Get-ChildItem *.csv | Sort-Object Name
Get-Content $files[0] -Encoding utf8 | Set-Content ..\combined.csv -Encoding utf8
$files | Select-Object -Skip 1 | ForEach-Object {
Get-Content $_ -Encoding utf8 | Select-Object -Skip 1 | Add-Content ..\combined.csv -Encoding utf8
}The first file is copied whole; every other file is appended without its first line. -Encoding utf8 matters in Windows PowerShell 5.1, which otherwise reads and writes files without a byte-order mark in the system's legacy code page and mangles accented characters. (In PowerShell 7, UTF-8 is the default.)
PowerShell when columns differ
Get-ChildItem *.csv | ForEach-Object { Import-Csv $_ } |
Export-Csv ..\combined.csv -NoTypeInformation -Encoding utf8Import-Csv reads rows as objects with named columns, so files whose columns are in a different order line up correctly. The output uses the first file's columns: columns that only exist in later files are dropped. It's also much slower on large files.
Which to use
| Method | One header | Different column order | Speed on big files |
|---|---|---|---|
| copy /b | No | No | Fastest |
| Get-Content (skip first line) | Yes | No | Fast |
| Import-Csv / Export-Csv | Yes | Yes (first file's columns) | Slow |
| Excel Power Query From Folder | Yes | Yes (by name) | Medium |
No command line? See combining CSV files in Excel or drop the files into the CSV combiner. On a Mac, see combining CSV files on a Mac.
Worked example and how to check it
A folder holds three exports, each with a header row: jan.csv with 1,200 data rows, feb.csv with 1,150 and mar.csv with 1,310.
| Method | Header lines | Data lines | Total lines |
|---|---|---|---|
| copy /b | 3 | 3660 | 3663 |
| PowerShell (skip first line) | 1 | 3660 | 3661 |
| Import-Csv / Export-Csv | 1 | 3660 | 3661 |
1,200 + 1,150 + 1,310 = 3,660 data rows. Count the lines in PowerShell:
(Get-Content ..\combined.csv | Measure-Object -Line).Lines
# or, faster on big files
$n = 0; Get-Content ..\combined.csv -ReadCount 10000 | ForEach-Object { $n += $_.Count }; $nLine counts equal row counts only when no field contains a line break inside quotes. Notes or address fields sometimes do; in that case count rows with (Import-Csv ..\combined.csv).Count instead.
Adding the source file name to each row
Get-ChildItem *.csv | ForEach-Object {
$name = $_.Name
Import-Csv $_ | Select-Object *, @{Name = "SourceFile"; Expression = { $name }}
} | Export-Csv ..\combined.csv -NoTypeInformation -Encoding utf8A SourceFile column makes it easy to trace a bad row back to its export, and to check per-file counts with a PivotTable afterwards.
Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
| combined.csv contains itself, growing each run | The output was written into the same folder | Write to ..\ or another folder |
| Header text appears in the middle of the data | copy /b keeps every file's header | Use the PowerShell script, or filter those rows out |
| Everything in one column in Excel | The files use semicolons, or Excel's list separator differs | Open with Data → From Text/CSV and choose the delimiter |
| “Running scripts is disabled on this system” | Execution policy blocks .ps1 files | Paste the commands into the window instead of running a script file |
| é instead of é | Encoding mismatch | Add -Encoding utf8, or use PowerShell 7 |
Repeating it every month
Save the PowerShell commands in a text file such as merge.ps1 next to the export folders. Each month, drop the new files into a fresh folder, open PowerShell there and paste or run the commands. Sorting by name (Sort-Object Name) keeps the order predictable if files are named with ISO dates such as 2026-09-sales.csv. If the merge feeds an Excel report, the Power Query folder method refreshes with one click instead.
Files with different headers
Before merging, confirm that every file has the same header line. This PowerShell command lists each distinct header and how many files use it:
Get-ChildItem *.csv | ForEach-Object { Get-Content $_ -TotalCount 1 } |
Group-Object | Select-Object Count, NameOne group means the fast Get-Content method is safe. Two or more groups mean the columns differ in order or name: use the Import-Csv method, rename headers first, or load the folder with Power Query, which matches columns by name. A header that differs only by a trailing space or capital letter counts as a different column in every one of these tools.
Frequently asked questions
- How do I combine multiple CSV files into one on Windows 10 or 11?
- Run copy /b *.csv ..\combined.csv in Command Prompt from the folder with the files, or use the PowerShell script that keeps a single header row.
- Why is there a strange character at the end of my merged CSV?
- copy without /b can add an end-of-file character. Use copy /b.
- Why are accented characters broken after merging in PowerShell?
- Windows PowerShell 5.1 writes in a legacy encoding by default. Add -Encoding utf8 to Set-Content and Add-Content.
- Can I merge CSV files on Windows without the command line?
- Yes. In Excel use Data → Get Data → From File → From Folder and Combine & Transform, or use a browser-based CSV combiner that processes the files locally.
- How do I merge CSV files and add the file name to each row?
- Use Import-Csv in a loop with a calculated SourceFile property, then Export-Csv, or use Power Query From Folder, which adds a Source.Name column.