compare excel data with date & show other matched data in powershell script.

Question

Hi, I am trying to get the "date of birth" column in my excel sheet compared with system date & get the matches. The next step would be to get the associated datas(login names) in the same row, of the matched dates & display the same or dump the data to any file somewhere.

This is what I have now:

$link
= "http://sp-fin/sites/arindam-sites/Shared Documents" 
(
new-object -com WScript.Network).MapNetworkDrive("M:","$link") 
cd M:
$file
= Get-ChildItem *.* | Where-Object {$_.xlsx -eq $birthday.xlsx} 
Copy-Item
$file -Destination c:\birth.xlsx 
$b
= c:\birth.xlsx 
$d
= (Get-Date -format "M-d-yy") | Out-File -FilePath c:\auto\date.csv 
$excel
= new-object -comobject Excel.Application 
$excel
.visible = $True 
$workbook
= {$excel.Workbooks.Open('$b') 
}
$excel
.displayalerts = $False 
$excel
.quit()

After this I tried few options to compare the excel data with the .csv file data which is nothing but system data, but nothing worked out till now. Any helpn this would be great!

Regards Arindam

Answer 1

How about some sql as per http://www.leeholmes.com/blog/InteractingWithSQLDatabasesInPowerShellInvokeSqlCommand.aspx?

Your query might be:

Invoke-SqlCommand (Resolve-Path xls_test.xls) -Sql 'SELECT * FROM [Sheet1$] WHERE SomeDate=Date()'

SomeDate refers to the column header.

Answer 2

I have actually got the data, where Its able to recognise the user whose birthday matches todays system date & is throwing the login name. Code is like this:

$link = "http://sp-fin/sites/arindam-sites/Shared Documents" (new-object -com WScript.Network).MapNetworkDrive("L:","$link") cd L: $file = Get-ChildItem . | Where-Object {$_.xlsx -eq $birthday.xlsx} Copy-Item $file -Destination c:\birth.xlsx $excel = new-object -comobject Excel.Application $excel.visible=$true $excel.displayalerts = $False $wb=$excel.workbooks.open("M:\birth.xlsx ") $wb.Sheets.Select(1) $Sheet=$wb.ActiveSheet $date_text = Get-Date -Format d for($i=3; $i -lt 12; $i++){ if ( $($sheet.Cells.Item($i,5).Text) -eq $date_text ){ " Birthday today : $($sheet.Cells.Item($i,1).Text) " } } $wb.Close() $Excel.Quit()

Now lets say there is more tahn 10 users in the list. if I am suppose to get the list of all these users in a list in the form of array or hash table as a output data, that would be great. I am working on this, if I can get some help or hint, that would be great!

Regards Arindam