Merging CSV Files with Powershell
Powershell is really useful for documenting and managing your servers but it’s also a pretty good tool for working with data. I’ve been using it to merge csv files, with an identical structure, into a single file. Now this is pretty easy, if rather tedious, to do using SQL Server Import / Export functionality or with SSIS. Powershell makes this a snap!
In this example I have two csv files in a directory
These just contain some simple name and age data.
This simple script will produce a third file, merging the contents of file1.csv and file2.csv.
# Author: Rhys Campbell # 2009-08-22 # Merging identical csv files # Directory containing csv files, include *.* $directory = "C:\Users\Rhys\Desktop\csv\*.*"; # Get the csv files $csvFiles = Get-ChildItem -Path $directory -Filter *.csv; # Updated 01/03/2010. Thanks to comment from Chris. # Resolves error Method invocation failed because [System.Management.Automation.PSObject] doesn't contain a method named 'op_Addition'. #$content = $null; $content = @(); # Process each file foreach($csv in $csvFiles) { $content += Import-Csv $csv; } # Write a datetime stamped csv file $datetime = Get-Date -Format "yyyyMMddhhmmss"; $content | Export-Csv -Path "C:\Users\Rhys\Desktop\csv\merged_$datetime.csv" -NoTypeInformation; |
If all goes as planned a new file will be created.
This file will contain data from both csv files.
This is obviously a fairly trivial example but it’s a massive timesaver when you have many such files to merge. Word of warning, if you’ve got very big files, you may want to change the script to use Add-Content, to flush each csv file to disk in the foreach loop, to avoid munching up all your RAM.


Hi, I got the following error running the script:
Method invocation failed because [System.Management.Automation.PSObject] doesn’t contain a method named ‘op_Addition’.
I found through another blog that “$content = null” needs to be changed to “content = @().”
Thanks!
Thanks Chris. I get the same error when trying this now. Perhaps a version issue. Anyway, I’ve updated the script.
Cheers,
Rhys
Thanks for the tip, it worked very well.