Combine 2 CSV files to a new CSV

PowerShell Help
1

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!

2

Bert,
Welcome to the forum. :wave:t4:

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. :wink:

3

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.

4

I see. I guess I understood what you want. :wink:

Sounds like a good idea. You could do this either with a Select-Object and some calculated properties or with a PSCustomObject.

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

5

Can you provide an example?

6

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. :wink:

7 
$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?

8

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

9

Ok, have changed that.

1 Like
10

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:

$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:

$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:

$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:

$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:

$Result | 
    Format-Table -AutoSize

… or pipe it to whatever further step you need. :wink:

1 Like
11

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

12

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

13

I’m gald it helped. :+1:t4: :slightly_smiling_face: