locked
how do I get multiple lines into a single cell (export-csv)? Truncating all but the first item RRS feed

  • Question

  • Hi, 

    I can't find a good example of this so I am asking for my sanity's sake :)

    Have a couple variables that I grabbed via various things and binding them to a new object but when exporting the csv it only lists the first item in that array.

    $exclude = ('<mydomain>\Account Creators', 'BUILTIN\Administrators', '<mydomain>\<other exclusion groups')
    
    #array to keep track of everybody till  the end
    $list = @()
    
    foreach($user in $userlist){
    
      #do some things like get user name, homedirectory from AD etc...
    
      #multiple values in $acl (who has access to this folder)
    
      $acl = (get-acl $user.HomeDirectory).access| Where-Object IdentityReference -notin $exclude -ErrorAction Stop).IdentityReference.value
    
     $props = {
    
         "Name" = $user.name;
         "HomeDir" = $user.HomeDirectory;
         "HomeDirACL" = $acl;
    
         }
    
      $foo  = New-Object PSCustomObject -Property $props
    
      $list += $foo
    }
    
    #after all foreach user in list
    $list | export-csv -path C:\temp\filename.csv -notypeinformation -encoding utf8

    Only way i can get this to show all the members of the acl is to to a change :

    "HomeDirACL" = $acl;

    to

    "HomeDirACL" = $acl -join ';'

    but its super ugly and requires 'massaging' of the report as it list all of them in a single line but all stretched out... 
    already tried playing with the -join option like "`r `n" but its just injects them as the join character...

    Monday, April 30, 2018 10:46 PM

Answers

  • You can also do it like this if you want everything in a single column:

    $list += [pscustomobject]@{
         Name = $user.name
         HomeDir = $user.HomeDirectory
         HomeDirACL = $acl -join '|'
    }
    


    \_(ツ)_/

    • Marked as answer by ShadAcker Tuesday, May 1, 2018 3:12 PM
    Monday, April 30, 2018 11:05 PM

All replies

  • You have to loop on the Get-Acl as it returns a list of entries.


    \_(ツ)_/

    Monday, April 30, 2018 11:02 PM
  • You can also do it like this if you want everything in a single column:

    $list += [pscustomobject]@{
         Name = $user.name
         HomeDir = $user.HomeDirectory
         HomeDirACL = $acl -join '|'
    }
    


    \_(ツ)_/

    • Marked as answer by ShadAcker Tuesday, May 1, 2018 3:12 PM
    Monday, April 30, 2018 11:05 PM