Filter csv file by array of hashtables

Go To StackoverFlow.com

1

What I am trying todo is filter a csv file (happens to be a weblog) with an array of hashtables (user information from a database).

#$data is from a database. (about 500 items) Type: System.Data.DataTable
$users = @()
foreach($row in $data)
{
    $userItem = @{
        LoginId = $row[0]
        LastName = $row[3]
        FirstName = $row[4]
        LastAccess = $null
    }
    $users += $userItem
}
#Log files are about 14,000 lines long
$logfiles = Get-ChildItem $logFolder -Recurse | where {$_.Extension -eq ".log"} | Sort-Object BaseName -Descending
foreach($log in $logfiles)
{
    $csvLog = Import-Csv $log.FullName -Header ("Blank","LoginId","Date")
    $u = $users | Select {&_.LoginId}
    $filteredcsvLog = $cvsLog | Where-Object { $u -contains $_.LoginId}
    #This returns null
    ....

}

This does not seem to work, what am I missing. My guess is that I need to flatten the array into [string[]], however I can't seem todo that either.

2012-04-04 16:33
by chris
The first things that I notice: Is $data a CSV? I don't see anywhere that you actually split $row into an array - EBGreen 2012-04-04 16:39
@Data is from a function that returns System.Data.DataTable, I'm looping through them just to pull off the relevant data and place it in a HashTabl - chris 2012-04-04 16:44


1

Rather than do an array of hashtables, I would do a hashtable of custom objects e.g.:

$users = @{}
foreach($row in $data)
{
    $userItem = new-object psobject -property @{
        LoginId = $row[0]
        LastName = $row[3]
        FirstName = $row[4]
        LastAccess = $null
    }
    $users[$userItem.LoginId] = $userItem
}

Then the filtering is easier and faster:

foreach($log in $logfiles)
{
    $csvLog = Import-Csv $log.FullName -Header ("Blank","LoginId","Date")
    $filteredcsvLog = $cvsLog | Where-Object { $users[$_.LoginId} }
    ....
}
2012-04-04 16:49
by Keith Hill
That worked and I like the solution better than mine, but one question why didn't the way I was originally doing it work? what part of my Where-Object statements were wrong - chris 2012-04-04 16:56
Not sure if this was a typo but Select {&_.LoginId} should be Select {$_.LoginId}. That aside, but it still probably wouldn't work. Stick a call to Get-Member after the Select cmdlet $users | Select {$_.LoginId} | Get-Member. On V3 beta, I get a hashtable out with a property added to it ($_.LoginId). On v2, I think you get a PSCustomObject. What you original approach should have been is $u = $users | Foreach {$_.LoginId}. This extracts just the LoginId value - Keith Hill 2012-04-04 17:17
Thanks for expanding my knowledge - chris 2012-04-04 19:46
Ads