FlexFiend FlexFiend - 1 year ago 62
PowerShell Question

Where-Object with complex evaluation

I have a PowerShell script where I read in a CSV file, and if the date in a certain column is greater than a parameter date, I output that row to a new file.

As of now, I read the CSV file and then pipe to a

where if the row "passes" I store it in an Arraylist. Then when all the rows are processed, I output the Arraylist to an output CSV file. My starting CSV file is 225MB with over a quarter million rows, meaning that this process is slow.

Is there a way I can add a filter function to my piping so that only the passing rows are passed to the output CSV in one fell swoop? The current
just uses things like
... and not more complex forms of evaluation.

For best practices, I've got my code below:

Import-Csv -Delimiter "`t" -Header $headerCounter -Path $filePath |
Select-Object -Skip(1) |
ForEach-Object {
#Skip the header
if( $lineCounter -eq 1)

$newDate = if ([string]::IsNullOrEmpty($_.1) -eq $true)
{ [DateTime]::MinValue }
else { [datetime]::ParseExact($_.1,”yyyyMMdd”,$null) }

$updateDate = if ([string]::IsNullOrEmpty($_.2) -eq $true)
{ [DateTime]::MinValue }
else { [datetime]::ParseExact($_.2,”yyyyMMdd”,$null) }

$distanceDate = (Get-Date).AddDays($daysBack * -1)

if( $newDate -gt $distanceDate -or $updateDate -gt $distanceDate )
$filteredArrayList |
ConvertTo-Csv -Delimiter "`t" -NoTypeInformation |
select -Skip 1 |
% { $_ -replace '"', ""} |
out-file $ouputFile -fo -en unicode -Append

Answer Source

I've added ConvertToDate as a function to stop that confusing the Where block.

DistanceDate is out because it appears to be calculated only once.

ExportCsv is a little function that writes pipeline input to a file.

I haven't tested it, so bugs are quite likely unless I got lucky.

function ConvertToDate {

    if ($DateString -eq '') {
        return [DateTime]::MinValue
    } else {
        return [DateTime]::ParseExact($DateString, ”yyyyMMdd”, $null)

filter ExportCsv {
        [Parameter(Position = 1)]

    $csv = $_ | ConvertTo-Csv -Delimiter "`t" | Select-Object -Last 1
    $csv -replace '"' | Out-File $Path -Append -Encoding Unicode -Force

$distanceDate = (Get-Date).AddDays($daysBack * -1)

Import-Csv -Delimiter "`t" -Header $headerCounter -Path $filePath |
    Select-Object  -Skip 1  |
    Where-Object { (ConvertToDate $_.1) -gt $distanceDate -or (ConvertToDate $_.2) -gt $distanceDate } |
    ExportCsv $OutputFile