Magenoob
Magenoob

Reputation: 13

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

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,[email protected],
akroy1,Roy,DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520910,9811520910,257,//- (Sch E) 2,Aruna,[email protected],
kkhurana,"Khurana, Karan",DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520911,9811520911,258,//- (Sch E) 3,Aruna,[email protected],
csharma1,"Sharma, Chetan",DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520912,9811520912,259,//- (Sch E) 4,Chakra,[email protected],
sumit,Sumit,DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520913,9811520913,260,//- (Sch E) 5,Chakra,[email protected],
saji,"Aji, Shiby",DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520914,9811520914,261,//- (Sch E) 6,Chakra,[email protected],
rksharm1,"Sharma, Rajesh ",ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520915,9811520915,262,//- (Sch E) 7,Chakra,[email protected],
yxsingh,Singh,ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520916,9811520916,263,//- (Sch E) 8,Aruna,[email protected],
sponnaga,Ponnaganti,ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520917,9811520917,264,//- (Sch E) 9,Chakra,[email protected],
bmallena,Mallena,ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520918,9811520918,265,//- (Sch E) 10,Aruna,[email protected],
ngarg,Garg,ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520919,9811520919,266,//- (Sch E) 11,Chakra,[email protected],
dsharma5,Sharma,PDP,ALEXANDRIA,VOS-BPCS_Elec,9811520920,9811520920,267,//- (Sch E) 12,Aruna,[email protected],
rpyarwar,Yarwar,PDP,ALEXANDRIA,VOS-BPCS_Elec,9811520921,9811520921,268,//- (Sch E) 13,Chakra,[email protected],
hraj1,Hans,PDP,ALEXANDRIA,VOS-BPCS_Elec,9811520922,9811520922,269,//- (Sch E) 14,Chakra,[email protected],

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,[email protected],
akroy1,Roy,DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520910,9811520910,257,//- (Sch E) 2,Aruna,[email protected],akroy1
kkhurana,"Khurana, Karan",DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520911,9811520911,258,//- (Sch E) 3,Aruna,[email protected],kkhurana
csharma1,"Sharma, Chetan",DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520912,9811520912,259,//- (Sch E) 4,Chakra,[email protected],csharma1
sumit,Sumit,DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520913,9811520913,260,//- (Sch E) 5,Chakra,[email protected],sumit
saji,"Aji, Shiby",DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520914,9811520914,261,//- (Sch E) 6,Chakra,[email protected],saji
rksharm1,"Sharma, Rajesh ",ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520915,9811520915,262,//- (Sch E) 7,Chakra,[email protected],rksharm1
yxsingh,Singh,ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520916,9811520916,263,//- (Sch E) 8,Aruna,[email protected],yxsingh
sponnaga,Ponnaganti,ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520917,9811520917,264,//- (Sch E) 9,Chakra,[email protected],
bmallena,Mallena,ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520918,9811520918,265,//- (Sch E) 10,Aruna,[email protected],bmallena
ngarg,Garg,ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520919,9811520919,266,//- (Sch E) 11,Chakra,[email protected],ngarg
dsharma5,Sharma,PDP,ALEXANDRIA,VOS-BPCS_Elec,9811520920,9811520920,267,//- (Sch E) 12,Aruna,[email protected],dsharma5
rpyarwar,Yarwar,PDP,ALEXANDRIA,VOS-BPCS_Elec,9811520921,9811520921,268,//- (Sch E) 13,Chakra,[email protected],rpyarwar
hraj1,Hans,PDP,ALEXANDRIA,VOS-BPCS_Elec,9811520922,9811520922,269,//- (Sch E) 14,Chakra,[email protected],

Upvotes: 1

Views: 81

Answers (2)

user6811411
user6811411

Reputation:

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

Upvotes: 1

Lee_Dailey
Lee_Dailey

Reputation: 7479

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,[email protected]
akroy1,Roy,DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520910,9811520910,257,//- (Sch E) 2,Aruna,[email protected]
kkhurana,"Khurana, Karan",DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520911,9811520911,258,//- (Sch E) 3,Aruna,[email protected]
csharma1,"Sharma, Chetan",DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520912,9811520912,259,//- (Sch E) 4,Chakra,[email protected]
sumit,Sumit,DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520913,9811520913,260,//- (Sch E) 5,Chakra,[email protected]
saji,"Aji, Shiby",DESIGNERS,ALEXANDRIA,VOS-BPCS_Elec,9811520914,9811520914,261,//- (Sch E) 6,Chakra,[email protected]
rksharm1,"Sharma, Rajesh ",ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520915,9811520915,262,//- (Sch E) 7,Chakra,[email protected]
yxsingh,Singh,ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520916,9811520916,263,//- (Sch E) 8,Aruna,[email protected]
sponnaga,Ponnaganti,ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520917,9811520917,264,//- (Sch E) 9,Chakra,[email protected]
bmallena,Mallena,ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520918,9811520918,265,//- (Sch E) 10,Aruna,[email protected]
ngarg,Garg,ELECTRICAL PR,ALEXANDRIA,VOS-BPCS_Elec,9811520919,9811520919,266,//- (Sch E) 11,Chakra,[email protected]
dsharma5,Sharma,PDP,ALEXANDRIA,VOS-BPCS_Elec,9811520920,9811520920,267,//- (Sch E) 12,Aruna,[email protected]
rpyarwar,Yarwar,PDP,ALEXANDRIA,VOS-BPCS_Elec,9811520921,9811520921,268,//- (Sch E) 13,Chakra,[email protected]
hraj1,Hans,PDP,ALEXANDRIA,VOS-BPCS_Elec,9811520922,9811520922,269,//- (Sch E) 14,Chakra,[email protected]
'@ | 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      : [email protected]
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      : [email protected]
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","[email protected]","__NotFound__"

[*...snip...*] 

"yxsingh","Singh","ELECTRICAL PR","ALEXANDRIA","VOS-BPCS_Elec","9811520916","9811520916","263","//- (Sch E) 8","Aruna","[email protected]","yxsingh"

Upvotes: 3

Related Questions