Copy columns from one csv file to another using PowerShell

Question

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.

Please explain how the columns from 1.csv would match the columns for 2.csv, so the correct values are placed in the rows. Does `name` correspond with `username` and if so how? Edit your question and show us the first 3 or 4 lines of each csv file (sanitized of course) — Theo, Nov 15 '20 at 11:30
Hi Theo, for some reason the text on the post is messed up so I added a screenshot with an example. Thanks — Maor Zohar, Nov 15 '20 at 12:26
@Maor, use the **`{}`** "code sample" icon above your question textbox. See: [How do I format my code blocks?](https://meta.stackoverflow.com/questions/251361/how-do-i-format-my-code-blocks) — iRon, Nov 15 '20 at 12:51
But is there some kind of rule to match `user1` to username `test1` ? — Theo, Nov 15 '20 at 13:04
Does this answer your question? [CMD or Powershell command to combine (merge) corresponding lines from two files](https://stackoverflow.com/questions/27233249/cmd-or-powershell-command-to-combine-merge-corresponding-lines-from-two-files) — iRon, Nov 15 '20 at 13:05
No rules, just copy columns from on file to another regardless the type of data or numbers of rows. — Maor Zohar, Nov 15 '20 at 13:18
With no rules as to combine which user to which machinename or lastlogondate, you'll end up with bogus results.. Is that useful at all? I **strongly** suggest you think this over a bit. — Theo, Nov 15 '20 at 13:24
I don't need to associate username with machine or anything else. Just need a simple columns copy from 1 to 2 so someone can search with his eyes some data. — Maor Zohar, Nov 15 '20 at 13:31
Using this [`Join-Object`](https://www.powershellgallery.com/packages/Join) cmdlet (see also: [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026)): `Import-Csv .\1.csv | Join (Import-Csv .\2.csv)` — iRon, Nov 15 '20 at 14:14
See also: [Import Single Column CSV Files and join them into Single Three Column CSV File Powershell](https://stackoverflow.com/q/59160633/1701026) — iRon, Nov 15 '20 at 14:35

score 1 · Accepted Answer · answered Nov 15 '20 at 18:36

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

Did the job finally! Thank you very much. – Maor Zohar Nov 16 '20 at 10:34 — Maor Zohar, Nov 16 '20 at 10:34

score 1 · Answer 2 · answered Nov 15 '20 at 18:56

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

Copy columns from one csv file to another using PowerShell

2 Answers2

Linked