文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >将两个csv文件与第三个csv文件的输出连接起来，输出不显示已连接的值

问将两个csv文件与第三个csv文件的输出连接起来，输出不显示已连接的值
EN

Stack Overflow用户
提问于 2019-04-12 06:11:33
回答 2查看 72关注 0票数 1

我有两个CSV文件output.csv和Roster.csv

"output.csv“有以下标题

代码语言：javascript
复制
UserID, User, Department, City, Group, Phone, Mobile, Extension, Office, Manager, Email

"Roster.csv“有这个标题

代码语言：javascript
复制
BUNID

我希望将BUNID与USERID匹配，并生成具有以下标题的输出

代码语言：javascript
复制
UserID, User, Department, City, Group, Phone, Mobile, Extension, Office, Manager, Email, BUNID

问题是输出是生成的，但是BUNID字段是唯一的空白字段。

这里是代码

代码语言：javascript
复制
$csv1 = Import-Csv "C:\CSVfiles\output.csv"
$csv2 = Import-csv "C:\CSVfiles\Roster.csv"

$Join = Join-Object -Left $csv1 -Right $csv2 -LeftJoinProperty UserID -RightJoinProperty BUNID -Type AllInLeft -RightProperties BUNID

$Join | select-object UserID, User, Department, City, Group, Phone, Mobile, Extension, Office, Manager, Email, BUNID | sort BUNID | Export-Csv "C:\CSVfiles\output_pas.csv"

在第三个csv上输出这些字段。

代码语言：javascript
复制
UserID, User, Department, City, Group, Phone, Mobile, Extension, Office, Manager, Email, BUNID

输出应该包含来自output.csv的所有数据，并匹配来自Roster.csv的BUNID，类似于我们可以使用vlookup所做的事情。

我知道我在犯一个业余错误，但我无法理解什么。

Output.csv样本

代码语言：javascript
复制
UserID,User,Department,City,Group,Phone,Mobile,Extension,Office,Manager,Email,
akumar58,Ankush,DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520909,9811520909,256,//- (Sch E) 1,Aruna,dfg12@gtus.com,
akroy1,Roy,DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520910,9811520910,257,//- (Sch E) 2,Aruna,dfg13@gtus.com,
kkhurana,"Khurana, Karan",DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520911,9811520911,258,//- (Sch E) 3,Aruna,dfg14@gtus.com,
csharma1,"Sharma, Chetan",DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520912,9811520912,259,//- (Sch E) 4,Chakra,dfg15@gtus.com,
sumit,Sumit,DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520913,9811520913,260,//- (Sch E) 5,Chakra,dfg16@gtus.com,
saji,"Aji, Shiby",DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520914,9811520914,261,//- (Sch E) 6,Chakra,dfg17@gtus.com,
rksharm1,"Sharma, Rajesh ",ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520915,9811520915,262,//- (Sch E) 7,Chakra,dfg18@gtus.com,
yxsingh,Singh,ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520916,9811520916,263,//- (Sch E) 8,Aruna,dfg19@gtus.com,
sponnaga,Ponnaganti,ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520917,9811520917,264,//- (Sch E) 9,Chakra,dfg20@gtus.com,
bmallena,Mallena,ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520918,9811520918,265,//- (Sch E) 10,Aruna,dfg21@gtus.com,
ngarg,Garg,ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520919,9811520919,266,//- (Sch E) 11,Chakra,dfg22@gtus.com,
dsharma5,Sharma,PDP,ALEXANDRIA,VOS-BPCS_Elec,9811520920,9811520920,267,//- (Sch E) 12,Aruna,dfg23@gtus.com,
rpyarwar,Yarwar,PDP,ALEXANDRIA,VOS-BPCS_Elec,9811520921,9811520921,268,//- (Sch E) 13,Chakra,dfg24@gtus.com,
hraj1,Hans,PDP,ALEXANDRIA,VOS-BPCS_Elec,9811520922,9811520922,269,//- (Sch E) 14,Chakra,dfg25@gtus.com,

Roster.CSV样品

代码语言：javascript
复制
BUNID
axsaxena
kjhebbar
smukher5
akroy1
kkhurana
csharma1
sumit
saji
rksharm1
yxsingh
bmallena
ngarg
dsharma5
rpyarwar
smohan1
hbmane
sdebnat1
skumar38
aprinja1
shanda
yhbijli1
bpannee1
saji

最终输出我想得到的，但是BUNID的最后一个字段使用脚本是空的。

代码语言：javascript
复制
UserID,User,Department,City,Group,Phone,Mobile,Extension,Office,Manager,Email,BUNID
akumar58,Ankush,DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520909,9811520909,256,//- (Sch E) 1,Aruna,dfg12@gtus.com,
akroy1,Roy,DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520910,9811520910,257,//- (Sch E) 2,Aruna,dfg13@gtus.com,akroy1
kkhurana,"Khurana, Karan",DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520911,9811520911,258,//- (Sch E) 3,Aruna,dfg14@gtus.com,kkhurana
csharma1,"Sharma, Chetan",DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520912,9811520912,259,//- (Sch E) 4,Chakra,dfg15@gtus.com,csharma1
sumit,Sumit,DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520913,9811520913,260,//- (Sch E) 5,Chakra,dfg16@gtus.com,sumit
saji,"Aji, Shiby",DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520914,9811520914,261,//- (Sch E) 6,Chakra,dfg17@gtus.com,saji
rksharm1,"Sharma, Rajesh ",ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520915,9811520915,262,//- (Sch E) 7,Chakra,dfg18@gtus.com,rksharm1
yxsingh,Singh,ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520916,9811520916,263,//- (Sch E) 8,Aruna,dfg19@gtus.com,yxsingh
sponnaga,Ponnaganti,ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520917,9811520917,264,//- (Sch E) 9,Chakra,dfg20@gtus.com,
bmallena,Mallena,ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520918,9811520918,265,//- (Sch E) 10,Aruna,dfg21@gtus.com,bmallena
ngarg,Garg,ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520919,9811520919,266,//- (Sch E) 11,Chakra,dfg22@gtus.com,ngarg
dsharma5,Sharma,PDP,ALEXANDRIA,VOS-BPCS_Elec,9811520920,9811520920,267,//- (Sch E) 12,Aruna,dfg23@gtus.com,dsharma5
rpyarwar,Yarwar,PDP,ALEXANDRIA,VOS-BPCS_Elec,9811520921,9811520921,268,//- (Sch E) 13,Chakra,dfg24@gtus.com,rpyarwar
hraj1,Hans,PDP,ALEXANDRIA,VOS-BPCS_Elec,9811520922,9811520922,269,//- (Sch E) 14,Chakra,dfg25@gtus.com,
csv
vlookup
powershell
EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2019-04-12 08:39:46

这里有一个合并两个CSV文件的方法。它不进行传统的合并，因为它添加了$Roster条目，而不是将它与$UserID条目合并。

代码语言：javascript
复制
# fake reading in a CSV file
#    in real life, use Import-CSV
$Roster = @'
BUNID
axsaxena
kjhebbar
smukher5
akroy1
kkhurana
csharma1
sumit
saji
rksharm1
yxsingh
bmallena
ngarg
dsharma5
rpyarwar
smohan1
hbmane
sdebnat1
skumar38
aprinja1
shanda
yhbijli1
bpannee1
saji
'@ | ConvertFrom-Csv

# fake reading in another CSV file
# apparent accidental trailing comma manually removed from each line
$OutputCSV = @'
UserID,User,Department,City,Group,Phone,Mobile,Extension,Office,Manager,Email
akumar58,Ankush,DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520909,9811520909,256,//- (Sch E) 1,Aruna,dfg12@gtus.com
akroy1,Roy,DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520910,9811520910,257,//- (Sch E) 2,Aruna,dfg13@gtus.com
kkhurana,"Khurana, Karan",DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520911,9811520911,258,//- (Sch E) 3,Aruna,dfg14@gtus.com
csharma1,"Sharma, Chetan",DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520912,9811520912,259,//- (Sch E) 4,Chakra,dfg15@gtus.com
sumit,Sumit,DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520913,9811520913,260,//- (Sch E) 5,Chakra,dfg16@gtus.com
saji,"Aji, Shiby",DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520914,9811520914,261,//- (Sch E) 6,Chakra,dfg17@gtus.com
rksharm1,"Sharma, Rajesh ",ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520915,9811520915,262,//- (Sch E) 7,Chakra,dfg18@gtus.com
yxsingh,Singh,ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520916,9811520916,263,//- (Sch E) 8,Aruna,dfg19@gtus.com
sponnaga,Ponnaganti,ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520917,9811520917,264,//- (Sch E) 9,Chakra,dfg20@gtus.com
bmallena,Mallena,ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520918,9811520918,265,//- (Sch E) 10,Aruna,dfg21@gtus.com
ngarg,Garg,ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520919,9811520919,266,//- (Sch E) 11,Chakra,dfg22@gtus.com
dsharma5,Sharma,PDP,ALEXANDRIA,VOS-BPCS_Elec,9811520920,9811520920,267,//- (Sch E) 12,Aruna,dfg23@gtus.com
rpyarwar,Yarwar,PDP,ALEXANDRIA,VOS-BPCS_Elec,9811520921,9811520921,268,//- (Sch E) 13,Chakra,dfg24@gtus.com
hraj1,Hans,PDP,ALEXANDRIA,VOS-BPCS_Elec,9811520922,9811520922,269,//- (Sch E) 14,Chakra,dfg25@gtus.com
'@ | ConvertFrom-Csv

# if you want this to be blank or $Null when no match is found,
#    replace the below with the desired value or "$Null"
$NotFound = '__NotFound__'

$Results = foreach ($OC_Item in $OutputCSV)
    {
    if ($OC_Item.UserID -in $Roster.BUNID)
        {
        $BUNID = $OC_Item.UserID
        }
        else
        {
        $BUNID = $NotFound
        }
    $OC_Item | Add-Member -MemberType NoteProperty -Name 'BUNID' -Value $BUNID

    $OC_Item
    }

$Results = $Results |
    Sort-Object -Property BUNID

# send to screen
$Results

# send to CSV file
$Results |
    Export-Csv -LiteralPath "$env:TEMP\Magenoob_-_Merged_User_Info.csv" -NoTypeInformation

截取屏幕输出..。

代码语言：javascript
复制
UserID     : akumar58
User       : Ankush
Department : DESIGNERS
City       : ALEXANDRIA
Group      : VOS-BPCS_Elec
Phone      : 9811520909
Mobile     : 9811520909
Extension  : 256
Office     : //- (Sch E) 1
Manager    : Aruna
Email      : dfg12@gtus.com
BUNID      : __NotFound__

[*...snip...*] 

UserID     : yxsingh
User       : Singh
Department : ELECTRICAL PR
City       : ALEXANDRIA
Group      : VOS-BPCS_Elec
Phone      : 9811520916
Mobile     : 9811520916
Extension  : 263
Office     : //- (Sch E) 8
Manager    : Aruna
Email      : dfg19@gtus.com
BUNID      : yxsingh

截断CSV文件内容..。

代码语言：javascript
复制
"UserID","User","Department","City","Group","Phone","Mobile","Extension","Office","Manager","Email","BUNID"
"akumar58","Ankush","DESIGNERS","ALEXANDRIA","VOS-BPCS_Elec","9811520909","9811520909","256","//- (Sch E) 1","Aruna","dfg12@gtus.com","__NotFound__"

[*...snip...*] 

"yxsingh","Singh","ELECTRICAL PR","ALEXANDRIA","VOS-BPCS_Elec","9811520916","9811520916","263","//- (Sch E) 8","Aruna","dfg19@gtus.com","yxsingh"
票数 3
EN

Stack Overflow用户

发布于 2019-04-12 12:22:34

如果BUNID与roster.csv中的BUNID匹配，您只想附加字段roster.csv，

因此，在计算的属性中包含Select-Object的@Lee_Dailey if的单个管道应该执行以下操作：

代码语言：javascript
复制
$Roster = Import-Csv "C:\CSVfiles\Roster.csv"
Import-Csv "C:\CSVfiles\output.csv" | Select-Object *,
  @{n='BUNID';e={if($_.UserID -in $Roster.BUNID){$_.UserID}else{"__NotFound__"}}} |
    Sort-Object BUNID | Export-Csv "C:\CSVfiles\output_pas.csv" -NoTypeInformation

如果新文件应该是，而不是，则包含Roster.csv中没有匹配的行：

代码语言：javascript
复制
$Roster = Import-Csv "C:\CSVfiles\Roster.csv"
Import-Csv "C:\CSVfiles\output.csv" | Where-Object UserID -in $Roster.BUNID | 
  Select-Object *,@{n='BUNID';e={$_.UserID}} |
    Sort-Object BUNID | Export-Csv "C:\CSVfiles\output_pas.csv" -NoTypeInformation
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/55645484

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档