CODEWITHSUNDEEP
powershellcsv
typod
typod

Reputation: 49

Importing CSV splits certain lines

I have a fairly simple script that needs to check around 20,000 AD Groups for their membership count. That all works fine, I can take the list of groups run it through the script and for the most entries it works fine. However I was getting some errors that I couldn't figure out and hopefully someone here can point me in the right direction.

I am using the DN of the object to query AD and for around 10% it fails, but when I copy the DN from the file, paste it into a command window and run the command manually it works fine. Some more checking and it seems that when I read an offending line into my variable there is a line break in the middle for some reason.

When looking at the value of the variable I get the following:

Working Example - "CN=ABC, OU=Location, OU=Distribution Lists, DC=Domain, DC=COM"

Error Example - "CN=ABC, OU=Location, OU=Distribution

Lists, DC=Domain, DC=COM"

It seems to insert a return in-between Distribution and Lists on certain entries in the file. I have tried deleting the character in-between and replacing it with a space but I get the same result.

Could it be the length? I am still looking for a common factor but any suggestions would be great.

Thanks

Updated with requested content.

$Groups = Import-Csv C:\Temp\DLName.csv
write-host ($Groups).Count
$i=1

foreach ($Group in $Groups)
{
$GroupInfo = Get-ADGroupMembersRecursive -Groups $Group.Name
$MembersCount = ($GroupInfo | Measure-Object).Count
$MembersList = $GroupInfo | Select Name -ExcludeProperty Name
$FriendlyName = Get-ADGroup -Identity $Group.Name
    
$Export = $FriendlyName.Name + ", " + $MembersCount 
$Export | Out-File C:\Temp\DLMembers.csv -Append

Write-host $FriendlyName "," $MembersCount

$i
$i++
}

Entry 1 and 3 work 2 doesn't, but the formatting here seems to have wrapped the entries.

Name

"CN=Company - DL Name1,OU=Country1 Distribution Lists,OU=Europe,OU=Acc,DC=Domain,DC=Domain,DC=com" "CN=Company - DL Name2,OU=Country2 Distribution Lists,OU=Europe,OU=Acc,DC=Domain,DC=Domain,DC=com" "CN=Company - DL Name3,OU=Country3 Distribution Lists,OU=America,OU=Acc,DC=Domain,DC=Domain,DC=com"

Top pic is the failure second pic works.

Not Working

Working

List Creation:

$SearchScope = "OU=OUName,DC=Domain,DC=Domain,DC=com"
$SearchFilter = {GroupCategory -eq 'Distribution'}
$Groups = Get-ADGroup -SearchBase $SearchScope -Filter 
$SearchFilter | Sort-Object Name

foreach ($Group in $Groups)
{

$Group.DistinguishedName | Select Name -ExpandProperty Name
$Group.DistinguishedName | Out-File C:\Temp\DLName.csv -Append

}

Upvotes: 0

Views: 80

Answers (1)

Theo
Theo

Reputation: 61028

Do not use a self-combined comma separated string and Out-File to create CSV files, because that will get you into trouble when fields happen to contain the delimiter character like in this case the comma (which will lead to mis-aligned data).

Your List Creation code should be like this:

$SearchBase   = "OU=OUName,DC=Domain,DC=Domain,DC=com"
$SearchFilter = "GroupCategory -eq 'Distribution'"
Get-ADGroup -SearchBase $SearchBase -Filter $SearchFilter | 
    Sort-Object Name | Select-Object Name, DistinguishedName |
    Export-Csv -Path 'C:\Temp\DLName.csv' -NoTypeInformation

Then you can use that csv later to do:

$Groups = Import-Csv -Path 'C:\Temp\DLName.csv'
Write-Host $Groups.Count

$result = foreach ($Group in $Groups) {
    $GroupInfo = Get-ADGroupMember -Identity $Group.DistinguishedName -Recursive
    # unnecessary.. $MembersCount = ($GroupInfo | Measure-Object).Count
    # unused..      $MembersList = $GroupInfo.Name
    # unnecessary.. $FriendlyName = Get-ADGroup -Identity $Group.Name

    # output an object with the wanted properties
    [PsCustomObject]@{
        GroupName   = $Group.Name
        MemberCount = @($GroupInfo).Count  # @() in case there is only one member in the group
    }
} 

# show on screen
$result | Format-Table -AutoSize

# output to CSV file
$result | Export-Csv -Path 'C:\Temp\DLMembers.csv' -NoTypeInformation

As you can see, I'm not using your custom function Get-ADGroupMembersRecursive because I have no idea what that outputs.. Also, there is no need for that because you can use the Get-ADGroupMember cmdlet with the -Recursive switch added

Upvotes: 1

Related Questions