Fixing Encoding for Excel CSV

by Apr 8, 2014

When you save Microsoft Excel data as CSV, unfortunately the encoding does not match the default encoding used by Import-Csv. So when you import the CSV file into PowerShell, special characters may be garbled, no matter what encoding you specify.

Here is a file called list.csv that I exported from Excel, and it contains some special characters. If you use default encoding, special characters are garbled, and if you do specify the -Encoding parameter, no matter what you do, special characters never seem to turn back to what they originally were:

When you mimick what Import-Csv does behind the scenes, it surprisingly works beautifully:

As it turns out, to read in Excel CSV files correctly, you must explicitly specify "Default" encoding (which coincidentally raises the question what the default encoding might be that is used when you do not specify any encoding):

Twitter This Tip! ReTweet this Tip!