Slice csv based on values of a column and send the attachment by mail

PowerShell Help
1

I have a huge csv file with multiple columns, I would like to use PowerShell to slice the csv into multiple csv files based on name of the column name ID and send to the respective email address corresponding to that ID. The email should contain a subject and body. A sample csv looks like this:qTKUh

Till now I could come up below query in PowerShell:

$some=Import-Csv .\filename.csv -Delimiter ';' |              
Group-Object -Property ID    
# way to select unique values in the column                                                                                
   foreach ($i in $some){
   $email = $i.Group.Email | Select -First 1 #to select first email address from csv
    $i.Group|
    Export-Csv -Delimiter ';'-Encoding UTF8 -NoTypeInformation -Force -Path ("C:\TEMP\"+$i.Name+".csv") 
    #iterated and saved by Force with respect to the name. It works this way and saved to my C drive Temp folder.
        if (Test-Path "C:\TEMP\$($i.Name).csv") {
            Write-Output "File $($i.Name) found: $email." 
             } else {
                 Write-Output "ERROR: File not found."  #for debugging
             } 
             break
             }
 $emailFrom = "xyz@gmail.com"  #email from address
 $smtpServer = "servername"
 $smtpPort = 25  
        
 Send-MailMessage -SmtpServer $smtpServer -Port $smtpPort -From $emailFrom -Body "Test $email" -Subject "File attached" -To $email -Attachments .\$i.Name.csv

Now the script does slice the csv based on the ID column and saves it as for example, 111.csv, 112.csv etc. in C drive Temp folder. I would like to make it work so that it saves the mail information as well and sends the mail to the corresponding email addresses for example, abc1@gmail.com,abc2@gmail.com etc.

Please help with resolving the script so it does that tasks explained above.

2

If I got it right you actually already have everything you need. You just have to bring it to the right order. :wink:

$CSV = Import-Csv -Path .\filename.csv -Delimiter ';'
$some = $CSV | Group-Object -Property ID

foreach ($i in $some) {
    $i.Group |
    Export-Csv -Delimiter ';'-Encoding UTF8 -NoTypeInformation -Force -Path ('C:\TEMP\' + $i.Name + '.csv') 

    if (Test-Path "C:\TEMP\$($i.Name).csv") {

        $SendMailMessageProps = @{
            SmtpServer  = 'servername'
            Port        = 25
            From        = 'xyz@gmail.com'
            Body        = 'Sliced CSV file'
            Subject     = 'File attached'
            To          = $i.Group.Email | Select-Object -First 1
            Attachments = .\$i.Name.csv
        }
        Send-MailMessage @SendMailMessageProps
    }
}

It would have been better to post some sample data formatted as code as well instead of an image. That would make it possible to copy the sample data for testing and debugging. :wink:

1 Like
3

many thanks for the script. Actually the issue is with $i.Group.Email | Select-Object -First 1 part, it only selects the first email address. What needs to be done is the email should be sent to all the IDs separately with the attachment .$i.Name.csv.
Like is there a way I can create a variable which selects the corresponding email addresses of the ID and sends email to that address?

4

Did you try my code suggestion? I’m not completely sure what you actually want to achieve. You may share one or two examples.

At the moment the code cuts out the first 2 rows with the ID 111 and sends it to the according email address abc1@gmail.com. Then the next row with the ID 112 and the mail to abc2@gmail.com. … and so on.

1 Like
5

Thanks alot the script works. So basically $i.Group.Email | Select-Object -First 1 plays the trick and selects the various email addresses for every unique IDs?

6

Well … if I got it right … yes. But you already had this in your code. That’s why I was unsure about your actual problem. :wink:

1 Like
7

thanks for prompt response, actually that only selects 1st member from the Object $i.Group.Email, I wanted for every .$i.Name.csv which is named after every unique ID from the main csv file filtered based on the distinct IDs. Now that $i.Name.csv should be send to corresponding email address in the next column. Thats what i wanted to do.
So I am not completely sure just about $i.Group.Email | Select-Object -First 1.

8

At this time in the code the objects are already grouped. So it selects the first email address from many email addresses which are ALL THE SAME. You could pick a random one or the last one as well as there are all the same in the group. At least in the sample data you posted.
The ID “111” comes with the email address “abc1@gmail.com”, the ID “112” comes with the email address “abc2@gmail.com”, the ID “113” comes with the email address “abc3@gmail.com” and so on and so on. If there are several rows with the ID “113” they come ALL with the email address “abc3@gmail.com”. So it doesn’t make any sense to send the email more than once because “abc3@gmail.com” would get the same email several times. Do you see what I mean?

If that’s not what you want PLEASE PLEASE PLEASE share one or two examples of what exactly you want to do. Do not describe it - show it!!

If you want to send the email to all email adresses contained in the CSV you could send the complete CSV to all email addresses. It wouldn’t make sense to cut it into pieces.

1 Like
9

Thanks alot for the detailed answer and explanation. Thats exactly I was looking for.
And sorry because of restrictions on the forum page, I could not share the csv. But It should work now, appreciate it :slight_smile:

10

Cool. Great that it helped.

You don’t need to share the complete CSV. But next time you could share a few lines of the CSV file formatted as code. That can be copied to do tests with it. Of course you should sanitize all sensitive information first. :wink:

11

hi, can please solve this prob

Powershell scripting

  1. Create a csv file with local PC information like ram, rom, drives etc details
  2. Send the above generated file over email
12

Manjunath2019,
Welcome to the forum. :wave:t4:

Please do not hijack other peoples posts. Especially when your question is not related to the post you’ve replied to. :point_up_2:t4:

Instead you should create an own new post and ask what you need to ask. If needed you can link an older thread in your question. :+1:t4:

Thanks in advance.