Concatenate CSV Files with PowerShell

For us Unix/Linux folks, the classic “cat” command is a beautiful way to combine text files together. Because of this popular Unix command, Microsoft ships PowerShell with a default alias cat which maps to their Get-Content command.

Unfortunately, the similarities end with only the name being the same. PowerShell makes life difficult by defaulting to two-byte character unicode output for text files. This breaks some popular programs like Excel which by default expects a CSV file to contain only single-byte characters and will put all text into a single column for multi-byte unicode CSV file. Some workarounds are discussed on the Stack Overflow question “… correctly … display .csv files within Excel 2013?” The syntax is also different – you must separate multiple file names by commas (the PowerShell array delimiter).

To resolve the multi-byte unicode text file output problem, I recommend replacing the default redirect Some-Command > OutFile.csv with the more versatile Some-Command | Out-File -Encoding Default OutFile.csv which allows you to specify a desired text output encoding. The “Default” encoding requested here is standard ANSI text with single-byte characters. This also resolves the issue with Excel which will open ANSI text CSV files properly (separate columns versus all data in a single column). Here’s an example of how to use PowerShell to concatenate CSV files for later use in Excel.

Get-Content FileOne.csv,FileTwo.csv,FileThree.csv `
  | Out-File -Encoding Default TestOne.csv
# A Wildcard can indicate multiple matching files
Get-Content File*.csv | Out-File -Encoding Default TestTwo.csv
Import-CSV (Get-ChildItem File*.csv) | Export-CSV TestThree.csv `
    -NoTypeInformation -Encoding UTF8
# AN ADVANTAGE to the Import/Export CSV method is that it will 
#   eliminate duplicate header rows

This note was inspired in part by the following Super User questions:

About notesbytom

Keeping technology notes on to free up my mind to solve new problems rather than figuring out the same ones repeatedly :-).
This entry was posted in System Administration and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s