# Combine 2 CSV files to a new CSV

**URL:** <https://forums.powershell.org/t/combine-2-csv-files-to-a-new-csv/18347>\
**Category:** PowerShell Help\
**Created:** [January 13, 2022, 6:41pm UTC](https://forums.powershell.org/t/combine-2-csv-files-to-a-new-csv/18347 "2022-01-13T18:41:00Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![bvleer](https://avatars.discourse-cdn.com/v4/letter/b/13edae/32.png) [@bvleer](https://forums.powershell.org/u/bvleer)\
**Post date:** [January 13, 2022, 6:41pm UTC](https://forums.powershell.org/t/combine-2-csv-files-to-a-new-csv/18347/1 "2022-01-13T18:41:00Z")

</div>

Hi, I have 2 csv files, first one:

_PersonNr;Name;Address;Town_  
_1;Johnson;Mainstreet;London_  
_2;Peterson;Longstreet;New York_

PersonNr is unique!

Second one:  
_PersonNr;BookLended_  
_1;Cookbook_  
_1;Footballlessons_  
_2;Gardening_

PersonNr is not unique, person can have more lendings.

Output CSV should look like:

PersonNr;Name;Address;Town;Book1;Book2;Book3;Book4;Book5  
_1;Johnson;Mainstreet;London;Coobook;Footballlessons;;;_  
_2;Peterson;Longstreet;New York;Gardening;;;;_

So Book1-Book5 should be filled from CSV 2, from Book1 on, if lended books\<5 the rest of the fields should be empty.

Any suggestions how to do this in Powershell?

Thnx!

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [January 13, 2022, 6:53pm UTC](https://forums.powershell.org/t/combine-2-csv-files-to-a-new-csv/18347/2 "2022-01-13T18:53:21Z")

</div>

Bert,  
Welcome to the forum. 👋🏽

Please, when you post code, sample data, error mesages or console output format it as code using the _preformatted text_ button ( **\</\>** ). Simply place your cursor on an empty line, click the button and paste your code.  
Thanks in advance

You explanation is not entirely clear. Could you please share a sample of the resulting CSV file?

Regardless of that - what have tried so far? Please share the code you already have. So we do not have to start from scratch. 😉

---

<div class="post-metadata">

**Author:** ![bvleer](https://avatars.discourse-cdn.com/v4/letter/b/13edae/32.png) [@bvleer](https://forums.powershell.org/u/bvleer)\
**Post date:** [January 13, 2022, 7:05pm UTC](https://forums.powershell.org/t/combine-2-csv-files-to-a-new-csv/18347/3 "2022-01-13T19:05:58Z")

</div>

Thnx Olaf, changed the format of the message and added 2 lines of resulting CSV. I think I have to read both source CSV files into an array, read array from CSV1, and for every line find a matching record from array CSV2 and fill a column for that match.

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [January 13, 2022, 7:13pm UTC](https://forums.powershell.org/t/combine-2-csv-files-to-a-new-csv/18347/4 "2022-01-13T19:13:15Z")

</div>

> [@bvleer](#):
>
> added 2 lines of resulting CSV

I see. I guess I understood what you want. 😉

> [@bvleer](#):
>
> I think I have to read both source CSV files into an array, read array from CSV1, and for every line find a matching record from array CSV2 and fill a column for that match.

Sounds like a good idea. You could do this either with a `Select-Object` and some [calculated properties](https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_calculated_properties?view=powershell-7.2) or with a `PSCustomObject`.

I’d prefer the latter one as it is easier to read and to maintain in my opinion.

> **[Everything you wanted to know about PSCustomObject - PowerShell](https://learn.microsoft.com/en-us/powershell/scripting/learn/deep-dives/everything-about-pscustomobject?view=powershell-7.2)**
>
> PSCustomObject is a simple way to create structured data.

> **[Powershell: Everything you wanted to know about PSCustomObject](https://powershellexplained.com/2016-10-28-powershell-everything-you-wanted-to-know-about-pscustomobject/)**
>
> PSCustomObjects are a great tool to add into your Powershell toolbelt. Let’s start with the basics and work our way into the more advanced features. The idea behind using a PSCustomObject is to have a very simple way to create structured data. Take a...

---

<div class="post-metadata">

**Author:** ![bvleer](https://avatars.discourse-cdn.com/v4/letter/b/13edae/32.png) [@bvleer](https://forums.powershell.org/u/bvleer)\
**Post date:** [January 13, 2022, 7:17pm UTC](https://forums.powershell.org/t/combine-2-csv-files-to-a-new-csv/18347/5 "2022-01-13T19:17:29Z")

</div>

Can you provide an example?

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [January 13, 2022, 7:19pm UTC](https://forums.powershell.org/t/combine-2-csv-files-to-a-new-csv/18347/6 "2022-01-13T19:19:27Z")

</div>

I’d prefer when you try it yourself first and if you get stuck you share your code here and we’ll improve it together. 😉

---

<div class="post-metadata">

**Author:** ![bvleer](https://avatars.discourse-cdn.com/v4/letter/b/13edae/32.png) [@bvleer](https://forums.powershell.org/u/bvleer)\
**Post date:** [January 14, 2022, 6:30am UTC](https://forums.powershell.org/t/combine-2-csv-files-to-a-new-csv/18347/7 "2022-01-14T06:30:51Z")

</div>

```auto
$File1Values = @{}
$File2Values = @{}

Import-Csv -Path c:\csv1.csv -Delimiter ';' |
 ForEach-Object {
  $File1Values.Add($_.PersonNr, $_)
}
Import-Csv -Path c:\csv2.csv -Delimiter ';' |
 ForEach-Object {
  $File2Values.Add($_.PersonNr, $_)
}

```

This is what I use to load the 2 csv’s in arrays. Question: How do I loop the first one, search for matching record in 2nd one, and add that data as an extra field?

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [January 14, 2022, 6:39am UTC](https://forums.powershell.org/t/combine-2-csv-files-to-a-new-csv/18347/8 "2022-01-14T06:39:53Z")

</div>

Please, when you post code, sample data, error mesages or console output format it as code using the _preformatted text_ button ( **\</\>** ). Simply place your cursor on an empty line, click the button and paste your code.  
Thanks in advance

---

<div class="post-metadata">

**Author:** ![bvleer](https://avatars.discourse-cdn.com/v4/letter/b/13edae/32.png) [@bvleer](https://forums.powershell.org/u/bvleer)\
**Post date:** [January 14, 2022, 7:05am UTC](https://forums.powershell.org/t/combine-2-csv-files-to-a-new-csv/18347/9 "2022-01-14T07:05:57Z")

</div>

Ok, have changed that.

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [January 14, 2022, 7:13am UTC](https://forums.powershell.org/t/combine-2-csv-files-to-a-new-csv/18347/10 "2022-01-14T07:13:51Z")

</div>

PowerShell does a lot of things implicitly. So you don’t have to think about it and can focus on the important parts.

To create an object with the content of CSV files you just use Import-Csv and assign it to a variable like this:

```auto
$CSVList_1 = Import-Csv -Path c:\csv1.csv -Delimiter ';'
$CSVList_2 = Import-Csv -Path c:\csv2.csv -Delimiter ';'

```

To make the code independend from files I use the limitted data you provided in your question. First we create the data objects we want to work with:

```auto
$CSVList_1 = @'
PersonNr;Name;Address;Town
1;Johnson;Mainstreet;London
2;Peterson;Longstreet;New York
'@ |
    ConvertFrom-Csv -Delimiter ';'

$CSVList_2 = @'
PersonNr;BookLended
1;Cookbook
1;Footballlessons
2;Gardening
'@ |
    ConvertFrom-Csv -Delimiter ';' 

```

Now, since we need all items from the second CSV file belonging to one individual PersonNr in an array we group the second list for the property PersonNr:

```auto
$GroupedCSVList_2 = 
    $CSVList_2 |
        Group-Object -Property PersonNr

```

Now we can match the individual elements from the list 1 to the elements of the grouped list 2:

```auto
$Result =
foreach ($CSVItem_1 in $CSVList_1) {
    $MatchedGroup = @(($GroupedCSVList_2 | Where-Object -Property Name -EQ -Value $CSVItem_1.PersonNr).Group.BookLended)
    [PSCustomObject]@{
        PersonNr = $CSVItem_1.PersonNr
        Name = $CSVItem_1.Name
        Address = $CSVItem_1.Address
        Town = $CSVItem_1.Town
        Book1 = $MatchedGroup[0]
        Book2 = $MatchedGroup[1]
        Book3 = $MatchedGroup[2]
        Book4 = $MatchedGroup[3]
        Book5 = $MatchedGroup[4]
    }
}

```

To show and check the results you can output it to the console:

```auto
$Result | 
    Format-Table -AutoSize

```

… or pipe it to whatever further step you need. 😉

---

<div class="post-metadata">

**Author:** ![bvleer](https://avatars.discourse-cdn.com/v4/letter/b/13edae/32.png) [@bvleer](https://forums.powershell.org/u/bvleer)\
**Post date:** [January 14, 2022, 7:15am UTC](https://forums.powershell.org/t/combine-2-csv-files-to-a-new-csv/18347/11 "2022-01-14T07:15:51Z")

</div>

Thnx a lot Olaf! I am going forward with this.

---

<div class="post-metadata">

**Author:** ![bvleer](https://avatars.discourse-cdn.com/v4/letter/b/13edae/32.png) [@bvleer](https://forums.powershell.org/u/bvleer)\
**Post date:** [January 14, 2022, 7:35am UTC](https://forums.powershell.org/t/combine-2-csv-files-to-a-new-csv/18347/12 "2022-01-14T07:35:18Z")

</div>

Works as a charm, and learned a lot. Thnx again.

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [January 14, 2022, 8:10am UTC](https://forums.powershell.org/t/combine-2-csv-files-to-a-new-csv/18347/13 "2022-01-14T08:10:20Z")

</div>

I’m gald it helped. 👍🏽 🙂

---

<div class="post-metadata">

**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\
**Post date:** [May 16, 2024, 8:19pm UTC](https://forums.powershell.org/t/combine-2-csv-files-to-a-new-csv/18347/14 "2024-05-16T20:19:50Z")

</div>

Closed on May 16, 2024, 8:19pm UTC


