Merge several CSV files into one, without copy-pasting

One export a month, one file per colleague, one extract per program: CSV files pile up, and they have to be combined before they can be analysed. Copy-pasting works for two or three files; beyond that, it takes time and lets errors through. Here is another way to go about it.

Why copy-pasting eventually becomes a problem

• you have to start over with every new batch of files;
• a forgotten row, or one pasted twice, easily goes unnoticed;
• when columns are not in the same order, or one is missing from a file, the values shift;
• once the files are combined, you no longer know which file a row came from.

What an automatic merge should do

Before choosing a method, here are the points that matter:

• combine all the columns of the files, even if they are not identical from one file to another (a missing column simply stays empty);
• keep the origin of each row, for example in a “source file” column;
• never overwrite an existing file unless you asked for it;
• produce a file that opens correctly in Excel, accents included.

A ready-made solution: a PowerShell script

I wrote a PowerShell script that does exactly this: it reads all the CSV files in a folder and combines them into a single file. It runs on Windows 10 and 11 with the PowerShell already installed, without administrator rights.

1. Get the script: sent on request through the contact page, together with two sample files and a user guide.
2. Prepare: place the CSV files to merge in one folder.
3. Run the script in PowerShell, giving the folder to read and the file to produce. The default separator is the semicolon, common in France; an option lets you use the comma.
4. Open the resulting file in Excel: all the rows are combined, with a column showing their original file.

A few precautions

• Always keep the original files: the script does not modify them, but a backup copy costs nothing.
• Check the result on a small sample before processing a large batch.
• If Windows blocks a downloaded script, the user guide that comes with the script explains how to unblock it (file properties).

And if your need is more specific?

This script covers the common case. If your files need specific processing (cleaning, calculations, automatic sending of the result, a link with other software), it can be adapted or replaced by a custom automation. To go further on this topic, also read five signs an Excel file deserves to be automated.