Write to CSV (manually)

This topic contains 4 replies, has 3 voices, and was last updated by Profile photo of Michael Maher Michael Maher 10 months, 4 weeks ago.

  • Author
    Posts
  • #33847
    Profile photo of Michael Maher
    Michael Maher
    Participant

    Hi Folks,

    Maybe I'm stuck in my vbScript way of thinking but I can't figure out a way to write a comma separated list of variables when one of variables is an array.

    I've a couple of nested For loops in my script so I've tried to keep this simple and posted just a snippet from my code. The script identifies a collection of AD Groups and then enumerates their members.

                $arrRecord."groupName" = $securityGroup
                $arrRecord."groupMembers" = $samAccountName
    
                forEach ($element in $arrRecord){
                           Write-Host $element.groupName $element.groupMembers
    

    This return the groupname username1 username2

    For example . . .

    Dept_Networks&IT JSMITH
    Dept_HR LJONES MJACKSON

    How can I write a comma between each value? I could cheat and do a find/replace after I gather the output but given how simple this would have been in vbScript I'm sure I'm missing a trick.

    Thanks,

    Michael

  • #33861
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    Are you wanting a comma between the group name and the group members like this

    Dept_HR, LJONES MJACKSON

    Or between the individual group members like this

    Dept_HR LJONES, MJACKSON

    Or between all the elements like this?

    Dept_HR, LJONES, MJACKSON
  • #33862
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    Here is an example of doing it all three ways using the -join operator

    $arrRecord = [pscustomobject]@{
        groupName = "Dept_HR";
        groupMembers = @("LJONES", "MJACKSON")
    }
    
    #$arrRecord
    
    @($arrRecord.groupName, ($arrRecord.groupMembers -join " ")) -join ","
    @($arrRecord.groupName, ($arrRecord.groupMembers -join ",")) -join " "
    @($arrRecord.groupName, ($arrRecord.groupMembers -join ",")) -join ","
    

    Results:

    Dept_HR,LJONES MJACKSON
    Dept_HR LJONES,MJACKSON
    Dept_HR,LJONES,MJACKSON
    
  • #33863
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    You're missing some fundamentals of Powershell. Let's walk through you scenario. When you want to get the members of single group, we do something like this:

    Get-ADGroupMember -Identity HelpDesk
    

    This returns data in a PSObject, which is typically what cmdlets return in Powershell. If we wanted to send this data to a CSV, we could simply do:

    Get-ADGroupMember -Identity HelpDesk | Export-CSV C:\mycsv.csv -NoTypeInformation
    

    If we only wanted the Name and SamAccountName properties, we use Select-Object:

    Get-ADGroupMember -Identity HelpDesk |
    Select Name, SamAccountName | 
    Export-CSV C:\mycsv.csv -NoTypeInformation
    

    Now, from what you are posting, it sounds like you have an array of groups and are gathering the members.

    #Array of groups
    $groups = "HelpDesk", "DesktopSupport"
    
    #Assign a variable to capture everything from our for loop
    #Loop through each group
    $results = foreach ($group in $groups) {
        #Pass the group variable to the Get-AdGroupMember
        Get-ADGroupMember -Identity $group |
        Select Name,
                  SamAccountName,
                  @{Name="GroupName"; Expression={$group}}
        #The last property is a calculated property, it's one method of appending to a new property to an existing property
    }
    
    #Export the results to a CSV
    $results | Export-CSV C:\mycsv.csv -NoTypeInformation
    

    I would recommend getting a Powershell book and getting some fundamentals down. There is a great deal of built-in functionality for working with data in Powershell whereas vbScript everything has to be done manually.

  • #33875
    Profile photo of Michael Maher
    Michael Maher
    Participant

    Thanks Gents,

    Curtis, your solution worked. I just had to edit one line in my original script.

    Rob, the flow of logic in your script was much superior to mine so I ditched my script.

    Thanks,

    Michael

You must be logged in to reply to this topic.