Combine CSV Files Using PowerShell

I recently had a handful of CVS files that needed reviewed. Each CSV file was the same format, and while I could have opened them each individually to sort and review, I thought it would be much easier to combine them into one file. It was time to turn to PowerShell. Let’s look at a few examples of how PowerShell can be used to combine multiple CSV files into a single file.

Have Some Character

We’ll start with three example files. Each will contain a few comic book characters and their publisher.

We want to combine our CSV files for DC, Marvel, and Boom.

One short PowerShell script to combine these files is:

Get-Content "C:\Users\Chad\Desktop\PSTest\*.csv" | 
	Add-Content "C:\Users\Chad\Desktop\PSTest\combined.csv"

We’re using Get-Content to check the path, pulling all csv files (with “all” being the * just like in SQL), and then combining them together into a new file. If we open our new combined.csv file, we’ll see all of our CSV files successfully combined:

Combine But Keep Header Once

What may or may not be an issue for you is the inclusion of the “Publisher,Identity,Name,Hometown” one time for each CSV file. It might be easier to take this combined file, sort the records in excel, and manually clean up the duplicate headers. In our case, we’re going to want the file to be good to go right after running a PowerShell script.

This script is a little bit more complicated but will get us the result we want:

# Set our path of source CSV files and our destination file
$csvFiles = Get-ChildItem "C:\Users\Chad\Desktop\PSTest" -Filter *.csv
$outputFile = "C:\Users\Chad\Desktop\PSTest\combined.csv"

# Set a flag to check if it's the first file so we can prevent multiple header lines
$firstCSV = $true

# Loop through each CSV file
foreach ($file in $csvFiles) {
    # Read the contents of the current CSV file
    $content = Get-Content $file.FullName | Select-Object -Skip 1
    
    # If it's the first CSV file, include the header
    if ($firstCSV) {
        $header = Get-Content $file.FullName | Select-Object -First 1
        $header | Out-File $outputFile
        $firstCSV = $false
    }
    
    # Append the CSV to our new combined destination file
    $content | Out-File $outputFile -Append
}

Perfect Time to Learn More

If you’re interested in learning more about PowerShell, check out free training on Brent Ozar’s blog in the month of March. New content is being released each weekday so make sure you keep going back to get your fix.

Thanks for reading!