How can i merge multiple CSV files in which two columns have same data using Powershell

Question

Am very new to powershell topic , I just want a help from you all . I need to merge multiple CSV file in a folder , in which this CSV files have same headers and different data , but in some cases two or more columns may have same data .

So here is my code ,

$filevalues = Import-Csv -Path  (Get-ChildItem -Path C:\Users\hi\ -Filter '*.csv').FullName

$firstfile = Import-Csv -Path C:\Users\hi\VM1.csv

[String[]]$NamesofApplication = $firstfile.Application;
[String[]]$NamesofFolder = $firstfile.FileName;
[String[]]$NamesofConfigvariable = $firstfile.ConfigVariable;
[String[]]$NamesofVM1 = $filevalues.VM1;
[String[]]$NamesofVM2 = $filevalues.VM2;
[String[]]$NamesofVM3 = $filevalues.VM3;

 Write-Host $NamesofVM1.Count

$array = @();
for($i=0; $i -lt $NamesofApplication.Count ; $i++){
    $hashtable = [pscustomobject]@{Application=$NamesofApplication[$i]; FileName=$NamesofFolder[$i]; ConfigVariable=$NamesofConfigvariable[$i];VM1=$NamesofVM1[$i];VM2=$NamesofVM2[$i];VM3=$NamesofVM3[$i] };
    $array += $hashtable;
};

$array | ForEach-Object { [pscustomobject] $_ } | Export-Csv C:\Users\hi\OutPut.csv

and here is my VM1.csv,VM2.csv and VM3.csv VM1.csv , VM2.csv , VM3.csv

and i just want my output to be like Output.csv

Can anyone help me in this .

Did you try to search for it? [https://stackoverflow.com/search?q=merge+multiple+CSV+files+Powershell](https://stackoverflow.com/search?q=merge+multiple+CSV+files+Powershell) — Olaf, Aug 05 '20 at 08:33
yeah , but nothing worked . since i have same data for two column , it was not printing actually . i tried every code , and i was trying for past two days @Olaf — DhanyaBalakrishnan, Aug 05 '20 at 08:35
What do you mean with two or more columns have same data, that wont prevent you from merging CSV files. — Daniel Björk, Aug 05 '20 at 08:49
i tried every code in stackoverflow regarding merging , but nothing is working for me @DanielBjörk — DhanyaBalakrishnan, Aug 05 '20 at 09:49
Please clarify what you men with "two or more columns have same data" — Daniel Björk, Aug 05 '20 at 09:50
Can you please check those three CSV files i have given down the code , in that VM1,csv and VM2 .csv has same data and different headers . like VM1.csv has a column with name VM1 and VM2.csv has a column with name VM2 . but both have same data @DanielBjörk — DhanyaBalakrishnan, Aug 05 '20 at 10:01
It's possible that what you want to do is a join rather than a merge. If so, do a search on Join-Object. This is a third party function offered by the author in various places, including Stackoverflow. — Walter Mitty, Aug 06 '20 at 11:22

score 0 · Accepted Answer · answered Aug 05 '20 at 09:32

If like in your example, csv can be merge by line, you can add data directly with Add-Member. A small example of the use of Add-member with csv data.

$csv1 = @'
Appli,Folder,FileName,Config,VM1
ABC,Folder1,FN1,Con1,VM11
,Folder2,FN2,Con2,VM12
,Folder3,FN3,Con3,VM13
SID,Folder4,FN4,Con4,VM14
,Folder5,FN5,Con5,VM15
'@ | ConvertFrom-Csv 

$csv2 = @'
Appli,Folder,FileName,Config,VM2
ABC,Folder1,FN1,Con1,VM11
,Folder2,FN2,Con2,VM12
,Folder3,FN3,Con3,VM13
SID,Folder4,FN4,Con4,VM14
,Folder5,FN5,Con5,VM15
'@ | ConvertFrom-Csv 

$csv3 = @'
Appli,Folder,FileName,Config,VM3
ABC,Folder1,FN1,Con1,VM11
,Folder2,FN2,Con2,VM12
,Folder3,FN3,Con3,VM13
SID,Folder4,FN4,Con4,VM14
,Folder5,FN5,Con5,VM15
'@ | ConvertFrom-Csv 


for ($i = 0; $i -lt $csv1.Count; $i++)
{ 
    $csv1[$i] | Add-Member -MemberType NoteProperty -Name VM2 -Value $csv2[$i].VM2
    $csv1[$i] | Add-Member -MemberType NoteProperty -Name VM3 -Value $csv3[$i].VM3
}
$csv1 | ConvertTo-Csv -NoTypeInformation

How many csv files do you want to merge? Is the property to merge is always VM1, VM2 or VM3? — YannCha, Aug 05 '20 at 14:41
that's dynamic it's based on another code . So it may be three or four etc — DhanyaBalakrishnan, Aug 06 '20 at 05:41

score 0 · Answer 2 · answered Aug 05 '20 at 10:21

Try Below Script to Script to merge multiple CSV files into one CSV, by appending each file to the end.

$getFirstLine = $true

get-childItem "*.csv" |

foreach { $filePath = $_

$lines = Get-Content $filePath  
$linesToWrite = switch($getFirstLine) {
       $true  {$lines}
       $false {$lines | Select -Skip 1}

}


Add-Content "<Location>\<Output File Name>.csv" $linesToWrite

}

Write-Output "New CSV File successfully created"

score 0 · Answer 3 · answered Aug 06 '20 at 10:19

Using this Join-Object cmdlet (see also: In Powershell, what's the best way to join two tables into one?):

If there is no relation between the object list (other the the row index):

$csv1 | Merge-Object $csv2 | Merge-object $csv3 | ConvertTo-Csv -NoTypeInformation

But I would advice against this approach as the rows need to be in the same order and have an equal starting point. It is better to merge the lists on a related item, e.g. the FileName:

$csv1 | Merge-Object $csv2 -on FileName | Merge-object $csv3 -on FileName | ...

Or multiple properties as Folder and FileName:

$csv1 | Merge-Object $csv2 -on Folder,FileName | Merge-object $csv3 -on Folder,FileName | ...

All three command will have the same results:

Appli Folder  FileName Config VM1  VM2  VM3
----- ------  -------- ------ ---  ---  ---
ABC   Folder1 FN1      Con1   VM11 VM11 VM11
      Folder2 FN2      Con2   VM12 VM12 VM12
      Folder3 FN3      Con3   VM13 VM13 VM13
SID   Folder4 FN4      Con4   VM14 VM14 VM14
      Folder5 FN5      Con5   VM15 VM15 VM15

only with the latter the rows do not have to be in order:

How can i merge multiple CSV files in which two columns have same data using Powershell

3 Answers3