Formatting help with Write-Output on a .CSV

This topic contains 5 replies, has 3 voices, and was last updated by Profile photo of Rob Simmers Rob Simmers 3 weeks, 5 days ago.

  • Author
    Posts
  • #57278
    Profile photo of Colter
    Colter
    Participant

    Hi Folks,

    I have a report (.csv file) that contains user IDs, domains, and what they have access to in that domain. For users that have access to multiple domains their ID appears multiple times in report.

    USERID,DOMAIN,CBM,CCA,CME
    IE001,AA,CBM,CCA,CME
    IE002,AA,CBM,,CME
    IE001,AL,CBM,CCA,CME
    IE002,AL,CBM,,CME			
    IE003,AL,CBM,,CME
    

    To import this information in to our access review system I need to combine a user's access rights into a single line then format the output like so into a .txt file:

    IE001, AA CBM, AA CCA, AA CME, AL CBM, AL CCA, AL CME
    
    IE002, AA CBM, AA CME, AL CBM, AL CME
    
    IE003, AL CBM, AL CME
    

    How could I accomplish this?

    Thanks

  • #57290
    Profile photo of Charles Downing
    Charles Downing
    Participant
    1. Use Import-Csv to pull the CSV data into memory
    2. Get list of all unique UserIDs using either "Select-Object -Unique" or Get-Unique
    3. For each of the unique UserIDs, use Where-Object to filter the CSV
    4. Loop through the records returned from the Where-Object to output to the format you're looking for

    This may be a good start:

    $data = Import-Csv 
    $outData = @()
    $uniques = $data | Sort-Object UserID | Select-Object UserID -Unique
    $uniques | ForEach-Object {
        $outputLine = "$_"
        $filtered = $data | Where-Object UserID -eq $_
        $filtered | ForEach-Object {
            if ( $_.CBM ) {
                $outputLine = $outputLine + ",$_.Domain CBM"
            }
            if ( $_.CCA ) {
                $outputLine = $outputLine + ",$_.Domain CCA"
            }
            if ( $_.CME ) {
                $outputLine = $outputLine + ",$_.Domain CME"
            }
        }
        
        $outData += $outputLine
    }
    
    $outData
    
  • #57293
    Profile photo of Colter
    Colter
    Participant

    Thanks Charles,

    From this script I am getting the following:

    $data = Import-Csv C:\data.csv 
    $outData = @()
    $uniques = $data | Sort-Object UserID | Select-Object UserID -Unique
    $uniques | ForEach-Object {
        $outputLine = "$_"
        $filtered = $data | Where-Object UserID -eq $_
        $filtered | ForEach-Object {
            if ( $_.CBM ) {
                $outputLine = $outputLine + ",$_.Domain CBM"
            }
            if ( $_.CCA ) {
                $outputLine = $outputLine + ",$_.Domain CCA"
            }
            if ( $_.CME ) {
                $outputLine = $outputLine + ",$_.Domain CME"
            }
        }
        
        $outData += $outputLine
    }
    
    $outData
    @{USERID=}
    @{USERID=IE001}
    @{USERID=IE002}
    @{USERID=IE003}
    

    What am I missing?

  • #57343
    Profile photo of Charles Downing
    Charles Downing
    Participant

    I went back and tested it and made some changes. Try this version. It spit out what I think you were expecting.

    $data = Import-Csv C:\Users\Student\Documents\data.csv
    $outData = @()
    $uniques = $data | Sort-Object UserID | Select-Object UserID -Unique
    $uniques | ForEach-Object {
        $userID = $_
        $outputLine = "$($userID.USERID)"
        $filtered = $data | Where-Object {$_.USERID -eq $userId.USERID}
        $filtered | ForEach-Object {
            if ( $_.CBM ) {
                $outputLine = $outputLine + ",$($_.Domain) CBM"
            }
            if ( $_.CCA ) {
                $outputLine = $outputLine + ",$($_.Domain) CCA"
            }
            if ( $_.CME ) {
                $outputLine = $outputLine + ",$($_.Domain) CME"
            }
        }
        
        $outData += $outputLine
    }
    
    $outData
    

    IE001,AA CBM,AA CCA,AA CME,AL CBM,AL CCA,AL CME
    IE002,AA CBM,AA CME,AL CBM,AL CME
    IE003,AL CBM,AL CME

  • #57359
    Profile photo of Colter
    Colter
    Participant

    Awesome. Thank you.

  • #57361
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    Another way to do it:

    $obj = @()
    $obj += [pscustomobject]@{
        USERID = "IE001"
        DOMAIN = "AA"
        CBM = "CBM"
        CCA = "CCA"
        CME = "CME"
    }
    
    $obj += [pscustomobject]@{
        USERID = "IE002"
        DOMAIN = "AA"
        CBM = "CBM"
        CCA = $null
        CME = "CME"
    }
    $obj += [pscustomobject]@{
        USERID = "IE001"
        DOMAIN = "AL"
        CBM = "CBM"
        CCA = "CCA"
        CME = "CME"
    }
    $obj += [pscustomobject]@{
        USERID = "IE002"
        DOMAIN = "AL"
        CBM = "CBM"
        CCA = $null
        CME = "CME"
    }
    $obj += [pscustomobject]@{
        USERID = "IE003"
        DOMAIN = "AL"
        CBM = "CBM"
        CCA = $null
        CME = "CME"
    }
    
    $test = $obj | Select USERID, 
                  @{Name = "DOMAINCBM"; Expression = { if ($_.CBM) {"{0} {1}" -f $_.DOMAIN, $_.CBM} else {$null} }},
                  @{Name = "DOMAINCCA"; Expression = { if ($_.CCA) {"{0} {1}" -f $_.DOMAIN, $_.CCA} else {$null} }},
                  @{Name = "DOMAINCME"; Expression = { if ($_.CME) {"{0} {1}" -f $_.DOMAIN, $_.CME} else {$null} }}
    
    $test | Group-Object -Property USERID | foreach {
        $arr = @()
        $arr += $_.Name
        foreach($item in $_.Group) {
            $arr += $item.DomainCBM
            $arr += $item.DOMAINCCA
            $arr += $item.DOMAINCME
        }
        
        Add-Content -Path C:\MyFile.txt -Value (($arr | Where{$_})  -Join ",")
    }
    

    Output:

    IE001,AA CBM,AA CCA,AA CME,AL CBM,AL CCA,AL CME
    IE002,AA CBM,AA CME,AL CBM,AL CME
    IE003,AL CBM,AL CME
    

You must be logged in to reply to this topic.