Sorting using the sort object in PowerShell part 3

Question

Changed my code a little from part 2 example below but taking part 2 example which works great thanks to a member in stackoverflow.

[string] $Source = 'e:\Temp\DataFromSkywardEdited.csv';
[string] $Destination = 'e:\Temp\DataFromSkywardRD.csv';
[object] $SortByColNames = 'LastName','FirstName';
[string] $ShowColsByNumber = "{0},{1},{2},{3},{4},{5},{6},{7},{8}";
[object] $ColumnValueFormat = '$_.EmployeeID.Trim()', '$_.FirstName.Trim()', '$_.LastName.Trim()', '$_.Location.Trim()', '$_.Department.Trim()', '$_.TelephoneNo.Trim()', '$_.Email.Trim()', '$_.EmpTypeCode.Trim()', '$_.EmployeeTypeDescription.Trim()';

Get-Content $Source |
    ConvertFrom-Csv -Delimiter $Delimiter | 
    Sort-Object -Property $SortByColNames -Unique |
    ForEach-Object {
        # Each of the values in $ColumnValueFormat must be executed to get the property from the loop variable ($_). 
        $values = foreach ($value in $ColumnValueFormat) {
            Invoke-Expression $value
        }
        # Then the values can be passed in as an argument for the format operator.
        $ShowColsByNumber -f $values
    } | 
    Add-Content $Destination

How would I make $ColumnValueFormat to be represented by numbers instead of a column name?

So instead of:

[object] $ColumnValueFormat = '$_.EmployeeID.Trim()', '$_.FirstName.Trim()', '$_.LastName.Trim()', '$_.Location.Trim()', '$_.Department.Trim()', '$_.TelephoneNo.Trim()', '$_.Email.Trim()', '$_.EmpTypeCode.Trim()', '$_.EmployeeTypeDescription.Trim()';

To something like:

[object] $ColumnValueFormat = '$_.[0].Trim()', '$_.[1].Trim()', '$_.[2].Trim()', '$_.[3].Trim()', '$_.[4].Trim()', '$_.[5].Trim()', '$_.[6].Trim()', '$_.[7].Trim()', '$_.[8].Trim()';

TheMadTechnician · Accepted Answer · 2016-08-11 22:18:55Z

2

This really looks like you're doing it the hard way, but to directly answer your question, you would simply remove the period that preceeds the bracketed numbers. So...

[object] $ColumnValueFormat = '$_[0].Trim()', '$_[1].Trim()', '$_[2].Trim()', '$_[3].Trim()', '$_[4].Trim()', '$_[5].Trim()', '$_[6].Trim()', '$_.[7]Trim()', '$_[8].Trim()';

answered Aug 11, 2016 at 22:18

TheMadTechnician

36.5k3 gold badges48 silver badges63 bronze badges

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

2 Comments

Costa Zachariou Over a year ago

Awesome. Thank you TheMadTechnician. This is what I have been looking for. I will give it a try when I get to work later and will let you know how it went.

Costa Zachariou Over a year ago

Worked brilliantly. Thank you TheMadTechnician. Although I have another problem making it more dynamically. I have posted another question. Hoping you or someone else will be able to help. Thanks again. :-)

Community · Accepted Answer · 2017-05-23 11:44:24Z

2

How would I make $ColumnValueFormat to be represented by numbers instead of a column name?

You wouldn't. Just … no.

To trim the fields of a CSV you'd simply do something like this:

$csv = Import-Csv $Source

foreach ($record in $csv) {
  foreach ($property in $record.PSObject.Properties) {
    $property.Value = $property.Value.Trim()
  }
}

$csv | Sort-Object $SortByColNames -Unique | Export-Csv $Destination

If you had to treat different fields in different ways you'd use a switch statement to distinguish between the values:

$property.Value = switch ($property.Name) {
  'foo' { ... }
  'bar' { ... }
  ...
}

modify the fields via calculated properties:

Import-Csv $Source |
  Select-Object @{n='EmployeeID';e={$_.EmployeeID.Trim()},
    @{n='FirstName';e={...},
    ... |
  Sort-Object $SortByColNames -Unique |
  Export-Csv $Destination

or re-create the records via New-Object:

Import-Csv $Source |
  ForEach-Object {
    New-Object -Type PSObject -Property @{
      'EmployeeID' = $_.EmployeeID.Trim()
      'FirstName'  = ...
      ...
    }
  } |
  Sort-Object $SortByColNames -Unique |
  Export-Csv $Destination

In PowerShell v3 and newer you can even use the type accelerator [ordered] to have properties created in a particular order, like this:

$props = [ordered]@{
  'EmployeeID' = $_.EmployeeID.Trim()
  'FirstName'  = ...
  ...
}
New-Object -Type PSObject -Property $props

or like this:

[PSObject][ordered]@{
  'EmployeeID' = $_.EmployeeID.Trim()
  'FirstName'  = ...
  ...
}

edited May 23, 2017 at 11:44

CommunityBot

11 silver badge

answered Aug 12, 2016 at 0:16

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

1 Comment

Costa Zachariou Over a year ago

Thank you Ansgar Wiechers for your time and knowledge to respond. I will use it in future. For this solution I am already using Get-Content piped into the Sort-Object. If I used the Import CSV I would have to change quite a bit of code.

Collectives™ on Stack Overflow

Sorting using the sort object in PowerShell part 3

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related