CODEWITHSUNDEEP
excelpowershellcsvmergemultiple-columns
Maor Zohar
Maor Zohar

Reputation: 592

Copy columns from one csv file to another using PowerShell

I have two csv files with several columns on each.

I want to copy 1.csv columns to 2.csv file. For example: 1.csv contains the follow: name, lastlogondate

user1 15/11/2020 user2 12/11/2020

2.csv contains the follow: machinename, username, status

win10-test1 tst1 available win10-test2 tst2 connected

I would like to add 1.csv file columns to 2.csv file or create a new csv/excel file with all columns as this: name, lastlogondate, machinename, username, status

user1 15/11/2020 win10-test1 tst1 available user2 12/11/2020 win10-test2 tst2 connected

(Tried to look for a solution but no luck) Thanks.

enter image description here

Upvotes: 2

Views: 2677

Answers (2)

Esperento57
Esperento57

Reputation: 17472

You can use Join-object command too :

#import 1.csv and add Index counter column as Key
$i=0
$Content1=import-csv "c:\temp\1.csv" -Delimiter "," | %{$i++;Add-Member -InputObject $_ -MemberType NoteProperty -Name "Index" -Value $i; $_}


#import 2.csv and add Index counter column as Key
$i=0
$Content2=import-csv "c:\temp\2.csv" -Delimiter "," | %{$i++;Add-Member -InputObject $_ -MemberType NoteProperty -Name "Index" -Value $i; $_}

#join csv files on Index Key => modify parameter Type for equijointure if necessary
Join-Object -Left $Content1 -Right  $Content2 -LeftJoinProperty "Index" -RightJoinProperty "Index" -Type AllInLeft | Export-Csv "c:\temp\3b.csv" -Delimiter "," -NoType

Upvotes: 1

Esperento57
Esperento57

Reputation: 17472

I suggest the following solution but starting from the following assumptions (it's up to you to correct the program otherwise):

A.The delimiter of the two csv files is the comma

B.The "join" made between the two files is the equivalent of a "left join" in SQL. If the 2.csv file has more lines than the 1.csv file, the extra lines will be ignored

#get content of first csv with delimiter is coma
$Content1=import-csv "c:\temp\1.csv" -Delimiter ","

#get content of second csv with delimiter is coma
$Content2=import-csv "c:\temp\2.csv" -Delimiter ","

#get all columns to 2.csv
$MemberToGet=Get-Member -InputObject $Content2[0] -MemberType NoteProperty | sort Name

$i=-1

#For every row of 1.csv i take the same position row of content 2.csv and add all property and his value to current object, and finally export result
$Content1 | %{
$CurrentRowObject=$_
$i++
$MemberToGet | %{
                    $Name=$_.Name
                    Add-Member -InputObject $CurrentRowObject -MemberType NoteProperty -Name $Name -Value $Content2[$i]."$Name"
                 }

#send to output the result object
$CurrentRowObject


} | export-csv "c:\temp\3.csv" -NoType

Upvotes: 1

Related Questions