Import data from one CSV to another CSV file - How?

Question

I have 2 CSV files almost identical. CSV1 looks like this:

Name,Surname,Town,Contry
Ann,Carlson,London,UK
Bert,Anderson,Copenhagen,DK
Gilbert,Norman, Bonn,DE
etc. etc

CSV2 looks like this:

Town,Contry
London,England
Copenhagen,Denmark
Bonn,Germany
etc. etc.

The files have exactly the same amount of rows, and I need to move/append the column Contry from CSV2 (Including data)to CSV1 or to a new CSV file.

What I have until now is:

...
$A=import-csv -path CSV1.csv|Select-object -property Name, SurName, Town
$B=Import-csv -path CSV2.csv|Select-obejct -property Country
...

Then - How do I merge these 2 variables and export them to a new file?

Check out this question & answer https://stackoverflow.com/q/60657881/4749264 Note the comments have very good solutions. However you would need something of a key field to merge / join on. — Steven, Jun 17 '20 at 19:40

score 0 · Answer 1 · answered Jun 17 '20 at 19:48

Quick 'n' dirty method. You could use a nested loop like below to get the fields and write a new file.

$A=import-csv -path CSV1.csv|Select-object -property Name, SurName, Town
$B=Import-csv -path CSV2.csv|Select-object -property Town, Contry

$resultCsv = @();
$A | ForEach-Object {
    $_person = $_;
    $B | ForEach-Object{
        $_country = $_;
        if($_person.Town -eq $_country.Town){
            Write-Host "$($_person.Name),$($_person.Surname),$($_person.Town),$($_country.Contry)";
            $resultCsv += [pscustomobject] @{"Name"=$_person.Name;"Surname"=$_person.Surname;"Town"=$_person.Town;"Contry"=$_country.Contry}
        }        
    }    
}

$resultCsv | Export-Csv -Path output.csv -NoTypeInformation

Note the $B=Import-Csv ... line ive added the 2nd field "Town" - you need this field to find a match with the field in $A

score 0 · Accepted Answer · answered Jun 17 '20 at 22:38

You can do the following, which is simple to code but not as efficient as perhaps using a lookup table. However, this does work regardless of the data order.

$csv1 = Import-Csv a.csv
$csv2 = Import-Csv b.csv

foreach ($row in $csv1) {
    $row.Contry = $csv2.where({$_.Town -eq $row.Town},'First').Contry
}

$csv1 | Export-Csv -Path new.csv -NoType

If each row of each CSV lines up perfectly to have matching Town data, then you could use array indexing of each file to process the change faster.

$csv1 = Import-Csv a.csv
$csv2 = Import-Csv b.csv

for ($i = 0; $i -lt $csv1.Count; $i++) {
    $csv1[$i].Contry = $csv2[$i].Contry
}

$csv1 | Export-Csv -Path new.csv -NoType

Import data from one CSV to another CSV file - How?

2 Answers2