我需要检查第一个csv文件中的列值是否存在于其他三个csv中的任何一个中,并按优先级将值返回到新的csv文件
因此,如果会话VPNct.csv文件中的用户名列表中存在allStaff.csv中的用户名字段,请将静态文本作为"VPN"放入最终的csv文件中。如果它不存在,请检查下一个csv文件:sessionCRXct.csv,然后放入静态文本"CRX",如果不检查最后一个csw文件:sessionTMSct.csv,则放入静态文本:TM,如果不存在,则将静态文本"none"放入最终csv文件。我有四个csv文件如下:
1.allStaff.csv2.VPN.csv3.CRX.csv4.TMS.csv
我已将csv文件导入如下变量:
$allUsers = Import-Csv -Path "C:allStaff.csv"
$vpn = Import-Csv -Path "C:VPN.csv" | Select-Object -ExpandProperty UserName
$crx = Import-Csv -Path "C:CRX.csv" | Select-Object -ExpandProperty UserName
$tms = Import-Csv -Path "C:TMS.csv" | Select-Object -ExpandProperty UserName
$allUsers变量显示以下内容:
名字姓氏用户名位置区域----------------------------Joe Bloggs jbloggs园丁维护财务会计Bob Seger bseger人力资源顾问Adam Boson aboson客户支持ITAdele bree abree Payroll Finance
$vpn变量显示以下内容:
用户名--------jbloggsjsmith
$crx变量显示以下内容:
用户名--------jbloggs名缩写和姓bseger
$tms变量显示以下内容:
用户名--------jbloggs名缩写和姓观察者aboson
然后我有下面的行来启动结果csv文件
$result = $allUsers | Select-Object *,ConnectionMethod
不太确定如何进行最终查询,我认为这应该是一个if-else循环,以遍历$result变量中的所有行,并检查其他csv(如果存在username字段(,然后返回静态文本。
$result | Export-Csv -Path "C:allStaffConnections.csv"
这就是我需要显示最终allStaffConnections.csv文件的方式。
名字姓氏用户名位置区域连接方法-----------------------------------------Joe Bloggs jbloggs Gardener维护VPNJim Smith jsmith会计财务VPNBob Seger bseger人力资源顾问CRXAdam Boson aboson客户支持IT TMSAdele bree abree Payroll Finance none
我使用以下代码是否正确?
$allUsers = Import-Csv -Path "C:allStaff.csv"
$vpn = Import-Csv -Path "C:VPN.csv" | Select-Object -ExpandProperty UserName
$crx = Import-Csv -Path "C:CRX.csv" | Select-Object -ExpandProperty UserName
$tms = Import-Csv -Path "C:TMS.csv" | Select-Object -ExpandProperty UserName
$vpnText = 'VPN'
$crxText = 'CRX'
$txsText = 'TMS'
$noneText = 'none'
$allUsersExtended = $allUsers | Select-Object *,ConnectionMethod
$results = $allUsersExtended.ForEach(
{
if($vpn -Contains $PSItem.UserName) {
# add $vpnText to ConnectionMethod column for that row in the $result
$PSItem.ConnectionMethod = $vpnText
}elseif($crx -Contains $PSItem.UserName) {
# add $crxText to ConnectionMethod column for that row in the $result
$PSItem.ConnectionMethod = $crxText
}elseif($tms -Contains $PSItem.UserName) {
# add $txsText to ConnectionMethod column for that row in the $result
$PSItem.ConnectionMethod = $tmsText
}else {
# add $noneText to ConnectionMethod column for that row in the $result
$PSItem.ConnectionMethod = $noteText
}
})
$results | Export-Csv -Path "C:allStaffConnections.csv" -NoTypeInformation
这会给我一个空的allStaffConnections.csv文件
我已经逐行运行代码,可以到达:
$allUsersExtended = $allUsers | Select-Object *,ConnectionMethod
这给了我额外的列"ConnectionMethod",但在运行循环后,它给了我一个空的allStaffConnections.csv文件。
这里有一种方法来完成这项工作。[grin]它假设您只想找到第一个连接类型。如果您想要所有这些类型(例如,JBloggs列出了所有3种类型(,则需要将它们连接起来。
它的作用。。。
- 在CSV文件中伪造读取
当准备使用真实数据时,注释掉或删除整个#region/#endregion
部分并使用Get-Content
- 遍历主集合
- 使用开关来测试每个连接类型列表中的成员身份
这会在开关找到匹配项时中断,因为它假定您只想要第一个匹配项。如果你想要所有的,那么你需要积累它们,而不是突破开关块 - 根据需要设置
$ConnectionType
- 使用所有需要的道具构建PSCO
这可能可以通过使用Select-Object
、通配符属性选择器和计算属性来缩短 - 将其发送到
$Results
集合 - 在屏幕上显示
- 将其保存到CSV文件
代码。。。
#region >>> fake reading in CSV files
# in real life, use Import-CSV
$AllUsers = @'
FirstName, LastName, UserName, Position, Area
Joe, Bloggs, jbloggs, Gardener, Maintenance
Jim, Smith, jsmith, Accountant, Finance
Bob, Seger, bseger, HR Advisor, Human Resources
Adam, Boson, aboson, Customer Support, IT
Adele, bree, abree, Payroll, Finance
'@ | ConvertFrom-Csv
$Vpn = @'
UserName
jbloggs
jsmith
'@ | ConvertFrom-Csv
$Crx = @'
UserName
jbloggs
jsmith
bseger
'@ | ConvertFrom-Csv
$Tms = @'
UserName
jbloggs
jsmith
bseger
aboson
'@ | ConvertFrom-Csv
#endregion >>> fake reading in CSV files
$Results = foreach ($AU_Item in $AllUsers)
{
# this presumes you want only the 1st connection type found
# if you want all of them, then you will need to concatenate them
switch ($AU_Item.UserName)
{
{$_ -in $Vpn.UserName}
{
$ConnectionType = 'VPN'
break
}
{$_ -in $Crx.UserName}
{
$ConnectionType = 'CRX'
break
}
{$_ -in $Tms.UserName}
{
$ConnectionType = 'TMS'
break
}
default
{
$ConnectionType = 'None'
}
}
[PSCustomObject]@{
FirstName = $AU_Item.FirstName
LastName = $AU_Item.LastName
UserName = $AU_Item.UserName
Position = $AU_Item.Position
Area = $AU_Item.Area
ConnectionTYpe = $ConnectionType
}
}
# on screen
$Results
# send to CSV
$Results |
Export-Csv -LiteralPath "$env:TEMPbrokencrow_-_UserConnectionType.csv" -NoTypeInformation
屏幕输出被截断。。。
FirstName : Joe
LastName : Bloggs
UserName : jbloggs
Position : Gardener
Area : Maintenance
ConnectionTYpe : VPN
[*...snip...*]
FirstName : Adele
LastName : bree
UserName : abree
Position : Payroll
Area : Finance
ConnectionTYpe : None
来自brokencrow_-_UserConnectionType.csv
的CSV文件内容。。。
"FirstName","LastName","UserName","Position","Area","ConnectionTYpe"
"Joe","Bloggs","jbloggs","Gardener","Maintenance","VPN"
"Jim","Smith","jsmith","Accountant","Finance","VPN"
"Bob","Seger","bseger","HR Advisor","Human Resources","CRX"
"Adam","Boson","aboson","Customer Support","IT","TMS"
"Adele","bree","abree","Payroll","Finance","None"