In PowerShell if you have output a CSV (comma separated values) file and open it Excel only to see it all munged onto one line, then you're probably using the Out-File
cmdlet instead of Set-Content
.
Set-StrictMode -Version Latest
$ErrorActionPreference = "Continue"
Cls
$array = @()
for ($i = 0; $i -lt 20; $i++) {
$random = Get-Random -Maximum 10
$array += [PSCustomObject] @{
Column_1 = Get-Random -Maximum 10
Column_2 = Get-Random -Maximum 10
Column_3 = Get-Random -Maximum 10
}
}
$array | ConvertTo-Csv -NoTypeInformation
$array | ConvertTo-Csv -NoTypeInformation | Out-File C:\Temp\Out-File.csv
"Column_1","Column_2","Column_3" "3","7","7" "9","0","8" "7","3","3" "1","8","8" "2","2","1" "4","5","9" "1","4","1" "8","9","8" "3","6","6" "9","0","6" "1","6","8" "4","2","1" "3","1","5" "8","9","3" "8","3","9" "9","6","2" "4","5","2" "2","1","8" "5","6","0" "7","8","8"
We have populated a table on screen and also sent to a file with Out-File
. Let's see what happens when we open it in Excel by double-clicking the file icon in Explorer.
Wah wah. It didn't import properly. If you use the menus to go through File -> Open -> Computer, and open the CSV file you will get an Import Wizard which lets you specify commas and import it properly.
But that's so much work. If you do this instead…
$array | ConvertTo-Csv -NoTypeInformation | Set-Content C:\Temp\Set-Content.csv
All I've done is swap Out-File
for Set-Content
. Now double-clicking this file's icon in Explorer will open it in Excel and also automatically parse the commas to display properly without any further fiddling.