CODEWITHSUNDEEP
powershellcsvmerge
ShawnIT
ShawnIT

Reputation: 227

Compare 2 csv files and match based on 1 column then export new file that contains fields from both

I have 2 csv files. Each have different headers and different number of columns, and have different number of entries.

Here are some examples of the first couple lines

CSV 1 

    ID,Last_Name,First_Name,Middle_Name,Email_Addr,Title,Gender
    ###1,smith,bill,p,[email protected],boss,m
    ###2,smith2,billy,p,[email protected],someguy,m

CSV 2

    ID,Name Id,Last Name,First Name,Middle Name,Gender
    ###2,ID1010,smith2,billy,p,M

I am trying to import them and compare the ID column. When a match is found I am wanting a new csv file with All info from CSV 1 and the matched Name ID from csv 2.

New CSV Example:

    ID,Last_Name,First_Name,Middle_Name,Email_Addr,Title,Gender,Name Id
    ###1,smith,bill,p,[email protected],boss,m,
    ###2,smith2,billy,p,[email protected],someguy,m,ID1010

Ive been looking and came across this Stackoverflow from about a year ago that seemed to be on the right track but I cant seem to get code modified for my needs. Here is what I have tried.

    $csv1 = Import-Csv -Path C:\STAFF\test1sky.csv
    $csv2 = Import-Csv -Path C:\STAFF\test1power.csv

    ForEach($Record in $csv2){
    $MatchedValue = (Compare-Object $csv1 $Record -Property "ID" -IncludeEqual -ExcludeDifferent -PassThru).value
    $Record = Add-Member -InputObject $Record -Type NoteProperty -Name "Name Id" -Value $MatchedValue
    }
    $csv2|Export-Csv 'C:\STAFF\combined.csv' -NoTypeInformation

I get the correct header in the new file but I never get the Name ID values to come though.

Any idea where I went wrong? I maybe on the wrong path completely and there be a easier way, but I need to be able to do this nightly without user interaction. Any help is appreciated!!

Upvotes: 0

Views: 8541

Answers (3)

Powerboy2
Powerboy2

Reputation: 69

$CSV1 = import-csv C:\Path\To\File1.csv
$CSV2 = import-csv C:\Path\To\File2.csv

#adds a row named "Name ID" to the PS Object( the CSV Import)
$CSV1|ForEach{$_|Add-Member 'Name ID' $Null}

ForEach($Record in $CSV1){

#gets the value from CSV1 for comparing to CSV2 
$NameValue=Record."Last_Name"

#gets the Power Shell Object from the CSV2 Import that matches the Name ID from $csv1
$Nameobject= $CSV2|Where-object "Last Name" -contains $Namevalue

#Sets the Field "Name ID" in the PS Object $CSV1 Record to the Name ID from $csv2 
$record."Name ID" = $Nameobject."Name ID"
}

You can easily grab addtional fields by adding other references to the CSV1 File by manipulating the CSV2 PS Object.

$record."Middle Name" = $nameobject."Middle_Name"

Since you have the entire object in the for loop form $csv2 you can call any of its fields or manipulate them by using variables and " |select -Property "Value" Like this 

$objlength = $nameobject |select "First_Name"
$objlength.length

but i prefer to call it directly from the object as the output looks cleaner like this 

$nameobject."First_Name".length

Upvotes: 1

TheMadTechnician
TheMadTechnician

Reputation: 36332

Let's try to simplify this. Add the 'Name ID' field to all records in CSV1. Then loop through it, and get the matches, and update the field. Something like:

$CSV1 = C:\Path\To\File1.csv
$CSV2 = C:\Path\To\File2.csv
$CSV1|ForEach{$_|Add-Member 'Name ID' $Null}
ForEach($Record in $CSV1){
    $Record.'Name ID' = $CSV2|Where{$_.ID -eq $Record.ID}|Select -Expand 'Name ID'
}

Upvotes: 1

Walter Mitty
Walter Mitty

Reputation: 18940

The operation you are looking for is called a relational join. Sometimes it's called an inner join, and sometimes just a join. My knowledge of join comes from SQL, not from Powershell.

Here's a description of "Join-Object". It seems to be what you are looking for.

http://blogs.msdn.com/b/powershell/archive/2012/07/13/join-object.aspx

Upvotes: 0

Related Questions