Joining two csv files with output on third csv file, output not showing joined values

Question

I have two CSV files output.csv and Roster.csv

"output.csv" has these headers

UserID, User, Department, City, Group, Phone, Mobile, Extension, Office, Manager, Email

"Roster.csv" has this header

BUNID

I want to match the BUNID with USERID and generate an output with these headers

UserID, User, Department, City, Group, Phone, Mobile, Extension, Office, Manager, Email, BUNID

The problem is that the output is generated but the BUNID field is the only one that is blank.

Here is the code

$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"

output these field on a third csv.

UserID, User, Department, City, Group, Phone, Mobile, Extension, Office, Manager, Email, BUNID

The output should contain all the data from output.csv and match the BUNID from the Roster.csv, something like what we can do with vlookup.

I know I am making an amateur mistake but not able to understand what.

Output.csv sample

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 Sample

BUNID
axsaxena
kjhebbar
smukher5
akroy1
kkhurana
csharma1
sumit
saji
rksharm1
yxsingh
bmallena
ngarg
dsharma5
rpyarwar
smohan1
hbmane
sdebnat1
skumar38
aprinja1
shanda
yhbijli1
bpannee1
saji

Final output what i want to get, but the last field of BUNID comes empty using the script.

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,

please add a sample of the two input CSV files - the header and two or three rows would be enuf.///// also, which `Join-Object` module or script are you using? that is not a standard item ... — Lee_Dailey, Apr 12 '19 at 06:48
I have added the sample above and i am using an excel module. — Magenoob, Apr 12 '19 at 07:37
thanks! [*grin*] ///// your Output.csv shows a trailing comma ... that may produce problems. what happens if you _remove_ that trailing comma? — Lee_Dailey, Apr 12 '19 at 08:05
about the `Join-Object` - that comes from where? it MAY be what is causing your problem in that you told it to merge based on two fields matching ... and therefore will leave ONE of them out since they _should be identical_. — Lee_Dailey, Apr 12 '19 at 08:11
Using this [`Join-Object`](https://stackoverflow.com/a/45483110/1701026): `$Csv1 | LeftJoin $Csv2 -On UserID -Eq BUNID | Export-Csv "C:\CSVfiles\output_pas.csv"` — iRon, Apr 12 '19 at 19:41

score 3 · Accepted Answer · answered Apr 12 '19 at 08:39

here's one way to merge the two CSV files. it does not do a traditional merge in that it ADDS the $Roster entry instead of merging it with the $UserID entry.

# 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

truncated on screen output ...

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

truncated CSV file content ...

"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"

Whoever is interested in serious coding is able (or should be) to abstract how you get the sample data. PLEASE stop repeating that long annoying samples in your answers, it just distracts from the solution. — , Apr 12 '19 at 11:33
No, this is just my personal view, should have included an ***IMO***. — , Apr 12 '19 at 11:40
@LotPings - kool! [*grin*] i will try to keep that "too much extra stuff" idea in mind ... — Lee_Dailey, Apr 12 '19 at 12:45

score 1 · Answer 2 · answered Apr 12 '19 at 12:22

IIUR you just want to append the field BUNID if UserID matches the BUNID from roster.csv,
so a single pipe with a Select-Objectcontaining @Lee_Dailey if inside a calculated property should do:

$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

In case the new file should NOT contain rows without a match in Roster.csv:

$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

Joining two csv files with output on third csv file, output not showing joined values

2 Answers2