How to Fix Get-ADComputer CSV Output Showing as Multiple Lines in Excel?
Asked by
TechWizard42
I'm having some trouble with the output from the Get-ADComputer command when exporting it to CSV. Most entries look fine, like this:
mot.sz0144/WinServ Backup Server/Z001S001LBS01 [10.1.1.71](http://10.1.1.71) 10/7/2023 15:52,
but some entries are breaking into two lines. For example:
"mot.sz0144/Site Containers Hosts/z001s001rfe02
CNF:65a00800-62ab-4225-923d-619d0080371c" [10.101.1.229](http://10.101.1.229) 10/23/2023 10:30.
When I open the CSV in Excel, everything looks great, but the output I'm trying to parse comes out in two lines instead of one. Here's the command I'm using:
Get-ADComputer -Filter * -properties canonicalname,IPv4Address,LastLogonDate | select canonicalname,IPv4Address,LastLogonDate | Export-CSV c:computers_by_OU.csv -NoTypeInformation. Any tips on how to fix this?
Answers
Have you tried using `Import-Csv` when reading the output? It might streamline how you're parsing this data rather than relying on the default methods. Just a thought!
Reply to this answer
I think the issue stems from the CN containing a CR or LF in some cases. You might want to tweak your parsing to handle these line breaks better. A clever regex could help replace those before exporting, or you might look for a CSV parser that can manage multiline content seamlessly. It's worth a try!
Reply to this answer
Those CNF:* objects pop up when conflicting updates happen on different AD controllers. It might be worth looking into the infrastructure if these 'problem' lines all relate to devices not seen in over a year. There could be some underlying issues there.
Reply to this answer
It sounds like you're dealing with some special characters in your CN, possibly slashes or line breaks that are causing the output to split. Instead of using the default export, have you considered the Import-Excel module? It allows a lot more control over formatting and can handle special characters better. If you can't install additional modules, try running this command:
`Get-ADComputer -Filter * -Properties canonicalname, IPv4Address, LastLogonDate | Select-Object @{Name='canonicalname'; Expression={$_.canonicalname -replace "[rn]", ""}}, IPv4Address, LastLogonDate | Export-CSV c:computers_by_OU.csv -NoTypeInformation`
This should help clean up those problematic lines!
FixItFelix: Thanks for the suggestion! I'll definitely give that a shot.
Reply to this answer
Add your answer
Answers are reviewed before publication.
TechWizard42: That's interesting! I hadn't made that connection yet.