Write to CSV (manually)

This topic contains 4 replies, has 3 voices, and was last updated by  Michael Maher 1 year, 7 months ago.

  • Author
    Posts
  • #33847

    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

    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

    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

    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

    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.