Import-Csv and sort by end of column cell value

Hi,

I’m not sure if it’s possible but it usually is with PowerShell :wink:

Anyway. I’m importing a csv file using the below:

[array]$AllClientsBody = @()
Foreach ($Path in $TargetPaths) { #}
# Extract the client name from path
$Client = ($Path -split ‘\’)[6]
# Import CSV file, create custom headers, skip the csv headers row, get the last row data only & convert to HTML formatting
$csvHTML = Import-Csv -Path “$Path\TotalMonthlyTransactions.csv” -Header “Month”,“Transactions” | Select-Object -Skip 1 | Select-Object -Last 1 | ConvertTo-Html -Fragment
# Construct the email body and populate with imported information
$MailBody = “Total Monthly Transactions for $Client</br>$csvHTML</br>”
# Add the client email body to the array so it can be sent in one email
$AllClientsBody += $MailBody
}

The problem is that the ordering in the first column is like this:

January 2017
January 2018
January 2019
January 2020
February 2017
February 2018
…etc.

Is some jiggery pokery that allows me to sort this on the latter part of the value and then the first?

I found this old, unanswered post in a Suggested Topics section. It provided a challenge in sorting source data that are formatted in a less than practical manner. I hope that this late response helps others who are facing similar challenges where source data are presented as “It is what it is”.

Process

  • Prep: Created a “DimDate” csv file of two elements: date and transactions for the last 7 years.
  • Prep: Split “DimDate” into groups by a range of years to mimic different client log files.
    • Sorted the data as to not be in the 1-12 month sequence.
      • See Prep Code.
  • Created an array of paths (Alice, Bob, and Charlie standing in for computer names).
  • Tried to follow the OP’s brief as near as I could.

Remarks

  • I assumed “Total Monthly Transactions for…" to mean something like Last Logged Month/Year.
  • Split-Path was used to ensure correct parsing (assuming last piece from the brief).
  • A switch assigns a client with a prepared subset of the source data.
    • This would not occur in an actual foreach $path.
  • The regex group capture in $d was essential in setting class properties effectively.
  • The $Matches hash is passed into the class ctor where the named elements set properties and feed a method.
    • The month number, generated by GetMonthNumber() provided for correct sorting later.
  • I didn’t follow the OP’s post regarding the html-for-email, but sent the data set to an html file so I could see the interpreted HTML.

Demo Code

using namespace System.Collections;

$paths = @('A:\logs\Alice\log.csv','A:\logs\Bob\log.csv','A:\logs\Charlie\log.csv');

Class DimDate
{
    [string] $Key;
    [string] $ClientName;
    [int] $MonthNbr;
    [string] $Year;
    [int]    $NbrTransactions;

    DimDate($q1,$q2)
    {
        $this.Key = $q2[0];
        $this.ClientName = $q1;
        $this.Month = $q2.month;
        $this.MonthNbr = $this.GetMonthNumber($q2.month);
        $this.Year = $q2.year;
    }

    [int]GetMonthNumber($q3)
    {
        return ([array]::indexof([cultureinfo]::CurrentCulture.DateTimeFormat.MonthNames,$q3)+1);
    }
}

$clientT = [ArrayList]::new();
foreach ($i in $paths)
{    
    $Client = ((Split-Path $i -Parent) -split "\\")[-1]
    switch ($Client)
    {
        'Alice' {$j = $x;break}
        'Bob' {$j = $y;break}
        'Charlie' {$j = $z;}
    }

    foreach($d in $j)
    {
        $d.Month -match '(?<month>\w+) (?<year>\d+)'|Out-Null;
        $e = [DimDate]::new($Client,$Matches);
        $e.NbrTransactions = $d.Transactions;
        [void]$clientT.Add($e);
    }    
}

$Body = [ArrayList]::new();
foreach($m in $clientT|Select-Object ClientName -Unique)
{
    $msg = ($clientT).Where({$_.ClientName -eq $m.ClientName})|Sort-Object Year,MonthNbr|Select-Object ClientName,Key,NbrTransactions -Last 1
    [void]$Body.Add($msg);
}
return $Body|ConvertTo-Html -Fragment|Out-File A:\results.html;

Results

Html Output
image

Prep code

$a = Import-Csv A:\DimDate.csv -Header "Month","Transactions"; # code not provided for DimDate.csv construction.
$x = $a.Where({$_.Month -match "201[456]"})|Sort-Object Month # Alice
$y = $a.Where({$_.Month -match "201[789]"})|Sort-Object Month # Bob
$z = $a.Where({$_.Month -match "202[01]"})|Sort-Object Month # Charlie

A simple calculated property would probably be enough in this case :wink: … so instead of this

$csvHTML = Import-Csv -Path "$Path\TotalMonthlyTransactions.csv" -Header 'Month', 'Transactions' |
    Select-Object -Skip 1 |
        Select-Object -Last 1 | 
            ConvertTo-Html -Fragment

You could use something like this:

$csvHTML = Import-Csv -Path "$Path\TotalMonthlyTransactions.csv" -Header 'Month', 'Transactions' | 
    Select-Object -Property *,@{Name = 'Year';Expression={($_.Month -split ' ')[1]}} | 
        Sort-Object -Property Year | 
            Select-Object -ExcludeProperty Year |
                Select-Object -Skip 1 | 
                    Select-Object -Last 1 |
                        ConvertTo-Html -Fragment
2 Likes

Many thanks @Olaf. This will have me going over code to see where I can optimize. I found the following link that goes into detail about taking advantage of the pipeline.

Redgate Hub: Ins and Outs of the PowerShell Pipeline

1 Like