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.