I'm using Get-ADComputer to export computer details to a CSV with semicolons as the delimiter. The exported properties include the computer name, operating system, creation date, and DistinguishedName. When I open the file in Excel, the distinguished name is split at its commas instead of staying in one column. I also tried manually adding escaped quotes around the DistinguishedName, but the quotes and backticks were written literally into the CSV. My export command is:
$computerData | Sort-Object -Property Name | Export-Csv $outputFile -NoTypeInformation -Delimiter ";"
How can I keep the entire DistinguishedName in one Excel column?
3 Answers
Don’t build escaped quotes into the property value. That changes the actual data and is why the quote characters appear in the spreadsheet. Keep the property as the raw value:
DistinguishedName = $computer.DistinguishedName
Export-Csv handles CSV quoting automatically. If you want a more readable AD location, you could also export CanonicalName, or remove the computer name from it so the organizational path is easier to sort and filter.
Export-Csv should quote a field automatically when it contains the delimiter, so you normally shouldn’t add quotes yourself. The likely problem is that Excel is using a comma as its configured list separator while your file uses semicolons. Import the file through Excel’s data-import wizard and select semicolon as the delimiter, or change the Windows regional List separator to semicolon before opening it. Then a value such as CN=PC1,OU=Desktops,DC=office will remain in one column.
Another simple option is to use a tab-delimited export instead:
$computerData | Sort-Object Name | Export-Csv $outputFile -NoTypeInformation -Delimiter "`t"
Open it using Excel’s import process and choose Tab as the delimiter. This avoids conflicts between commas in distinguished names and regional CSV settings.
Tab-delimited files are often easier when the data itself contains commas, although Excel still needs to be told that the separator is a tab during import.
Exactly. If Excel is parsing with commas, it treats the commas inside the DN as separators. The PowerShell export can be correct even though double-clicking the file opens it incorrectly.