PowerShell Custom Table and convert row to column

user_17072562 0
Viewed 33

I have files in an array

$txt = @(P123456N123, P123456N122, P123456N223, P12asd6N122, P12asd6N124,
 P12asd6N201, P1235d6N124, P1235d6N123, P1235d6N122)

I need to get the last three digits of each file, already done. What I'm struggling with is getting a table that looks like the below:

File Name Numbers
P123456N 122, 123, 223
P12asd6N 122, 124, 201
P1235d6N 122, 123, 124

What I have so far is:

$table = @()
foreach ($file in $txt){
    $subNo = $file.basename.substring(($file.basename.length - 4),3)
    $FileName = $file.name.substring(0, 8)
    $table += [PSCustomObject] @{
    FileName = $FileName
    SubNo = $subNo
    }
}

I can then do $table | Group-Object FileName but that gives me a terribly formatted result and it isn't portable into a csv in the format listed below. Any help would be appreciated.

powershell
2 Answers

Try the following, which combines Group-Object with Select-Object and calculated properties:

@(
 'P123456N123', 'P123456N122', 'P123456N223', 'P12asd6N122', 'P12asd6N124',
 'P12asd6N201', 'P1235d6N124', 'P1235d6N123', 'P1235d6N122'
) | 
  Group-Object { $_.Substring(0, 8) } | 
  Select-Object @{ Name='File Name'; Expression='Name'}, 
                @{ Name='Numbers'; Expression={ $_.Group.Substring(8) -join ', ' } }

To-display output:

File Name Numbers
--------- -------
P123456N  123, 122, 223
P1235d6N  124, 123, 122
P12asd6N  122, 124, 201
user_453750

You could group the values using a calculated expression with Group-Object and then iterate over the grouped objects to get the desired output:

$txt = @(
    'P123456N123', 'P123456N122', 'P123456N223', 'P12asd6N122', 'P12asd6N124',
    'P12asd6N201', 'P1235d6N124', 'P1235d6N123', 'P1235d6N122'
)

$txt | Group-Object { $_ -replace '\d{3}$' } | ForEach-Object {
    [pscustomobject]@{
        'File Name' = $_.Name
        'Numbers'   = $_.Group -replace '.+(?=\d{3}$)' -join ', '
    }
}

The regex implies that all files end with 3 numeric digits.

user_153395440
Related