0

I have an XML that I convert to a CSV, that works fine and outputs rows into the CSV. I'd like to format the csv vertically vs horizontally.

$xml = [xml](Get-Content "C:\temp\test.xml")
$data = $xml.SelectNodes("//EmployeeImport/Employee") | Select-Object -Property *
$data | Export-Csv -Path "c:\temp\employee.csv" -NoTypeInformation 
$csvFile = "c:\temp\employee.csv"
$folderPath = Split-Path -Parent $csvFile
$csv = Import-Csv -Path $csvFile -Delimiter "," 

foreach ($record in $csv) {
    $OutFileName = join-path -Path $folderPath -ChildPath $($record.FirstName + " " + $record.LastName  +  $record.HireDate.replace('/','_') + ".csv");
    $record | select-object * | export-csv -NoTypeInformation -Path $OutFileName -Delimiter ",";
}

Current Output

Name Social
Mickey 123456

Desired Output

Name Mickey
Social 123456
kometen
  • 6,536
  • 6
  • 41
  • 51
FishNowWorkLater
  • 5
  • 2

1 Answers1

0

This Join-Object Module is basically meant to two join object lists based on a related property (see also: In Powershell, what's the best way to join two tables into one? but as it might also be used to do a side-by-side join (by omitting the related property define by the -on parameter). This can be useful to transpose tables as e.g. a csv file:

Install-Module -Name JoinModule -MinimumVersion 3.8.1

Sample Csv

$Csv = @'
"@","A","B","C"
"1","D","E","F"
"2","G","H","I"
"3","J","K","L"
'@

As Matrix

Considering a Csv file (or string) as a matrix were the header row has the same format as each following data row, you might just separate each field on the comma delimiter:

$Table = @()
# Get-Content .\My.csv |Foreach-Object { ...
$Csv -Split '\r?\n' |Foreach-Object { $Table = $Table |FullJoin $_.Split(',') }
$Transposed = $Table |Foreach-Object { $_ -Join ',' }

Explanation

  • $Csv -Split '\r?\n' splits the multiline string in an array of lines which is the default behavior of the Get-Content cmdlet
    • Note: this approach doesn't take in account that the data might also contain a comma, e.g.:
      "Name","Social"
      "Mickey","123,456"
  • $Table = $Table |FullJoin $_.Split(',') full joins each row as a column to the prior row and output a list of PSObject collections ( System.Collections.ObjectModel.Collection[psobject]]) similar to the foreach and where methods
    • Note: there is a bug in the versions prior to 3.8.1 of the Join-Object cmdlet that doesn't correctly implement the start from an empty pipeline ($Table = @())
  • $Table |Foreach-Object { $_ -Join ',' } iterates through each row of PSObject collections and joins each item (using the -join operator) as a single string.

As List of PowerShell objects

$Data = ConvertFrom-Csv $Csv # = $xml.SelectNodes("//EmployeeImport/Employee")
$Names = $Data[0].PSObject.Properties.Name
$Last = $Names.get_Count() - 1
$Table = @() |FullJoin $Names[1..$Last] -Name $Names[0]
$Data |Foreach-Object {
    $Values = $_.PSObject.Properties.Value
    $Last = $Values.get_Count() - 1
    $Table =  $Table |FullJoin $Values[1..$Last] -Name $Values[0]
}

Explanation
In this case the input is considered to contain a list of PowerShell objects. Where it is common that each row is a PowerShell object and each property in the object represents a column.

  • This means that the header row is actually contained by the first (and all other rows) in the $Data[0].PSObject.Properties.Name
  • The same counts for rebuilding the output where the first item should be considered to be the property name of each object. This is done with the command: ... |FullJoin $Names[1..$Last] -Name $Names[0]
iRon
  • 20,463
  • 10
  • 53
  • 79