merge 2 different CSV with different header into CSV in powershell.

PowerShell Help
1

I just want to mention that there is a similar thread on this site regarding this topic, but I cant run the code written by Olaf because due to Powershell constrainedlanguage mode turned on in my environment. Most resources on Google is mostly on merging CSV with same header or shared header . I managed to combine both CSV into a CSV but they turned into string.

My scenario as below:

File 1 CSV:

A B C
1 2 3
4 5 6
7 8 9

File 2 CSV:

D E F
10 11 12
13 14 15
16 17 18

desired output CSV:

A B C D E F
1 2 3 10 11 12
4 5 6 13 14 15
7 8 9 16 17 18

Any help on this is greatly appreciated.

2

[pre]

$csv1 = Get-Content C:\Windows\Temp\csv1.csv
$csv2 = Get-Content C:\Windows\Temp\csv2.csv

0…($csv2.Count - 1) | %{$csv1[$],$csv2[$] -join ‘,’} | Out-File C:\Windows\Temp\csv3.csv

[/pre]

3

Great response Kiran. That definitely builds what nearownkira45 is asking for.

I’m curious though, nearownkira45, is that what you wanted? Do you have two correlating CSVs with equal and matching records?

 

So if the first CSV were like this:

FirstName, LastName

Peter, Griffin

Lois, Griffin

Meg, Griffin


And the second CSV were this:

VoiceActorFirst, VoiceActorLast

Seth, MacFarlane

Alex, Borstein

Mila, Kunis


You’d end up with:

FirstName, LastName, VoiceActorFirst, VoiceActorLast

Peter, Griffin, Seth, MacFarlane

Lois, Griffin, Alex, Borstein

Meg, Griffin, Mila, Kunis


 

Now maybe this is exactly what you wanted. Maybe not? Do let us know.

4

Hmmm … without trying to be picky … but when I take nearownkira45’s example data and use Kirans code I would end up with something like this:

A B C,D E F

1 2 3,10 11 12

4 5 6,13 14 15

7 8 9,16 17 18

I’d expect that nearownkira45 wanted to have something more usable. :wink:

You would need to use the same character to join both data sets like you use inside the CSV files.

5

Hey Kiran, the OP wanted this output.
A B C D E F
1 2 3 10 11 12
4 5 6 13 14 15
7 8 9 16 17 18

Your code does that, layout but adds that additional comma.

 0..($csv2.Count – 1) | 
%{$csv1[$_],$csv2[$_] -join ','}

A B C,D E F
1 2 3,10 11 12
4 5 6,13 14 15
7 8 9,16 17 18

So, we of course need to drop that out.

0..($csv2.Count – 1) | 
%{$csv1[$_],$csv2[$_] -join ' '}

A B C D E F
1 2 3 10 11 12
4 5 6 13 14 15
7 8 9 16 17 18
6

[quote quote=145427]Great response Kiran. That definitely builds what nearownkira45 is asking for.

I’m curious though, nearownkira45, is that what you wanted? Do you have two correlating CSVs with equal and matching records?

So if the first CSV were like this:

FirstName, LastName

Peter, Griffin

Lois, Griffin

Meg, Griffin


And the second CSV were this:

VoiceActorFirst, VoiceActorLast

Seth, MacFarlane

Alex, Borstein

Mila, Kunis


You’d end up with:

FirstName, LastName, VoiceActorFirst, VoiceActorLast

Peter, Griffin, Seth, MacFarlane

Lois, Griffin, Alex, Borstein

Meg, Griffin, Mila, Kunis


Now maybe this is exactly what you wanted. Maybe not? Do let us know.[/quote]

The solution contributed by the member here so far (THANKS!) is what i generally wanted 90% of the time.

but there also times of the 10% of what I wanted is as below ( non correlating records as u mentioned):

I am outputing some of the RSOP file to powershell console, so for example:

RSOP XML output CSV1 is :

$xmldoc.rsop.ComputerResults.ExtensionData.Extension.SecurityOptions | Select "SettingNumber" | export-csv $file1

RSOP XML output CSV2 is :

$xmldoc.rsop.ComputerResults.ExtensionData.Extension.SecurityOptions.Display | Select "units" ,"DisplayNumber","DisplayString","DisplayBoolean","Name" | export-csv $file2

will output like below:

File1 CSV output:
A
1
2
3
4
5
6

File 2 CSV output:

B C D E F
7 8 9 10 11
12 13 14 15 16
17 18 19 20 21
22 23 24 25 26

The intended output will be like below:

A B C D E F
1 7 8 9 10 11
2 12 13 14 15 16
3 17 18 19 20 21
4 22 23 24 25 26
5
6

There will be 2 empty space as shown above, and the code should be able to process empty field on CSV2 or CSV1 (CSV 2 in this case)

wait…

what does 0… means ? is it a C# code?

I do not recognize it from the symbol below:

7

Something like this.

$Csv1 = Import-Csv -Path c:\Temp\CSV1.csv
$Count = 0
Import-Csv -Path c:\Temp\Csv2.csv | ForEach-Object -Process {
    $_ | Add-Member -MemberType NoteProperty -Name A -Value $Csv1.A[$Count] -Force -PassThru
    $Count++
}

PS: Not a reliable code.

8

Nope. That’s the range operator of Powershell. You can read more about operators by running Get-Help about_Operators.

9

Yes that’s true, usually the CSV values are separated by a comma, so I have used. And yes as you said, space works here. Thank you.