2つのCSVファイルを比較して、合致する項目があれば別ファイルとして出力したい

Question

こんにちは。Powershellの質問です。宜しくお願いします。

以下のような2つのCSVファイルがあり、もし[user.csv]のemployeeIDがmachine.csvのNameに含まれていれば、
(今回は11111と22222)

[user.csv]
employeeID EmployeeName City Description
12345 田中 京都
11111 中村 福岡 広報
34567 須賀 東京
22222 古市 大阪 第2営業部

[machine.csv]
Name operatingSystem EmployeeName City Description
JP00000 Win7
JP11111 Mac
JP22222 Win8
JP33333 Win10

このように、別CSVとして出力し、machines.csvのName、operatingSystemコラムと
user.csvのEmployeeName、City、Descriptionコラムを合体させたいと思います。
[Merge.csv]
Name operatingSystem EmployeeName City Description
JP11111 Mac 中村 福岡 広報
JP22222 Win8 古市 大阪 第2営業部

自分で試してはみたものの、あまり詳しくない為全く出力できません。ぜひ、解決方法を教えて頂ければと思います。
どうぞよろしくお願いいたします。

$users = import-csv "./User.csv"
$machines = import-csv "./Machine.csv"

foreach ($line in $machines) {
if($machines.'Name' -match $users.'employeeID'){

   Write-output $($machines.'Name' + ":" + $machines.'operatingSystem' + ":" + $users.'EmployeeName' + ":" + $users.'City' + ":" + $users.'Description') | Out-file "./Merge.csv" -append

}

}

Answer 1

内部結合がしたいということですね。
SQL は使ったことがありますか？
マイクロソフトの PowerShell チームのブログに使い回しできる関数がありますのでこれを使ってみてください。

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

Comments

[SUSU0703]

Zuishin-san.

早速ありがとうございます。残念ながらSQLは使ったことがありません。試してみたのですが、難しくて上手くいきませんでしたした。。例えば、2つのCSVを1つに結合して、Name列にemployeeID列の値が含まれている場合、

[ketsugou1.csv]

行
1. Name operatingSystem employeeID EmployeeName City Description
2. 12345 田中 京都
3. 11111 中村 福岡 広報
4. 34567 須賀 東京
5. 22222 古市 大阪 第2営業部
6. JP00000 Win7
7. JP11111 Mac
8. JP22222 Win8
9. JP33333 Win10

合致したものを別ファイルで出力するにはどうすればいいでしょうか。
[ketsugou2.csv]
行
1. Name operatingSystem EmployeeName City Description
2. JP11111 Mac 中村 福岡 広報
3. JP22222 Win8 古市 大阪 第2営業部

たびたび恐れ入りますが宜しくお願い致します。

[SUSU0703]

すごい、出来てます！ ありがとうございます。

最後にもう一つだけお願いします。machine.csvのNameが、JPTOK00000、JPOSA11111、JPFUK22222、などJPの後の名前がランダムな場合はどのようにすればよいでしょうか。

どうぞよろしくお願いいたします。

[Zuishin]

意味がわかりません。
完全にランダムならどうしようもないでしょう。
法則があるならその法則に従ってください。

[SUSU0703]

ちょっと説明不足でした。
ランダムなのはJPの後の3文字の部分だけです。(この場合、TOK、OSA, FUK の部分)

単純に考えて
{ $_.employeeID = "JP*" + $_.employeeID; $_ }
で試したのですがやはり上手くいかず。ここの指定が上手く出来ればと思っています。

宜しくお願い致します。

[Zuishin]

それなら
-Where { $args[1].Name.EndsWith($args[0].employeeID) }
のようにしてみたらどうですか？
もちろん JP を足しているところは削除してください。

[Zuishin]

EndsWith は .NET の System.String のメソッドですから調べてください。

[SUSU0703]

バッチリできました！！ 本当にありがとうございます。助かりました。自力ではとても無理でした。。
感謝感謝です！！！