I'm using Get-ADComputer to export computer information to a CSV with Name, operating system, creation date, and DistinguishedName columns. I set Export-Csv to use a semicolon as the delimiter, but when I open the file in Excel, the distinguished name—such as CN=PC1,OU=Desktops,DC=office—is split at its commas. I tried manually adding escaped quotes around the values, but the quotes and backticks appeared in the CSV instead. My export command is:
$computerData | Sort-Object -Property Name | Export-Csv $outputFile -NoTypeInformation -Delimiter ";"
How can I keep the distinguished name in one column when opening the CSV in Excel?
3 Answers
You can avoid locale-dependent CSV behavior by exporting with a tab delimiter instead:
$computerData | Sort-Object Name | Export-Csv $outputFile -NoTypeInformation -Delimiter "`t"
Open the result as a tab-delimited file in Excel. The DN's commas will then remain part of the same field.
Export-Csv is already designed to quote fields when necessary, so don't add escaped quotes inside the calculated properties. The likely problem is that Excel is using a comma as its list separator while the file was written with semicolons. Change the Windows regional List separator to a semicolon, then reopen the file. Excel should treat the semicolons as column separators and preserve the commas inside the distinguished name.
The object construction looks fine. Keep DistinguishedName as the original string and let Export-Csv handle quoting. Manually inserting quote characters changes the data itself, which is why the quotes and backticks show up in the spreadsheet. If you want an easier-to-read AD location, you could also export CanonicalName, optionally removing the final computer-name component.
That explains it—the file was being interpreted with Excel's configured separator instead of the delimiter supplied to Export-Csv.