Generically Transpose PowerShell Array Rows into Columns

Question

There are quite a few posts on SO that address PowerShell transposition. However, most of the code is specific to the use case or addresses data being gathered from a text/CSV file and does me no good. I'd like to see a solution that can do this work without such specifics and works with arrays directly in PS.

Example data:

Customer Name:    SomeCompany
Abbreviation:    SC
Company Contact:    Some Person
Address:    123 Anywhere St.
ClientID:    XXXX

This data is much more complicated, but I can work with it using other methods if I can just get the rows and columns to cooperate. The array things that "Name:" and "SomeCompany" are column headers. This is a byproduct of how the data is gathered and cannot be changed. I'm importing the data from an excel spreadsheet with PSExcel and the spreadsheet format is not changeable.

Desired output:

Customer Name:, Abbreviation:, Company Contact:, Address:, ClientID:
SomeCompany, SC, Some Person, 123 Anywhere St., XXXX

Example of things I've tried:

$CustInfo = Import-XLSX -Path "SomePath" -Sheet "SomeSheet" -RowStart 3 -ColumnStart 2
$b = @()
foreach ($Property in $CustInfo.Property | Select -Unique) {
    $Props = [ordered]@{ Property = $Property }
    foreach ($item in $CustInfo."Customer Name:" | Select -Unique){ 
        $Value = ($CustInfo.where({ $_."Customer Name:" -eq $item -and 
                    $_.Property -eq $Property })).Value
        $Props += @{ $item = $Value }
    }
    $b += New-Object -TypeName PSObject -Property $Props
}

This does not work because of the "other" data I mentioned. There are many other sections in this particular workbook so the "Select -Unique" fails without error and the output is blank. If I could limit the input to only select the rows/columns I needed, this might have a shot. It appears that while there is a "RowStart" and "ColumnStart" to Import-XLSX, there are no properties for stopping either one.

I've tried methods from the above linked SO questions, but as I said, they are either too specific to the question's data or apply to importing CSV files and not working with arrays.

@Eris If I understand correctly, since it is being imported from an excel spreadsheet, it uses the entire row as a record. — McKenning, Oct 27 '16 at 15:46
Given that your input "record" consists of 5(?) rows, how do you know that it "ends" at `ClientId`? If you don't have a separator between groups of values you want to make into an output object, it will be impossible to split. — Eris, Oct 27 '16 at 15:49

score 0 · Answer 1 · answered Oct 27 '16 at 17:22

I was able to resolve this by doing two things:

Removed the extra columns by using the "-Header" switch on the Import-XLSX function to add fake header names and then only select those headers.

$CustInfo = Import-XLSX -Path "SomePath" -Sheet "SomeSheet" -RowStart 2 -ColumnStart 2 -Header 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18 | Select "1","2"

The downside to this is that I had to know how many columns the input data had -- Not dynamic. If anyone can provide a solution to this issue, I'd be grateful.

Flipped the columns and headers with a simple foreach loop:

$obj = [PSCustomObject]@{}
ForEach ($item in $CustInfo) {
   $value = $null
   $name = $null
   if ($item."2") { [string]$value = $item."2" }
   if ($item."1") { [string]$name = $item."1" }
   if ($value -and $name) {
       $obj | Add-Member -NotePropertyName $name -NotePropertyValue $value
   }
}

I had to force string type on the property names and values because the zip codes and CustID was formatting as an Int32. Otherwise, this does what I need.

Generically Transpose PowerShell Array Rows into Columns

1 Answers1