function AddItemProperties($item, $properties, $output) {
if ($item -ne $null) {
foreach ($property in $properties) {
$propertyHash = $property -as [hashtable]
if ($propertyHash -ne $null) {
$hashName = $propertyHash["name"] -as [string]
if ($hashName -eq $null) {
throw "there should be a string Name"
}
$expression = $propertyHash["expression"] -as [scriptblock]
if ($expression -eq $null) {
throw "there should be a ScriptBlock Expression"
}
$_ = $item
$expressionValue = & $expression
$output | add-member -MemberType "NoteProperty" -Name $hashName -Value $expressionValue
}
else {
# .psobject.Properties allows you to list the properties of any object, also known as "reflection"
foreach ($itemProperty in $item.psobject.Properties) {
if ($itemProperty.Name -like $property) {
$output | add-member -MemberType "NoteProperty" -Name $itemProperty.Name -Value $itemProperty.Value
}
}
}
}
}
}
function WriteJoinObjectOutput($leftItem, $rightItem, $leftProperties, $rightProperties, $Type) {
$output = new-object psobject
if ($Type -eq "AllInRight") {
# This mix of rightItem with LeftProperties and vice versa is due to
# the switch of Left and Right arguments for AllInRight
AddItemProperties $rightItem $leftProperties $output
AddItemProperties $leftItem $rightProperties $output
}
else {
AddItemProperties $leftItem $leftProperties $output
AddItemProperties $rightItem $rightProperties $output
}
$output
}
<#
.Synopsis
Joins two lists of objects
.DESCRIPTION
Joins two lists of objects
.EXAMPLE
Join-Object $a $b "Id" ("Name","Salary")
#>
function Join-Object {
[CmdletBinding()]
[OutputType([int])]
Param
(
# List to join with $Right
[Parameter(Mandatory = $true,
Position = 0)]
[object[]]
$Left,
# List to join with $Left
[Parameter(Mandatory = $true,
Position = 1)]
[object[]]
$Right,
# Condition in which an item in the left matches an item in the right
# typically something like: {$args[0].Id -eq $args[1].Id}
[Parameter(Mandatory = $true,
Position = 2)]
[scriptblock]
$Where,
# Properties from $Left we want in the output.
# Each property can:
# – Be a plain property name like "Name"
# – Contain wildcards like "*"
# – Be a hashtable like @{Name="Product Name";Expression={$_.Name}}. Name is the output property name
# and Expression is the property value. The same syntax is available in select-object and it is
# important for join-object because joined lists could have a property with the same name
[Parameter(Mandatory = $true,
Position = 3)]
[object[]]
$LeftProperties,
# Properties from $Right we want in the output.
# Like LeftProperties, each can be a plain name, wildcard or hashtable. See the LeftProperties comments.
[Parameter(Mandatory = $true,
Position = 4)]
[object[]]
$RightProperties,
# Type of join.
# AllInLeft will have all elements from Left at least once in the output, and might appear more than once
# if the where clause is true for more than one element in right, Left elements with matches in Right are
# preceded by elements with no matches. This is equivalent to an outer left join (or simply left join)
# SQL statement.
# AllInRight is similar to AllInLeft.
# OnlyIfInBoth will cause all elements from Left to be placed in the output, only if there is at least one
# match in Right. This is equivalent to a SQL inner join (or simply join) statement.
# AllInBoth will have all entries in right and left in the output. Specifically, it will have all entries
# in right with at least one match in left, followed by all entries in Right with no matches in left,
# followed by all entries in Left with no matches in Right.This is equivallent to a SQL full join.
[Parameter(Mandatory = $false,
Position = 5)]
[ValidateSet("AllInLeft", "OnlyIfInBoth", "AllInBoth", "AllInRight")]
[string]
$Type = "OnlyIfInBoth"
)
Begin {
# a list of the matches in right for each object in left
$leftMatchesInRight = new-object System.Collections.ArrayList
# the count for all matches
$rightMatchesCount = New-Object "object[]" $Right.Count
for ($i = 0; $i -lt $Right.Count; $i++) {
$rightMatchesCount[$i] = 0
}
}
Process {
if ($Type -eq "AllInRight") {
# for AllInRight we just switch Left and Right
$aux = $Left
$Left = $Right
$Right = $aux
}
# go over items in $Left and produce the list of matches
foreach ($leftItem in $Left) {
$leftItemMatchesInRight = new-object System.Collections.ArrayList
$null = $leftMatchesInRight.Add($leftItemMatchesInRight)
for ($i = 0; $i -lt $right.Count; $i++) {
$rightItem = $right[$i]
if ($Type -eq "AllInRight") {
# For AllInRight, we want $args[0] to refer to the left and $args[1] to refer to right,
# but since we switched left and right, we have to switch the where arguments
$whereLeft = $rightItem
$whereRight = $leftItem
}
else {
$whereLeft = $leftItem
$whereRight = $rightItem
}
if (Invoke-Command -ScriptBlock $where -ArgumentList $whereLeft, $whereRight) {
$null = $leftItemMatchesInRight.Add($rightItem)
$rightMatchesCount[$i]++
}
}
}
# go over the list of matches and produce output
for ($i = 0; $i -lt $left.Count; $i++) {
$leftItemMatchesInRight = $leftMatchesInRight[$i]
$leftItem = $left[$i]
if ($leftItemMatchesInRight.Count -eq 0) {
if ($Type -ne "OnlyIfInBoth") {
WriteJoinObjectOutput $leftItem $null $LeftProperties $RightProperties $Type
}
continue
}
foreach ($leftItemMatchInRight in $leftItemMatchesInRight) {
WriteJoinObjectOutput $leftItem $leftItemMatchInRight $LeftProperties $RightProperties $Type
}
}
}
End {
#produce final output for members of right with no matches for the AllInBoth option
if ($Type -eq "AllInBoth") {
for ($i = 0; $i -lt $right.Count; $i++) {
$rightMatchCount = $rightMatchesCount[$i]
if ($rightMatchCount -eq 0) {
$rightItem = $Right[$i]
WriteJoinObjectOutput $null $rightItem $LeftProperties $RightProperties $Type
}
}
}
}
}
$users = Import-Csv .\user.csv | ForEach-Object { $_.employeeID = "JP" + $_.employeeID; $_ }
$machines = Import-Csv .\machine.csv
Join-Object $users $machines -Where { $args[0].employeeID -eq $args[1].Name }`
-LeftProperties EmployeeName, City, Description -RightProperties Name, operatingSystem |
Select-Object Name, operatingSystem, EmployeeName, City, Description |
Export-Csv .\result.csv -NoClobber -NoTypeInformation -Encoding utf8
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2018/03/10 02:19
2018/03/10 12:52
2018/03/10 12:54
2018/03/10 13:14
2018/03/10 13:23
2018/03/10 13:24
2018/03/10 14:43