CODEWITHSUNDEEP
filepowershelljoincsv
Sam Song
Sam Song

Reputation: 31

Powershell: Merge selective columns in 2 CSV file

Due to a sudden need to write a script,to combine 2 csv files with rows and columns having at least 1 common column, I am resorting to powershell. I am noob in Powershell. Can anyone advise how to read from both files, compare and combine a row with a common column, finally output to another file?

CSV File 1

Hosts  ABC  DEF
=====  ===  ===
SVR01   10  100
SRV02   22   99

CSV File 2

Hosts  UVW   XYZ
=====  ===   ===
SVR01   13  10.5
SRV02   19   8.9

Expected output

Hosts  DEF  UVW   XYZ
=====  ===  ===   ===
SVR01  100   13  10.5
SRV02   99   19   8.9

Hope to seek some guidance.

Thank you.

Upvotes: 3

Views: 14535

Answers (2)

user1555314
user1555314

Reputation: 17

I am slo looking to merge to or more csv files if a column ID row data matches. Tried the above script and did not get it to work. Is there another way to do this

Upvotes: 0

gpduck
gpduck

Reputation: 381

If the files will not be too large, I would load one into a hash table using the common column value as the key, then iterate over the second file and use the key value to look up the columns to merge from the first file. You just have to watch out for eating up too much RAM if the first file is big (what is big is dependent on how much RAM you have) because its entire contents will be loaded into memory.

#Make an empty hash table for the first file

$File1Values = @{}


#Import the first file and save the rows in the hash table indexed on "KeyColumn"

Import-Csv -Path c:\file1.csv | ForEach-Object {
  $File1Values.Add($_.KeyColumn, $_)
}


#Import the second file, using Select-Object to select all the values from file2,
#  and adding custom properties from the first file using the name/expression
#  hash tables.

Import-Csv -Path c:\file2.csv | Select-Object *,@{
  Name="ABC"; Expression={$File1Values[$_.KeyColumn].ABC}
}, @{
  #You can abbreviate Name/Expression
  N="DEF"; E={$File1Values[$_.KeyColumn].DEF}
} | Export-Csv -Path c:\OutFile.csv

For that last section, you could also use any of these techniques The Many Ways to Create a Custom Object to create the custom objects, I chose the "Select-Object" method as you only have to rebuild the bits of the object that are coming from the first file (at the expense of a more complex syntax).

If you're on V3 and want to use the new [PsCustomObject] type accelerator, that last bit would look like this (notice how you have to manually add both file 1 and file 2 properties):

#Import the second file and make a custom object with properties from both files

Import-Csv -Path c:\file2.csv | ForEach-Object {
  [PsCustomObject]@{
    ABC = $File1Values[$_.KeyColumn].ABC;
    DEF = $File1Values[$_.KeyColumn].DEF;
    UVW = $_.UVW;
    XYZ = $_.XYZ;
  }
} | Export-Csv -Path c:\OutFile.csv

Upvotes: 4

Related Questions