locked
Format my CSV file into columns using Powershell RRS feed

  • Question

  • First, I'm fairly new to Powershell.

    I have a script that I found and changed to get the results I need. Basically this runs through a CSV file and verifies if the user matches in AD. If it does, it fetches AD properties and stores them into the CSV by using "Add-Content". If it doesn't match, it notes "User doesn't exist" and adds this to the CSV file by using "Add-Content" as well. I use the "Add-Content" method because Export-csv doesn't append each of the users, only the last one that runs. Using "Add-Content" works nicely as it gives me the results and appends all of the data to the file, but it just dumps it all on the same line. Could someone point me in the right direction to help me format my data into columns?

    # defined parameters for input file and export file
    param ($inputfile='path-to-imported-csv',$logfile='path-to-exported-csv') 
    
    # Store imported CSV into variable
    $csv = Import-CSV $inputfile 
    
    # adds the text "Search results" to export CSV file
    "Search Result" | Add-Content $logfile 
    
    #For each row in column
    ForEach ($user in $csv) 
    
    {
    
    #Store account name in variable
    $ADName = $user.ADAccount
    
    
    #runs this query
    $search = Get-ADUser -filter { samaccountname -eq $ADName }
    
        IF ($search) # if search returns true
    
            {
    
               $search | Select name,samaccountname,givenname,surname,userprincipalname,enabled | Add-Content $logfile         
    
            }
    
        ELSE #If null, or doesn't exist
    
            {
    
            "$user does not exist" | Add-Content $logfile    
    
            }
    
            
    }

    Wednesday, March 28, 2018 2:45 PM

All replies

  • Get-Help Export-Csv -Online

    If you find that my post has answered your question, please mark it as the answer. If you find my post to be helpful in anyway, please click vote as helpful. (99,108,97,121,109,97,110,50,64,110,121,99,97,112,46,114,114,46,99,111,109|%{[char]$_})-join''

    Wednesday, March 28, 2018 3:13 PM
  • Export-CSV -Append

    You should always carefully review the complete help for the cmdlets you are about to use - including the examples.


    Best regards,

    (79,108,97,102|%{[char]$_})-join''

    Wednesday, March 28, 2018 4:22 PM
  • I see what I think needs to happen, which is add -Append to the export-csv. When I do this, I get "it doesn't have the property that corresponds to the following column: Length" which tells me that the values are not objects. I found an article that talks about this and the solution is to put the string values into new object properties (https://stackoverflow.com/questions/19450616/export-csv-exports-length-but-not-name). I see the possible solution here on this line here:

    Select-Object @{Name='Name';Expression={$_}}

    For this, would I need something like this for each of the properties?

    Select-Object @{name='name';Expression={$_}},@{name='samaccountname';Expression={$_}},etc

    Wednesday, March 28, 2018 4:33 PM
  • CSV files are made for uniform data. So every single "line" you export has to have the same properties. So you cannot use something like this:
    "Search Result" | Add-Content $logfile
    ## or this : 
    "$user does not exist" | Add-Content $logfile
    You could do it this way:
    param (
        $inputfile = 'path-to-imported-csv',
        $logfile = 'path-to-exported-csv'
    ) 
    $csv = Import-CSV $inputfile 
    ForEach ($user in $csv) {
        $ADName = $user.ADAccount
        $search = Get-ADUser -filter { samaccountname -eq $ADName }
            if ($search)  {
                $search | 
                    Select-Object -Property name,samaccountname,givenname,surname,userprincipalname,enabled | 
                        Export-Csv -Path $logfile  -Append
            }
            else {
                "$user does not exist"   
            }
    }




    Best regards,

    (79,108,97,102|%{[char]$_})-join''

    Wednesday, March 28, 2018 4:49 PM
  • Right, so the impression I'm getting is there's no way for me to get user information and null users in the same CSV? I was just trying find some way to get this all in run.
    Wednesday, March 28, 2018 8:56 PM
  • Right, so the impression I'm getting is there's no way for me to get user information and null users in the same CSV? I was just trying find some way to get this all in run.

    You can create a custom object and fill it in when found or leave it blank except for the name when not found.


    \_(ツ)_/

    Wednesday, March 28, 2018 9:09 PM
  • There's almost alway a way. Something like this could work:
    param (
        $inputfile = 'path-to-imported-csv',
        $logfile = 'path-to-exported-csv'
    ) 
    $csv = Import-CSV $inputfile 
    ForEach ($user in $csv) {
        $ADName = $user.ADAccount
        $search = Get-ADUser -filter { samaccountname -eq $ADName }
            if ($search)  {
                $search | 
                    Select-Object -Property name,samaccountname,givenname,surname,userprincipalname,enabled | 
                        Export-Csv -Path $logfile  -Append
            }
            else {
                [System.Management.Automation.PSCustomObject] @{
                    name = $user
                    samaccountname = 'n/a'
                    givenname = 'n/a'
                    surname = 'n/a'
                    userprincipalname = 'n/a'
                    enabled = 'n/a'
                } | Export-Csv -Path $logfile  -Append
            }
        }
    untested!!

    Best regards,

    (79,108,97,102|%{[char]$_})-join''

    Wednesday, March 28, 2018 9:13 PM
  • This might be more workable and easier to understand.

    Try it:

    param ( $inputfile = 'path-to-imported-csv', $logfile = 'path-to-exported-csv' ) Import-CSV $inputfile | ForEach-Object{ $ADName = $_.ADAccount $userinfo = [pscustomobject]@{ samaccountname = $ADName GivenName = $null Surname = $null UserPrincipalName = $null Enabled = $null } if($user = Get-ADUser -filter { samaccountname -eq $ADName} -Properties *){ $userinfo.GivenName = $user.GivenName $userinfo.Surname = $user.Surname $userinfo.UserPrincipalName = $user.UserPrincipalName $userinfo.Enabled = $user.Enabled }
    $userinfo # output the result object } | Export-Csv -Path $logfile -NoType



    \_(ツ)_/





    • Edited by jrv Thursday, March 29, 2018 12:03 AM
    Wednesday, March 28, 2018 10:28 PM
  • There is also another little used technique that can be useful in many cases:

    param (
        $inputfile = 'path-to-imported-csv',
        $logfile = 'path-to-exported-csv'
    )
    
    $props = 'SamAccountName','GivenName','Surname','UserPrincipalName','Enabled'
    Import-CSV $inputfile |
        ForEach-Object{
            $ADName = $_.ADAccount
            
            if($user = Get-ADUser -filter { samaccountname -eq $ADName} -properties $props){
                $user
            }else{
                '' | select @{n='SamAccountName';e={$ADName}}
            }
        } |
        select $props |
        Export-Csv -Path $logfile  -Append        


    \_(ツ)_/



    • Proposed as answer by BOfH-666 Thursday, March 29, 2018 12:01 AM
    • Edited by jrv Thursday, March 29, 2018 12:02 AM
    Wednesday, March 28, 2018 11:55 PM
  • Wow ...  really neat.

    Best regards,

    (79,108,97,102|%{[char]$_})-join''

    Thursday, March 29, 2018 12:00 AM
  • Since we want to remove all interim variables to improve supportability we can then do this:

    param (
        $inputfile = 'path-to-imported-csv',
        $logfile = 'path-to-exported-csv'
    )
    
    $props = 'SamAccountName','GivenName','Surname','UserPrincipalName','Enabled'
    
    Import-CSV $inputfile |
        ForEach-Object{
            if($user = Get-ADUser -filter "samaccountname -eq '$($_.ADAccount)'" -properties $props){
                $user
            }else{
                [pscustomobject]@{SamAccountName = $_.ADAccount}
            }
        } |
        select $props |
        Export-Csv -Path $logfile  -Append        
    


    \_(ツ)_/


    • Edited by jrv Thursday, March 29, 2018 12:06 AM
    Thursday, March 29, 2018 12:05 AM
  • Wow ...  really neat.

    Best regards,

    (79,108,97,102|%{[char]$_})-join''

    Yours, mine and all will work when tested and debugged. 

    Refactoring is a good exercise as it closes the code in on mathematical determinism.  Code and computers are assumed to be or to attempt to attain a mathematical deterministic state. Re-factoring helps us with this and also reduces complexity.

    See: https://en.wikipedia.org/wiki/Deterministic_system

    Also: https://en.wikipedia.org/wiki/Code_refactoring

    I know.  It's heady but worth being familiar with.  It can save time and pain in future projects.


    \_(ツ)_/

    Thursday, March 29, 2018 12:27 AM