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!
Chad Callihan 
Here is an easier way to combine csv files. You could even make it a one liner by removing the variables. In addition, you could assign the contents of the CSVs to a variable for additional manipulation, such as removing duplicates, adding or removing columns, etc..
$csvFiles = Get-ChildItem “C:\Users\Chad\Desktop\PSTest” -Filter *.csv
$outputFile = “C:\Users\Chad\Desktop\PSTest\combined.csv”
Import-Csv -Path $CsvFiles | Export-Csv -Path $OutputFile -NoTypeInformation
LikeLiked by 1 person
I had to expand on the above script as I was working with Unix files with LF endings and needed to preserve the LF endings so had to get the raw data. The above example will add the CRLF if using Set-Content or Out-file. Here is what I came up with:
# 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”
$date = Get-Date
$formatDate = $date.ToString(“yyyyMMdd”)
$newFilename = “$outputFile$formatDate.csv”
# Set a flag to check if it’s the first file so we can prevent multiple header lines
$firstCSV = $true
$index = 0
# Loop through each CSV file
foreach ($file in $csvFiles) {
# Read the contents of the current CSV file preserving the Unix lf characters
$content = Get-Content $file.FullName -raw -Encoding Ascii
# remove excess newlines
$Newcontent = $content -replace “`n`n”, “”
# If it’s the first CSV file, include the header
if ($firstCSV) {
$header = Get-Content $file.FullName | Select-Object -First 1
$header = $header -replace “‘r’n”, “`n”
$index = $header.Length + 1
$header + “`n” | Set-Content $newFilename -Encoding ascii -NoNewline
$firstCSV = $false
}
#Header is as many characters in each file – so just get the rest
$Newcontent = $Newcontent.Substring($index)
# Append the CSV to our new combined destination file
$Newcontent | Out-file $newFilename -Append -Encoding ascii -NoNewline
}
LikeLike
How would you remove duplicate entries? When for examples the files you import could contain the same rows
LikeLike
One option would be to import the CSV, use the -Unique parameter to filter unique values, and export that CSV as a new file. It could look this:
Import-Csv C:\Users\Chad\Desktop\Test.csv | Sort-Object Publisher,Identity,Name,Hometown -Unique | Export-Csv -Path C:\Users\Chad\Desktop\Unique_Only.csv -NoTypeInformation
LikeLike