none
Powershell reade csv sent email. RRS feed

  • Question

  • Hello,

    I used powershell to import csv file. "Import-Csv .\sometest.csv -Delimiter "|" | Where-Object {$_.123456 -eq "20"}"

    I need a sum up all entry in "-eq" field and send email with sum up result.

    To send email  i used:

    $OutputPth = "C:\

    $OutputFile = "name"

    $SmtpServer = "server"

    $From = "from"

    $CC = "filename with email address"

    I dont know how to smu up field and how to create email with result.

    And one more thing there is some way to read only begin of header for ex first 3 leters?

    example header: 

    10|V01.00|20130812134030752

    I ask You about this becouse when i use this header " Import-Csv .\sometest.csv | select 10|V01.00|20130812134030752" powershell return error

    At line:1 char:11
    + 10|V01.00|20130812134030752
    +           ~~~~~~~~~~~~~~~~~
    Expressions are only allowed as the first element of a pipeline.
        + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
        + FullyQualifiedErrorId : ExpressionsMustBeFirstInPipeline

    Please help to resolve this problem.

    Below entry from Csv file with amended header.

    123456
    20|5465464|3215664
    30|656876k|385ghi
    20|653fydjfyj
    50|dfgbh656sdfg|sdfgsd5dsfg|2334234

    Monday, August 26, 2013 12:56 PM

Answers

  • Hmm, if you only want to know how many rows have 20 in the 123456 field, that is even easier.
    If you need to send this info via email, check out http://technet.microsoft.com/en-us/library/hh849925.aspx

    It might look something like this:

    $Body = (import-csv .\notes.csv -Delimiter "|" | Where-Object {$_.123456 -eq "20"}).count

    send-mailmessage -from "u1@ex.com" -to u2@ex.com -subject "Row count for file" -body "The row count for this file is $($body)"-smtpServer smtp.servername

    Hope that helps!

    D

    • Marked as answer by cichy201 Thursday, August 29, 2013 12:32 PM
    Wednesday, August 28, 2013 12:44 PM
  • Hi,

    the below should work also for those scenarios (just converting it to an array):

    $Body = @(import-csv .\notes.csv -Delimiter "|" | Where-Object {$_.123456 -eq "20"}).count
     
    send-mailmessage -from "u1@ex.com" -to u2@ex.com -subject "Row count for file" -body "The row count for this file is $($body)"-smtpServer smtp.servername
    

    • Marked as answer by cichy201 Thursday, August 29, 2013 12:32 PM
    Thursday, August 29, 2013 10:04 AM

All replies

  • Let me summarize my understanding of what you are asking:
    Import a CSV file that contains rows of data
    For each row where Column x = 20, add Column Y + Z, but only the first 3 characters from each field.

    Is that correct?
    To accomplish that, you could do:

    $Results = @()
    ForEach ($Line in (Import-Csv .\mycsv.csv -Delimiter "|" | Where-Object {$_.COLX -eq "20"})){$Results += [int]$Line.COLY.Substring(0,3) + [int]$Line.COLZ.Substring(0,3)}
    $Results

    CSV File Configuration
    COLX|COLY|COLZ
    20|87654|3456gd
    20|234654|97833456
    30|987sdfg786

    If you don't have a header in the CSV, you can add one during the import (-header).

    D

    Monday, August 26, 2013 5:55 PM
  • Hello,

    Thank You for reply. 

    I need  information only about how many "20" i have in csv. If csv file contains two "20" result =2, and this info i need to send by email.

    I cannot modify csv file.

    (-header) Thanls i forgot this :)

    BR/Lukas

    Tuesday, August 27, 2013 8:10 AM
  • Hmm, if you only want to know how many rows have 20 in the 123456 field, that is even easier.
    If you need to send this info via email, check out http://technet.microsoft.com/en-us/library/hh849925.aspx

    It might look something like this:

    $Body = (import-csv .\notes.csv -Delimiter "|" | Where-Object {$_.123456 -eq "20"}).count

    send-mailmessage -from "u1@ex.com" -to u2@ex.com -subject "Row count for file" -body "The row count for this file is $($body)"-smtpServer smtp.servername

    Hope that helps!

    D

    • Marked as answer by cichy201 Thursday, August 29, 2013 12:32 PM
    Wednesday, August 28, 2013 12:44 PM
  • Hi,

    Great it's work, but if  in file i have one entry i dont have any result. Mayby it's a bug?

    BR/Lukas

    Thursday, August 29, 2013 9:26 AM
  • Hi,

    the below should work also for those scenarios (just converting it to an array):

    $Body = @(import-csv .\notes.csv -Delimiter "|" | Where-Object {$_.123456 -eq "20"}).count
     
    send-mailmessage -from "u1@ex.com" -to u2@ex.com -subject "Row count for file" -body "The row count for this file is $($body)"-smtpServer smtp.servername
    

    • Marked as answer by cichy201 Thursday, August 29, 2013 12:32 PM
    Thursday, August 29, 2013 10:04 AM
  • Hi,

    Thanks. Tell Me how can i import-csv multiple file from folder?

    BR/Lukasz

    Thursday, August 29, 2013 11:19 AM
  • Hi,

    you should indicate whether your intitial question has been answered and post this as a separate question.

    Thursday, August 29, 2013 12:29 PM