none
Appened to adjacent cell in CSV file RRS feed

  • คำถาม

  • HI,

    I'm not great with powershell but it's what I know best so I'm trying to use it to do the following:

    I need to extract data like below from AD:

    $users = (import-csv "$path\users.csv").users
    Foreach ($user in $users){

    $Dname = Get-ADUser $user -properties TelephoneNumber| select TelephoneNumber
    $Email =  Get-ADUser $user -properties UserPrincipalName | select UserPrincipalName
    }

    and append it to an adjacent cell in an existing csv file(i.e specify the column it writes to and only write on the same row as the matching username in users.csv)

    any assistance with this would be appreciated

    regards,

    Ian

    15 พฤศจิกายน 2562 9:58

ตอบทั้งหมด

  • It would be better if you took the time to learn basic PowerShell.

    $csvfile = "$path\users.csv"
    (import-csv $csvfile) |
        ForEach-Object{
            $user = Get-ADUser $_.user -properties TelephoneNumber,UserPrincipalName
            $_.TelephoneNumber = $user.TelephoneNumber
            $_.UserPrincipalName = $user.UserPrincipalName
        } |
        Export-Csv $csvfile
    


    \_(ツ)_/

    15 พฤศจิกายน 2562 10:09
    ผู้ดูแล
  • Hi Jrv,

    Thanks for your speedy reply.

    While admittedly my powershell skills are not fantastic (hence why I am here). I can see why you made your suggestion. The name of the file is deceptive and I should have provided more information. The file users.csv is a master file which contains about 50 columns and several hundred rows of data I can't afford to lose i need that to append to each column being.

    So you can see why export-csv is not an option.

    Regards,

    Ian

    16 พฤศจิกายน 2562 11:14
  • Hi Jrv,

    Thanks for your speedy reply.

    While admittedly my powershell skills are not fantastic (hence why I am here). I can see why you made your suggestion. The name of the file is deceptive and I should have provided more information. The file users.csv is a master file which contains about 50 columns and several hundred rows of data I can't afford to lose i need that to append to each column being.

    So you can see why export-csv is not an option.

    Regards,

    Ian

    Yes, you probably don't want to clobber your original file.  I did not read this in detail, but wouldn't simply using export-csv to a *new* file name be sufficient?

    • แก้ไขโดย Marco.Shaw 16 พฤศจิกายน 2562 13:20
    16 พฤศจิกายน 2562 13:20
  • Hi Jrv,

    This seems close to what I need but I need to tweek and test properly if it will line up with the correct user name in users.csv

    probably need a foreach-object (as you suggested)  to append all the data I want to put in from AD, i have another messy way I know will work but I don't even want to say it makes my skin crawl thinking about it.

    $csvfile = "$path\users.csv"
    $csvfile2 = "$path\users2.csv"
    
    Import-csv $csvfile | % { $name = $_.User; $_ | Add-Member 'Email' "$Email"; $_ } | export-csv $csvfile2 -Append -force -NoTypeInformation

    There are some minor issues but it's late and I'm too tired to think about it right now.

         1. I can't run more than once against he same $csvfile2. if i try i get the following error (even if i clear allvariables)
            export-csv : Cannot process argument because the value of argument "name" is not valid. Change the              value of the "name" argument and run the operation again.  
            #this means currently I have to delete the existing file for $csvfile2 or create a new file

         #2. not a big deal as I can fix headders manually but it would be nice to to have to and it's replacing some exisiting headders with "H" because it seams to think there is no header for some reason. is it possible to prevent it doing this?:
             # WARNING: One or more headers were not specified. Default names starting with "H"
             # have been used in place of any missing headers

        #i'll tweak/test tomorrow if it can do adjacent cells and come back to update

    Thanks again,

    Ian



    • แก้ไขโดย Ian089 16 พฤศจิกายน 2562 14:18
    16 พฤศจิกายน 2562 14:14
  • All you needed to do was to modify the "Export-CSV" in JRV's example. Add your $csvfile2 and -NoTypeInformation to it.

    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    16 พฤศจิกายน 2562 16:34
  • My code can be easily modified to not do what you aoriginally asked by following Rich's suggestion.

    import-csv $path\users.csv |
        ForEach-Object{
            $user = Get-ADUser $_.user -properties TelephoneNumber,UserPrincipalName
            $_.TelephoneNumber = $user.TelephoneNumber
            $_.UserPrincipalName = $user.UserPrincipalName
        } |
        Export-Csv csvfile2.csv
    

    You will also have to change the column names to ones that are in your actual CSV file.

    First take the time to learn basic PowerShell and learn what a CSV file is.  Without any knowledge of PowerShell or Windows/computer technology you are just asking for us to act as consultants and design and write a solution.  This is beyond the scope of a technical forum and not in accordance with the rules of this forum.


    \_(ツ)_/

    16 พฤศจิกายน 2562 20:35
    ผู้ดูแล
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    18 พฤศจิกายน 2562 6:50
    ผู้ดูแล
  • Hi,

    forgot to come back. I actually came up with a different solution. Haven't tested jrv's but it looks better than mine.

    24 พฤศจิกายน 2562 13:02
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee



    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    6 ธันวาคม 2562 7:35
    ผู้ดูแล