Combine two CSV files in powershell without changing the order of columns

Question

I have "a.csv" and "b.csv" . I tried to merge them with below commands

cd c:/users/mine/test 
Get-Content   a.csv, b.csv |  Select-Object -Unique | Set-Content -Encoding ASCII joined.csv

But I got Output file like b.csv added by end of the row of a.csv. I wanted add by end of the column of a.csv then b.csv columns should begin

Vm     Resource    SID
mnvb    vclkn     vxjcb
vjc.v   vnxc,m    bvkxncb

Vm      123     456     789
mnvb   apple    banana  orange 
vjc.v  lemon    onion   tomato

My expected output should be like below. Without changing the order

Vm     Resource    SID    123       456     789
mnvb    vclkn   vxjcb     apple    banana  orange 
vjc.v   vnxc,m  bvkxncb   lemon    onion   tomato

Using this [`Join-Object`](https://www.powershellgallery.com/packages/Join/) (see also: [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026)): `Import-Csv a.csv | Join-Object (Import-Csv b.csv) -On Vm | Export-Csv joined.csv` — iRon, May 04 '20 at 07:26

score 1 · Accepted Answer · answered May 04 '20 at 06:51

From here, there are two ways to do it -

Join-Object custom function by RamblingCookieMonster. This is short and sweet. After you import the function in your current PoSh environment, you can use the below command to get your desired result -

Join-Object -Left $a -Right $b -LeftJoinProperty vm -RightJoinProperty vm | Export-Csv Joined.csv -NTI

The accepted answer from mklement which would work for you as below -

# Read the 2 CSV files into collections of custom objects.
# Note: This reads the entire files into memory.
$doc1 = Import-Csv a.csv
$doc2 = Import-Csv b.csv

$outFile = 'Joined.csv'

# Determine the column (property) names that are unique to document 2.
$doc2OnlyColNames = (
  Compare-Object $doc1[0].psobject.properties.name $doc2[0].psobject.properties.name |
    Where-Object SideIndicator -eq '=>'
).InputObject

# Initialize an ordered hashtable that will be used to temporarily store
# each document 2 row's unique values as key-value pairs, so that they
# can be appended as properties to each document-1 row.
$htUniqueRowD2Props = [ordered] @{}

# Process the corresponding rows one by one, construct a merged output object
# for each, and export the merged objects to a new CSV file.
$i = 0
$(foreach($rowD1 in $doc1) {
  # Get the corresponding row from document 2.
  $rowD2 = $doc2[$i++]
  # Extract the values from the unique document-2 columns and store them in the ordered
  # hashtable.
  foreach($pname in $doc2OnlyColNames) { $htUniqueRowD2Props.$pname = $rowD2.$pname }
  # Add the properties represented by the hashtable entries to the
  # document-1 row at hand and output the augmented object (-PassThru).
  $rowD1 | Add-Member -NotePropertyMembers $htUniqueRowD2Props -PassThru
}) | Export-Csv -NoTypeInformation -Encoding Utf8 $outFile

..If I dont want to remove duplicate columns.What needs to be change from the above code — , May 04 '20 at 15:25
I doubt you could achieve that with the above code cause if you try to use`Add-Member` to add the `VM` column for **b.csv**, it would complain that the member `VM` already exists. You could use something like - `gc $a = a.csv; gc $b = b.csv; 0..($b.Count – 1) | %{$a[$_],$b[$_] -join ','} | Out-File $outFile` — Vivek Kumar Singh, May 05 '20 at 07:32

Combine two CSV files in powershell without changing the order of columns

1 Answers1