CODEWITHSUNDEEP
powershellcsv
user5865015
user5865015

Reputation: 41

Comparing dates of a CSV column in PowerShell

I have a CSV file spreadsheet (converted from an Excel xlsx) with around 21 columns and 74,000 rows. The four columns of interest to me are columns having to do with an employees start date, a termination date, a department name, and a vice president they report to.

I am trying to write a script that will return all employees whom have reached their start date, have not been terminated, work in a department that contains 'HR' in the name, and report to a specific VP. I will elaborate on my specific issues after the block of code.

$Lawson = Import-Csv .\Documents\Lawson_HR.csv 

$startDate = $Lawson | where [datetime]::ParseExact($_.'LAW HIRE DATE', 'dd-MM-yyyy', $null) -le (Get-Date)
$endDate = $startDate | where {$_.'LAW TERM DATE' -eq ''} 
$HR = $endDate | where {$_.'LAW DEPT NAME' -match 'HR'}
$VP = $endDate | where {$_.'VICE PRESIDENT' -match 'Croner'}

First, the $startDate variable does not work, I am unsure of the syntax needed to compare a given date (from the CSV) to today's date. (The $endDate variable functions as it should, but I was told that the method used is unreliable.)

Also, I would like to search the Dept Name column in each row for any instance of the letters 'HR' (note: dept names could be things like 'HR - Career Services' or 'HR - Diversity'. I want all rows that have 'HR' anywhere in the Dept Name field). I get the feeling the -match operator is not the way to do that, but I'm not certain.

Similarly, I would like for the $VP variable to return all items in which the Vice President column has a given name (in this case, Croner).

Upvotes: 0

Views: 676

Answers (1)

Mark Wragg
Mark Wragg

Reputation: 23355

This line needs curly braces { } but looks otherwise OK to me:

$startDate = $Lawson | where { [datetime]::ParseExact($_.'LAW HIRE DATE', 'dd-MM-yyyy', $null) -le (Get-Date) }

To do a simple partial match you're better off using -Like and a wildcard character as -Match uses regex (although should work).

Also I just noticed you were piping the $enddate variable not $lawson:

$HR = $Lawson | where {$_.'LAW DEPT NAME' -like '*HR*'}

If you're trying to do all of these criteria together, just combine them with -and:

$Lawson | where { [datetime]::ParseExact($_.'LAW HIRE DATE', 'dd-MM-yyyy', $null) -le (Get-Date) -and $_.'LAW TERM DATE' -eq '' -and $_.'LAW DEPT NAME' -like '*HR*' -and $_.'VICE PRESIDENT' -match 'Croner'}

Upvotes: 1

Related Questions