PowerShell - Trying to combine data from 2 CSV files into one based on column value

Question

long time listener first time caller.

Normally I am pretty good at finding and digging and getting what I need and then modifying it to suit. This one seems to be a little trickier than what I have managed to pull off before. I am self taught in PowerShell mostly out of curiosity to see what I can do.

I am trying to create a report from data from 2 CSVs, and "most" of the data in the 2 CSVs are identical. There is simply 1 column of data in one of the CSVs that I want to add to the other one. I live regularly in the world of excel and I can do this with a formula in a matter of seconds [=VLOOKUP(H8,C:C,2,FALSE)] but accomplishing the same goal in PowerShell seems to be eluding me.

As I mentioned, I tend to try and find others who have done similar things and modify it. The best sounding one I found here ( Combine data from 2 CSV files into 1 new CSV using Powershell ) and I am still trying to play with the code on that site. Sometimes I find something and I try and stick with it too long where there might be another command that I am not familiar with that is better suited to what I should be looking at and might just need a pointer in that direction.

But here is a visual representation of what I am trying to do. And every email address in File 2, is present in File 1.

mklement0 · Accepted Answer · 2022-08-02 00:40:28Z

1

Use Import-Csv to parse both CSV input files into arrays of [pscustomobject] instances for OOP processing.
For file 2, build a hashtable that maps the Email column values to their License values.
Then use a calculated property with Select-Object to append a property to the objects parsed from file 1, using the hashtable to map each Email property to the License value from file 2; if there is no hashtable entry for a given Email property value, $null is returned, which in the context of exporting to CSV (with Export-Csv) amounts to an empty field (column value).

# Import file 2 and create a hashtable that maps each Email
# column value to the License column value.
$ht = @{}
Import-Csv File2 | ForEach-Object { $ht[$_.Email] = $_.License }

# Import file 1 and append a License column that contains
# the license value from file 2 if the Email column value matches.
Import-Csv File1 |
  Select-Object *, @{ Name='License'; Expression={ $ht[$_.Email] } } 
  # | Export-Csv ...  # complete as needed

answered Aug 2, 2022 at 0:40

mklement0

452k68 gold badges728 silver badges988 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shaun Mcbride Over a year ago

huh, even last night when I was laying down to bed I was trying to think of ways to use Select-where with ForEach to try and get the license rows to line up in the correct places. That worked, perfectly well and a far cry easier than I had expected. For some reason everything in my head had like twice as many lines to get these togeather. Thank you very much, I will have to add these to my notes for future things I try and do.

mklement0 Over a year ago

Glad to hear it, @ShaunMcbride.

Collectives™ on Stack Overflow

PowerShell - Trying to combine data from 2 CSV files into one based on column value

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related