Sorting using the sort object in PowerShell part 2

Question

I am still stuck trying to sort and remove duplicates from a CSV file (separated by commas) containing some data into a new CSV file dynamically. Got further with help from one of the stack overflow members for the -Property $SortByColNames but now my issue is in the ForEach section. I get it to work if I hard code the ForEach's numbered part in curly brackets and the column names after "-f" but if I try to make the two into variables passed from parameters in a function it gives me a CSV file containing the physical string of the -f section.

I have tried making $ShowColsByNumber a string and an object variable and $ColumnValueFormat also as a string and an object. Neither work.

Can anyone please help.

Below is the code that works and after is the code that does not:

Works

[string] $Source = 'e:\Temp\DataFromSkywardEdited.csv';
[string] $Destination = 'e:\Temp\DataFromSkywardRD.csv';
[object] $SortByColNames = 'LastName','FirstName';

Get-Content $Source |
      ConvertFrom-Csv | 
    Sort -Property $SortByColNames -Unique |
    ForEach {"{0},{1},{2},{3},{4},{5},{6},{7},{8}" -f $_.EmployeeID.Trim(), $_.FirstName.Trim(), $_.LastName.Trim(), $_.Location.Trim(), $_.Department.Trim(), $_.TelephoneNo.Trim(), $_.Email.Trim(), $_.EmpTypeCode.Trim(), $_.EmployeeTypeDescription.Trim()} | 
        Add-Content $Destination

Does not work

[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 -Property $SortByColNames -Unique |
                ForEach {$ShowColsByNumber -f $ColumnValueFormat} | 
                    Add-Content $Destination;

Your replacement won't work because you're inserting literal strings for "$_.EmployeeID.Trim()". This is not invoked and expanded to add to your string. — Chris Dent
– Chris Dent, Commented Aug 11, 2016 at 14:33

Chris Dent · Accepted Answer · 2016-08-11 15:55:27Z

4

This should work.

[String[]]$ColumnValueFormat = 'EmployeeID', 'FirstName', 'LastName' # Truncated, example.

Get-Content $Source |
    ConvertFrom-Csv -Delimiter $Delimiter |
    Sort-Object -Property $SortByColNames -Unique |
    Select-Object $ColumnValueFormat |
    ForEach-Object {
        $_.PSObject.Properties.Value.Trim() -join ','
    }

Using the original values requires something like this. Each of the values in your array must be executed each time you loop. One of the ways to do that uses Invoke-Expression, this isn't really considered good practice.

[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

edited Aug 11, 2016 at 15:55

answered Aug 11, 2016 at 14:42

Chris Dent

4,2801 gold badge17 silver badges15 bronze badges

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

5 Comments

Costa Zachariou Over a year ago

Hi Chris. Thank you for getting back to me. I am still a little confused. I replaced the $ColumnValueFormat in the following line: ForEach {$ShowColsByNumber -f $ColumnValueFormat} | with the following: Select-Object and its ForEach-Object in curly brackets but it is complaining about the $ShowColsByNumber. How do I go about it?

Chris Dent Over a year ago

Sort-Object is already ordering the values for you in this example, the part in ForEach-Object is dealing with trim. You cannot use the version you have as it is without some rather messy work to turn each of the values in $ColumnValueFormat into a ScriptBlock. Let me show you.

Chris Dent Over a year ago

Sorry, I meant Select-Object is ordering the values (ordering the columns, not ordering the rows). Anyway, I've added one possible way of getting the original to work as is. I wouldn't consider using Invoke-Expression to be good practice, more of a last resort.

Costa Zachariou Over a year ago

It works great. Thank you, you are swesome :-) If you have time and/or want to add a comment about how else you would do this with out using the Invoke-Expression it would be appreciated. Thanks again.

Costa Zachariou Over a year ago

Hi Chris. One more question. How would you specify $ColumnValueFormat numerically? So instaed of: [object] $ColumnValueFormat = '$_.EmployeeID.Trim()', FirstName.Trim()'... It would be something like: [object] $ColumnValueFormat = '$_.[0].Trim()', '$_.[1].Trim()' There maybe CSV files without column headings.

Collectives™ on Stack Overflow

Sorting using the sort object in PowerShell part 2

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related