Powershell Out-File Doesn't Work For CSV

Question

I have the following code:

$databaseContents = "col1,col2,col3,col4"
$theDatabaseFile = "C:\NewFolder\Database.csv
$databaseContents | Out-File $theDatabaseFile

However when I open the csv file in Excel, it has col1,col2,col3,col4 in cell A1 rather than col1 in cell A1, col2 in cell B1 etc.

Something strange I've noticed: If I open the file in Notepad and copy the text into another Notepad instance and save it as Database1.csv, then open it in Excel, it displays as expected.

How can I get the Out-File commandlet to save it as a .csv file with the contents in 4 columns as expected?

EDIT: I've noticed if I use Set-Content rather than Out-File, the csv file is then displayed correctly in Excel. Does anyone know why this is?

Answer 1

Why it makes a difference to Excel I am unclear, but it comes down to the encoding of the resulting output file - Unicode (in the cases that do not work) vs. ASCII (in the cases that do).

@JPBlanc's alternate approach works because it sets the encoding of the output file to ASCII where your original example (implicitly) set the encoding of the output file to Unicode.

Just adding -Encoding ascii to your original example works fine too:

$databaseContents = "col1,col2,col3,col4"
$theDatabaseFile = "C:\NewFolder\Database.csv
$databaseContents | Out-File $theDatabaseFile -Encoding ascii

And adding an explicit -Encoding unicode to your original example yields the same broken result you encountered:

$databaseContents = "col1,col2,col3,col4"
$theDatabaseFile = "C:\NewFolder\Database.csv
$databaseContents | Out-File $theDatabaseFile -Encoding unicode

This is basically what was happening implicitly.

Answer 2

This works also :

$databaseContents = "col1;col2;col3;col4"
$theDatabaseFile = "C:\temp\Database.csv"
$databaseContents | Out-File $theDatabaseFile -Encoding ascii

By default CSV separator in Excel seams to be ';' and Out-File save as unicode forcing ASCII seams to give the result you look for.