Month: December 2011

The fact that PowerShell gives you the ability to a build tasks in many different ways, it’s sometimes intimidating.  But, you’ll find the way to improve your coding as time goes.  I try, the best I can, to keep things simple.

So, here’s another approach on How-To “Sum” items in a CSV file:

My Data

[sourcecode language=”powershell”]
## sbData.csv:
# Date,Item,ItemType,Amount,Company
# 12/12/11,Ben Aipa 8’10,Longboard,1500,BenAipa
# 12/12/11,Walden 9’6",Longboard,1100,Walden
# 12/13/11,Channel Island 6′,Shortboard,800,ChannelIsland
# 12/13/11,Mark Richards 6’8",Shortboard,750,Surftech
# 12/14/11,Walden 7’6",Funboard,800,Walden
# – End of CSV file –
[/sourcecode]

The request is to sum the amount of the items group by Date and ItemType.  And here’s the solution:

[sourcecode language=”powershell”]
## – Working process to Sum amount in a CSV file by grouping columns:
$gdata = Import-csv sbData.csv | Group-Object -Property Date,ItemType;

## – BUild your new object with the sum items:
[Array] $newsbData += foreach($gitem in $gdata)
{
$gitem.group | Select -Unique Date,ItemType, `
@{Name = ‘SumAmount’;Expression = {(($gitem.group) | measure -Property Amount -sum).sum}}
};

## – Build back the CSV file with the Sum items
$newsbData | Export-Csv NewsbData.csv -NoTypeInformation;
ii NewsbData.csv;
[/sourcecode]

So, you can substitute the group items in ” -property ” parameter with the ones you want to be grouped on, then use  the “Select -unique …” to include the grouped items you want to list.  The magic happens when including the  “Measure” command against the already grouped items so you can sum the ‘Amount’ field.  Now, this code could become a template to sum up items when needed.

The Result:

[sourcecode language=”powershell”]
# – My CSV new Output data:
# "Date","ItemType","SumAmount"
# "12/13/11","Longboard","2600"
# "12/13/11","Shortboard","1550"
# "12/14/11","Funboard","800"
# – end of file –
[/sourcecode]

Happy PowerShelling!!

For this quick blog, let’s create a month hash object table containing the number and name of the month.  Then, we are going to query this Hash Table object for a specific month.

1. Creating our Month Hash Table:

[sourcecode language=”powershell”]
$MonthHashTable = @{ `
[int] "1" = "January"; `
[int] "2" = "February"; `
[int] "3" = "March"; `
[int] "4" = "April"; `
[int] "5" = "May"; `
[int] "6" = "June"; `
[int] "7" = "July"; `
[int] "8" = "August"; `
[int] "9" = "September"; `
"10" = "October"; `
"11" = "November"; `
"12" = "December"; `
};
[/sourcecode]

Notice in this example, we’ve assigned ‘Strong-typed’ values to most of the keys (month number) as Integer, and leaving the last three to default to a string data “data type“.

Keep in mind, in a Hash Table you have Keys, and Values.  So, we are going to search for a ‘Key’ in order to get the ‘Value’ so we can extract the Month name.

Here’s the Hash Table Syntax for finding the value using the key:
$yourHashTable.Item( [#key#] )

2. Lets go and Looking for the fourth and eleventh month name:

[sourcecode language=”powershell”]
## —————————————-
## – Getting "April":
## – Value stored as Integer for "April":
$mth = 4;

## – Bad, passing a string value when expecting an Integer
## -> no key found, nothing to return:
$MonthHashTable.Item("4");

## – Good, getting month name using Integer value
## -> keys found, a values are returned:
$MonthHashTable.Item(4);
April

$MonthHashTable.Item($mth);
April

## —————————————-
## – Getting "November":
## – Value stored as Integer for "November":
$mth = 11;

## – Bad, passing an integer value when expecting a string
## -> no key found, nothing to return:
$MonthHashTable.Item(11);

## – Bad, passing an integer value when expecting a string
## -> no key found, nothing to return:
$MonthHashTable.Item($mth);

## – Value stored as String for "November":
$mth = "11";

## – Good, getting month name using String value:
## -> key found, a value is returned:
$MonthHashTable.Item($mth);
November

[/sourcecode]

As the sample show, we need to make sure the variable value must have the proper “data type” so it can find the key.

Happy PowerShelling!!