CODEWITHSUNDEEP
powershellactive-directory
A.Codes
A.Codes

Reputation: 197

How to have multiple values for one key in hashtable from csv

I tried to create an empty hashtable, import your CSV, group things by ID, then add each group to the hashtable.

But for some reason I am getting error:

Exception calling "Add" with "2" argument(s): "Key cannot be null.
Parameter name: key"
At line:4 char:5
+     $myHT.add($_.Name,$_.Group.Name)
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentNullException

I have then tried the following which is much closer to my desired output:

$myCSV = Import-Csv "X:\Path\To\My\CSV\File.csv"
$myCSV | Group-Object -Property ID | Select-Object -Property @{N="ID";E={$_.Name}},@{N="Name";E={$_.Group.Name -join ","}}

However, if I still have duplicates if I have the same name with the same ID in my csv. Is there anyway to get unique names only? I currently get the following:

ID    Name
1234  John, John, Jeff
1235  Jane
1236  Bob

I only want the name to be added once if it already exists.

Upvotes: 0

Views: 547

Answers (3)

A.Codes
A.Codes

Reputation: 197

I think I've figured it out. You can use Group-Object with some calculated properties to get the desired output.

$myCSV = Import-Csv "X:\Path\To\My\CSV\File.csv"
$myCSV | Select-Object -Property ID,Name -Unique | Group-Object -Property ID | Select-Object -Property @{N="ID";E={$_.Name}},@{N="Name";E={$_.Group.Name -join ","}}

Upvotes: 0

Theo
Theo

Reputation: 61068

You need to de-dupe the $_.Group.Name items using another Select-Object or Sort-Object like below:

Using your example csv:

ID,Name
1234,John
1235,Jane
1236,Bob
1234,Jeff

$myCSV = Import-Csv "D:\test\input.csv" | Group-Object -Property ID | 
    Select-Object @{Name = "ID";   Expression = {$_.Name}},
                  @{Name = "Name"; Expression = {($_.Group.Name | Select-Object -Unique) -join ","}}

$myCSV

Output:

ID   Name     
--   ----     
1234 John,Jeff
1235 Jane     
1236 Bob

Upvotes: 0

TheMadTechnician
TheMadTechnician

Reputation: 36297

If you want a hashtable, then the easiest way would be to make an empty one, import your CSV, group things by ID, then add each group to the hashtable.

$myHT = @{}
$myCSV = Import-Csv "X:\Path\To\My\CSV\File.csv"
$myCSV | Group ID | ForEach-Object {
    $myHT.add($_.Name,$_.Group.Name)
}

That won't put a pretty ', ' between names, but each key in the hashtable will have a value that's an array of names.

Upvotes: 2

Related Questions