none
read csv files and produce email with attachment RRS feed

  • Question

  •  I have csv  files being created that have a header record than row 2 has corresponding data.

    \\servera\csv\*.csv

     shippernbr|email|emaillCC|Returnemail|returnemailcc
    "091",me@hotmail.com,mecc@hotmail.com,mertn@hotmail.com,mertncc@hotmail.com

     I need to format that data into an email then after reading shippernbr value(091) go find that file in
    \\servera\pdf directory and send that pdf as an attachment to the email. When completed(email sent) move the csv and pdf to an
    archive directory.  I'm using PSv2 and would like to log all events to keep a audit trail of what gets sent.

     Thanks.

    Friday, June 14, 2013 1:59 AM

Answers

  • Try this:

    Function Write-Log($LogText){ $LogEntry=@" $((Get-Date).ToString("dd-MM-yyyy HH:mm:ss")) > $($LogText) "@ $LogEntry $LogEntry | Add-Content 'c:\temp\testlog.txt' }

    $rootfolder='C:\Temp'

    $files=Get-ChildItem "$rootfolder\*.csv" ForEach($file in $files){ Write-Log "Found file $file" Import-CSV $file | ForEach-Object{ $attachment="$rootfolder\pdf\$($_.shippernbr).pdf" Write-Log "Attachment is $($attachment)" $email=$_.email Write-Log "Email address is $($email)" $emailCC=$_.emaillCC Write-Log "Email CC address is $($emailCC)" Send-MailMessage -from "me@mydomain.com" -To $email -Cc $emailCC -Subject 'Subject' -SmtpServer 'myserver' -ErrorAction Continue if($?){ Write-Log "Email Sent Successfully" Move-Item $file -Destination "$rootfolder\archive\" -WhatIf Write-Log "Moved file $($file) to $rootfolder\archive\" Move-Item $attachment -Destination "$rootfolder\archive\" -WhatIf Write-Log "Moved file $($attachment) to $rootfolder\archive\" }else{ Write-Log "Email Send failed: $($error[0])";break } } }

    Fill in your values for the variables, sender and SMTP server of course.

    Remove -whatif from the move-item commands to actually execute the move-item commands.

    The logging function outputs to file & screen, you can comment out the screen output.

    Yes Mike, this is a write it for me ;-)


    Inspired by Carlsberg.


    Friday, June 14, 2013 2:45 AM
  • Yep, that clears it up.

    Use the first script I posted and make this change:

    Import-CSV $file -delimiter '|' | ForEach-Object{....


    Inspired by Carlsberg.

    • Marked as answer by hart60 Saturday, June 15, 2013 2:32 AM
    Saturday, June 15, 2013 2:14 AM

All replies

  • Try this:

    Function Write-Log($LogText){ $LogEntry=@" $((Get-Date).ToString("dd-MM-yyyy HH:mm:ss")) > $($LogText) "@ $LogEntry $LogEntry | Add-Content 'c:\temp\testlog.txt' }

    $rootfolder='C:\Temp'

    $files=Get-ChildItem "$rootfolder\*.csv" ForEach($file in $files){ Write-Log "Found file $file" Import-CSV $file | ForEach-Object{ $attachment="$rootfolder\pdf\$($_.shippernbr).pdf" Write-Log "Attachment is $($attachment)" $email=$_.email Write-Log "Email address is $($email)" $emailCC=$_.emaillCC Write-Log "Email CC address is $($emailCC)" Send-MailMessage -from "me@mydomain.com" -To $email -Cc $emailCC -Subject 'Subject' -SmtpServer 'myserver' -ErrorAction Continue if($?){ Write-Log "Email Sent Successfully" Move-Item $file -Destination "$rootfolder\archive\" -WhatIf Write-Log "Moved file $($file) to $rootfolder\archive\" Move-Item $attachment -Destination "$rootfolder\archive\" -WhatIf Write-Log "Moved file $($attachment) to $rootfolder\archive\" }else{ Write-Log "Email Send failed: $($error[0])";break } } }

    Fill in your values for the variables, sender and SMTP server of course.

    Remove -whatif from the move-item commands to actually execute the move-item commands.

    The logging function outputs to file & screen, you can comment out the screen output.

    Yes Mike, this is a write it for me ;-)


    Inspired by Carlsberg.


    Friday, June 14, 2013 2:45 AM
  • Yes Mike, this is a write it for me ;-)



    I usually like to get them to admit it before handing the script over. As usual, I've picked up something new from your scripts. $? is going to serve me well, thanks for that.
    Friday, June 14, 2013 2:56 AM
    Moderator
  • May be you will guilt him into it?

    The OP may gain some knowledge from studying the script and altering it to suit their exact needs... and pass the gained knowledge on to someone else?

    Yes, $? can be more useful than try-catch sometimes.


    Inspired by Carlsberg.

    Friday, June 14, 2013 3:02 AM
  •  

     Thanks for the script. I have done some simple import-csv for other projects, but haven't done anything that creates
    and email with an attachment using a csv file as input.

     What book or online DOC would you recommend PS development?

     
      Thanks again, very much appreciated !!!

    Friday, June 14, 2013 1:08 PM
  • Hi,

    Here's a great resource for getting started and it has several book options listed as well:

    http://technet.microsoft.com/en-us/scriptcenter/dd793612.aspx

    You can also browse around in the script repository and look at working example scripts:

    http://gallery.technet.microsoft.com/scriptcenter

    Lastly, here's a link to my favorite command reference site (usually has good examples):

    http://ss64.com/ps/

    Friday, June 14, 2013 1:28 PM
    Moderator
  •  



      When I ran script it can't find the pdf for attachment.

    $attachment="$rootfolder\pdf\$($_.shippernbr).pdf"

      The first line in csv has the header record(labels for columns) and line 2 has the data. All the data values are enclosed in Double Quotes. So in this
    case shippernbr would be "DT1678".

      This is the output from log file.
        Attachment is C:\Test\pdf\.pdf

       Thanks.

    Friday, June 14, 2013 2:40 PM
  • If your data is actually as you describe in your first post:

    shippernbr|email|emaillCC|Returnemail|returnemailcc
    "091",me@hotmail.com,mecc@hotmail.com,mertn@hotmail.com,mertncc@hotmail.com

    then it is not csv data and the script will not work - it needs an actual csv file for input.

    Please post the real content of one of your files.

    Edit:

    Working with the data you posted, this modified code may work:

    $temp=@'
    shippernbr|email|emaillCC|Returnemail|returnemailcc
    "091",me@hotmail.com,mecc@hotmail.com,mertn@hotmail.com,mertncc@hotmail.com
    '@
    
    $temp | Set-Content 'c:\temp\test.csv'
    
    
    Function Write-Log($LogText){
    $LogEntry=@"
    $((Get-Date).ToString("dd-MM-yyyy HH:mm:ss")) > $($LogText)
    "@
    $LogEntry
    #$LogEntry | Add-Content 'c:\temp\testlog.txt'
    }
    
    
    $rootfolder='C:\Temp'
    
    
    $files=Get-ChildItem "$rootfolder\*.csv"
    
    ForEach($file in $files){
        Write-Log "Found file $file"
        $mydata=(Get-Content $file)[1]  -replace '"','' -split ','
        $attachment="$rootfolder\pdf\$($mydata[0]).pdf"
        Write-Log "Attachment is $($attachment)"
        $email=$mydata[1]
        Write-Log "Email address is $($email)"
        $emailCC=$mydata[2]
        Write-Log "Email CC address is $($emailCC)"
        #Send-MailMessage -from "me@mydomain.com" -To $email -Cc $emailCC -Subject 'Subject' -SmtpServer 'myserver' -ErrorAction Continue
        if($?){
            Write-Log "Email Sent Successfully"
            Move-Item $file -Destination "$rootfolder\archive\" -WhatIf
            Write-Log "Moved file $($file) to $rootfolder\archive\"
            Move-Item $attachment -Destination "$rootfolder\archive\" -WhatIf
            Write-Log "Moved file $($attachment) to $rootfolder\archive\"
        }else{
            Write-Log "Email Send failed: $($error[0])";break
        }
    }
    
    


    Inspired by Carlsberg.


    • Edited by RiffyRiot Friday, June 14, 2013 10:33 PM New code
    Friday, June 14, 2013 10:15 PM
  •  

     The files are saved as .csv but the delimiter used is a |(bar) to separate header fields, and to separate the data fields. The shippernbr field is key to lookup in the pdf folder a file for attachment called 091.pdf or 45667.pdf Each file on the rootfolder has 2 records per file.  When I original copied  data for example I used wrong delimiter for data records.

    shippernbr|email|emaillCC|Returnemail|returnemailcc
    "091"|me@hotmail.com|mecc@hotmail.com|mertn@hotmail.com|mertncc@hotmail.com
    shippernbr|email|emaillCC|Returnemail|returnemailcc
    "45667"|me@hotmail.com|mecc@hotmail.com|mertn@hotmail.com|mertncc@hotmail.com

      Does this help?

      Thanks again.


    Saturday, June 15, 2013 2:10 AM
  • Yep, that clears it up.

    Use the first script I posted and make this change:

    Import-CSV $file -delimiter '|' | ForEach-Object{....


    Inspired by Carlsberg.

    • Marked as answer by hart60 Saturday, June 15, 2013 2:32 AM
    Saturday, June 15, 2013 2:14 AM
  •   Very Cool !!!!!

     That worked....

     Thanks again.

    Saturday, June 15, 2013 2:32 AM