Powershell script to read excel datewise and print max column value

Topics: Developer Forum, User Forum
Feb 4, 2014 at 5:36 AM
Hi Guys,

I need some help in regards to reading, filtering rows from an excel based on column wise. Here is my requirement, can some on help on coding this. I am a dumb in Powershell. However I managed to read data fromexcel and print what I want.
  1. I'm collecting some data and storing the output in an excel file in below format.
    2/4/2014 11:01:00 1.6 23
    2/4/2014 11:02:00 1.5 33
    2/4/2014 11:03:00 1.9 55
and so on
  1. My requirement is to
    (a) Filter rows based on yesterday's Timestamp
    (b) from the rows extracted i have to read column2, column 3
    (c) find maximum value in both the col2 and col3 and send them through an email with HTML formatting
Output should be like this through email and in HTML format in table.

Firstcol Max1 Max2
2/3/2014 1.8 68
(yesterday's date) (Max value in col2) (Max value in col3)

If it was unix/linux scripting this would have been easier using grep and calculating max value
Feb 5, 2014 at 12:56 AM
Edited Feb 5, 2014 at 12:56 AM
Well, I think putting the data in Excel makes things harder -- especially for your linux grep ;-)

If you simply save it in .csv format, then you'll have a much easier job, using the Import-CSV cmdlet and specifying the header parameter. Taking a file like this, for instance:
2/4/2014 11:01:00,1.6,23
2/4/2014 11:02:00,1.5,33
2/4/2014 11:03:00,1.9,55
You can just do something like this:
$Yesterday = (Get-Date).AddDays(-1).Date

$YesterdayData = Import-CSV Data.csv -Header Date1, Val1, Val2 | 
       Select @{ Name="Date"; Expression={[DateTime]$_.Date}}, Val1, Val2 | 
       Where-Object { $_.Date.Date -eq $Yesterday }

$Max1 = $YesterdayData | Sort Val1 -Desc | Select -First 1
$Max2 = $YesterdayData | Sort Val2 -Desc | Select -First 1

$HTML = $Max1, $Max2 | ConvertTo-Html -Fragment

Send-MailMessage -Body $Html -BodyAsHtml # And the rest of your parameters ...