回答編集履歴

追記

2018/03/10 06:06

投稿

Zuishin

スコア28660

test CHANGED Viewed

@@ -7,3 +7,423 @@
 [Join-Object](https://blogs.msdn.microsoft.com/powershell/2012/07/13/join-object/)
+###追記
+カレントディレクトリに user.csv と machine.csv があるときに result.csv に書き込みます。
+result.csv が既にあるとエラーになります。
+```
+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
+```