Powershell Out-File Doesn't Work For CSV

7
votes

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?

3
powershell

3 Answers

10
votes

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).

PowerShell Out-File vs. Set-Content to a CSV File

@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.

2
votes

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.

1
votes

I was having the same problem than Backwards_Dave and just like him, using the Set-Content instead of Out-File command worked for me:

#Build $temp String
$temp = "$scriptApplication;$applicationName;$type;$description;$maxSessions;$enabled;$tempParams`n"

#Write $temp String to $fichierCsv file
"$temp" | Set-Content $fichierCsv

I tried using JPBlanc's and J0e3gan's solution but it did not work (-Encoding ascii option): I wonder why.