help with parsing csv

This topic contains 4 replies, has 4 voices, and was last updated by Profile photo of Bob McCoy Bob McCoy 7 months, 2 weeks ago.

  • Author
    Posts
  • #37350
    Profile photo of taotao07
    taotao07
    Participant

    So i have this a csv file, similar to one below
    user,phonenumber,emailaddress
    user1,3219873452,user1@email.com
    user2,7651239876,user2@email.com
    user1,3211234321,user1a@email.com
    user2,7651233214,user2a@email.com
    I would like to be able to get an output for each user with both of their phone numbers and email addresses – just once. So a little story behind this, someone from my company would like to send a single email with the info that's in the csv file. I've tried foreach user but that's sending multiple emails. Is there a way to send just one message containing both email addresses and phone numbers?
    I don't need help with send-mailmessage, just getting this damn data in a single email (per user) is giving me a headache!
    Thanks in advance

  • #37351
    Profile photo of Richard Diphoorn
    Richard Diphoorn
    Participant

    What have you tried so far? Can you show some code you have tried?

  • #37361
    Profile photo of random commandline
    random commandline
    Participant

    Something like this should work for you.

    Import-Csv .\test.csv | Group-Object -Property user | 
    ForEach-Object {$_.group ; Send-MailMessage -Body $($_.group) -Subject $($_.name)}
    

    Below is a working example.

    $csvoutput = "user,phonenumber,emailaddress
    user1,3219873452,user1@email.com
    user2,7651239876,user2@email.com
    user1,3211234321,user1a@email.com
    user2,7651233214,user2a@email.com
    " -split "`n"
    
    $csvoutput | ConvertFrom-Csv | Group-Object -Property user | 
    ForEach-Object {$_.group}
    
  • #37995
    Profile photo of taotao07
    taotao07
    Participant

    random commandline, thank you, perhaps i didn't explain it in right.
    I'm looking for something that would look like this:

    user1 3219873452 user1@email.com
    3211234321 user1a@email.com
    user2 7651239876 user2@email.com
    7651233214 user2a@email.com
    Thanks!

  • #38054
    Profile photo of Bob McCoy
    Bob McCoy
    Participant
    # create empty output file (no leftovers from previous runs)
    $outFile = New-Item -ItemType File -Path . -Name test.txt -Force
    # read in the input list and create a group collection based on user
    $users = Import-Csv -Path .\test.csv | Group-Object -Property user
    foreach ($user in $users)
    {
        # first (default) entry per user
        $out = "$($user.Group[0].user) $($user.Group[0].phonenumber) $($user.Group[0].emailaddress)"
        # append additional shortened entries for the same user
        for ($i = 1; $i -le $user.Count; $i++)
        {
            $out = "$out`n$($user.Group[$i].phonenumber) $($user.Group[$i].emailaddress)".TrimEnd()
        }
        # update the output file
        Add-Content -Path $outFile -Value $out
    }

    If there will never be more than two entries then the for loop can be replaced with a simple 'if' statement and the index ($i) hardcoded to 1. But this version will work with any number of entries per user.

You must be logged in to reply to this topic.