[Powershell] Referencing a variable ($) from another variable

[Garacesh]

Probably titled this completely wrong, but hey, let's try and explain it.

 

So suppose we have a CSV file, like the following:

 

[table=width: 200, class: outer_border]

[tr]

[td]Produce[/td]

[td]Units[/td]

[/tr]

[tr]

[td]Carrots[/td]

[td]47[/td]

[/tr]

[tr]

[td]Potatoes[/td]

[td]56[/td]

[/tr]

[tr]

[td]Apples[/td]

[td]24[/td]

[/tr]

[tr]

[td]Carrots[/td]

[td]23[/td]

[/tr]

[tr]

[td]Potatoes[/td]

[td]86[/td]

[/tr]

[tr]

[td]Apples[/td]

[td]2[/td]

[/tr]

[tr]

[td]Carrots[/td]

[td]19[/td]

[/tr]

[tr]

[td]Potatoes[/td]

[td]41[/td]

[/tr]

[tr]

[td]Apples[/td]

[td]10[/td]

[/tr]

[tr]

[td]Carrots[/td]

[td]17[/td]

[/tr]

[tr]

[td]Potatoes[/td]

[td]26[/td]

[/tr]

[tr]

[td]Apples[/td]

[td]30[/td]

[/tr]

[/table]

 

Now let's suppose I want to know the total units sold. I'd import the CSV and set int variables to zero, then loop through the CSV..

 

$CSVLoc = "Location"
$ProduceList = Import-CSV $CSVLoc
[int]$CarrotsSold = 0
[int]$PotatoesSold = 0
[int]$ApplesSold = 0
ForEach ($Entry in (Import-CSV $CSVLoc)) {

 

The snag is I'm having to define separate IF loops for each produce.

    If ($Entry.Produce -imatch "Carrots") {
       $CarrotsSold += $Entry.Units
   }
   If ($Entry.Produce -imatch "Potatoes") {
       $PotatoesSold += $Entry.Units
   }
   If ($Entry.Produce -imatch "Apples") {
       $ApplesSold += $Entry.Units
   }        
}

 

And whilst that works in practice, it just irks at me to have the same code block repeated.

 

Is there no way to condense this down to, for example.. ForEach $Entry {$($Entry.Produce)Sold += $Entry.Units}?

Edited October 9, 2019 by Garacesh

[old_n07]

How about something like this

 

$CSVLoc = "C:\Produce.csv"
$Items = Import-CSV $CSVLoc
[iNT]$TotalSold = 0
foreach ($Item in $Items){$TotalSold += $Item.units}
$TotalSold

 

It returned 381 as the value for $TotalSold

[Garacesh]

That would lump potatoes, carrots and apples all together, when I'm looking for the total of each.

i.e. 106 Carrots, 209 potatoes, 66 apples.

 

(The script is actually for totting up house points from a csv exported from SIMS, and yes since it's a CSV I could manually do it with Excel and =SUMIFS() but I'm looking to pass the results off to some other stuff and make it all automated)

[Katy]

If it were another language I'd use an array, $Items[$EntryName] += $EntryTotal; but not sure if you can do that in Powershell.

[David44]

If it were another language I'd use an array, $Items[$EntryName] += $EntryTotal; but not sure if you can do that in Powershell.

Hashtables are a thing in Powershell though.

 

Something like this could work.

 

$items = @{}

$items.bananas = 36
$items.apples = 53
$items.carrots = 24

[old_n07]

How about this

 

$CSVLoc = "C:\Produce.csv"

Import-Csv -Path $CSVLoc | 
Group-Object -Property Produce |
Select-Object -Unique -Property Name, @{
   Label = "Total";
   Expression = { ($PSItem.group | Measure-Object -Property Units -sum).Sum }
   }

 

EDIT:

 

Or if you want it all in one line

 

Import-Csv -Path "C:\Produce.csv" | Group-Object -Property Produce |Select-Object -Unique -Property Name, @{Label = "Points"; Expression = {($_.group | Measure-Object -Property Units -Sum).Sum}}

Edited October 9, 2019 by old_n07

[ThomL]

How about this?

$CSVLoc = "Location"
$ProduceList = Import-CSV $CSVLoc

$Totals = $ProduceList | Group-Object -Property Produce | ForEach {
    New-Object PSObject -Property @{
         Produce = $_.Name
         Units = ($_.Group | Measure-Object -Property Units -Sum).Sum
    }
}
$Totals

 

*EDIT, Tweaked slightly - fewer lines/tidier

 

$CSVLoc = "Location"

$Totals = Import-CSV $CSVLoc | Group-Object -Property Produce | ForEach {
    New-Object PSObject -Property @{ Produce = $_.Name; Units = ($_.Group | Measure-Object -Property Units -Sum).Sum}
}

$Totals

Edited October 9, 2019 by ThomL

[Garacesh]

How about this?

$CSVLoc = "Location"
$ProduceList = Import-CSV $CSVLoc

$Totals = $ProduceList | Group-Object -Property Produce | ForEach {
    New-Object PSObject -Property @{
         Produce = $_.Name
         Units = ($_.Group | Measure-Object -Property Units -Sum).Sum
    }
}
$Totals

 

Oooh that seems promising, but how do I reference them individually further down the line?

 

$Totals.[0], $Totals.Produce[1] etc works, but there's no guarantee the houses(/produce) will always be in the same order. so I'm wary of using that permanently (this would only ever really be a problem at the start of the year when one house hadn't accrued any points yet, but best to account for it now)

Edited October 9, 2019 by Garacesh

[ThomL]

Changed my mind, the way I posted is creating an array of multiple hash tables... not sure why - too late in the day to fix that code, so here is attempt number 2!

 

$CSVLoc = "CSV Path"
$Totals = @{}
Import-CSV $CSVLoc | Group-Object -Property Produce | ForEach {
         $Totals.Add($_.Name, ($_.Group | Measure-Object -Property Units -Sum).Sum)
}

Write-Host "`nRaw totals:"
$Totals

Write-Host "`nSorted (name) Totals:"
$Totals.GetEnumerator()  | Sort-Object -Property Name

Write-Host "`nSorted (Value) Totals:"
$Totals.GetEnumerator()  | Sort-Object -Property Value -Descending

Write-Host "`nTotal 'Carrots'"
$Totals["Carrots"]

Write-Host "`nTotal 'Potatoes'"
$Totals["Potatoes"]

Write-Host "`nTotal 'Apples'"
$Totals["Apples"]

Write-Host "`nDynamic:"
foreach ($key in $Totals.Keys){
   Write-Host "`nTotal '$key'"
   $Totals[$key]
}

 

As you can see there are a load of write-hosts that aren't needed just to show the referencing working. You can now reference items with $Totals["Apples"] for example. Actual code needed:

 

$CSVLoc = "CSV Path"
$Totals = @{}
Import-CSV $CSVLoc | Group-Object -Property Produce | ForEach {
         $Totals.Add($_.Name, ($_.Group | Measure-Object -Property Units -Sum).Sum)
}

 

OR this if you want to lose a line but have more horizontal space used?

$Totals = @{}
Import-CSV "CSV Path" | Group-Object -Property Produce | ForEach {
         $Totals.Add($_.Name, ($_.Group | Measure-Object -Property Units -Sum).Sum)
}

Any good?

Edited October 9, 2019 by ThomL

[Garacesh]

Ooh yes that works nicely.

 

S'pose it's not technically what I was after but probably a much better way of doing it than I wanted lol.