Importing CSV to get more info and append to that CSV

This topic contains 6 replies, has 2 voices, and was last updated by Profile photo of bosox912 bosox912 3 years, 10 months ago.

  • Author
    Posts
  • #8032
    Profile photo of bosox912
    bosox912
    Participant

    I get a csv export from our HR system and I need to append the DN for the user to the csv.

    I have figured out how to get the DN:

    Import-Csv '.\file.csv' | foreach {Get-QADUser -FirstName $_."First Name
    " -LastName $_."Last Name" | select DN }

    This exports the information on the screen.  I cannot figure out how to pipe it back to the CSV so the new information appears in the row with the other information.

    I have been struggling with this for a few weeks and cannot find an answer.

    Thank you

  • #8036
    Profile photo of Don Jones
    Don Jones
    Keymaster

    So, right now, all you're outputting to the pipeline is the DN, which comes from the output of Get-QADUser. To select additional information, you'd need to include those properties in your Select command. The Select command will not have access to the information from the original CSV – it will only have access to what is output by Get-QADUser.

    If you need your final CSV to include information from both the original CSV, and from Get-QADUser, then a one-liner is going to be an awkward way to do that. You would be better moving to a more script-based approach.

    Given that information, what's the direction, and your next question?

  • #8037
    Profile photo of bosox912
    bosox912
    Participant

    I have not worked with powershell scripts as of yet.  That does sound like the best way to go.  How would I combine the 2 in a script?

     

    Thank you

  • #8043
    Profile photo of Don Jones
    Don Jones
    Keymaster
    
    function Do-Whatever {
    
    $file = Import-CSV input.csv
    
    foreach ($line in $file) {
    
    $result = Get-QADUser  -FirstName $line.”First Name” -LastName $line.”Last Name”
    
    $out = @{'First Name'=$line."First Name";'Last Name'=$line."Last Name';'DN'=$result.DN
    
    Write-Output $out
    
    }
    
    }
    
    Do-Whatever | Export-CSV output.csv
    
    

     

    Something like that, approximately. The idea is to get your input data into a variable ($line) so that it can be re-used along with other data. To create the output, I used a hashtable construct, which is simplistic but should work for you.

  • #8062
    Profile photo of bosox912
    bosox912
    Participant

    I attempted to use the sample provided, but the output file is not as expected.  I am attaching the csv file.

    Here is the script code:

    function Do-Whatever {

    $file = Import-CSV adtest.csv

    foreach ($line in $file) {

    $result = Get-QADUser  -FirstName $line.”First Name” -LastName $line.”Last Name”

    $out = @{‘First Name’=$line.”First Name”;’Last Name’=$line.”Last Name";'DN’=$result.DN}

    Write-Output $out

    }

    }

    Do-Whatever | Export-CSV output.csv -notype

     

  • #8064
    Profile photo of Don Jones
    Don Jones
    Keymaster

    The hashtable isn't working, is all.

    Just before Write-Output, try adding:

    $obj = new-object -Type PSObject -Prop $out

    And change the Write-Output line to:

    Write-Output $obj

    .

  • #8065
    Profile photo of bosox912
    bosox912
    Participant

    That seems to work!  Thank you for your help.

You must be logged in to reply to this topic.