Costa Zachariou Costa Zachariou - 3 months ago 36
PowerShell Question

Sorting using the sort object in PowerShell part 2

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;

Answer

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
Comments