Powershell convert list formatted record into table format

Question

I'm looking for some powershell to convert Listed properties into a CSV or Table, not all records in the list have the same Properties but the "Index" property signifies a new record.

INPUT

Index:1  
FirstName:Bob  
LastName:Smith  
DOB:1/1/1970  
Index:2  
FirstName:John  
DOB:1/1/1971  
Index:3  
LastName:Jones  
DOB:1/1/1972

OUTPUT

FirstName,LastName,DOB  
Bob,Smith,1/1/1970  
John,,1/1/1971  
,Jones,1/1/1972  

Any help is greatly appreciated. Thanks

Answer 1

An other example.

$arrInputFile = Get-Content -Path "C:\temp\input.txt"
$strCSVExportPath = "C:\temp\Export.csv"

$arrCSVHeader = @()
Foreach ($strLine in $arrInputFile) {
    If ($arrCSVHeader -notcontains $strLine.Split(":")[0]) {
        $arrCSVHeader += $strLine.Split(":")[0]
    }        
}

$arrCSVOutput = @()
$objCurrentIndexBlock = $null
Foreach ($strLine in $arrInputFile) {    
    If ($strLine.Split(":")[0] -eq "Index") {
        If ($objCurrentIndexBlock -ne $null) {
            $arrCSVOutput += $objCurrentIndexBlock
        }

        $objCurrentIndexBlock = "" | Select-Object -Property $arrCSVHeader
    }

    $objCurrentIndexBlock.$($strLine.Split(":")[0]) = $strLine.Split(":")[1].Replace(" ",$null)

}
$arrCSVOutput += $objCurrentIndexBlock

$arrCSVOutput | Export-Csv -Path $strCSVExportPath -NoClobber -NoTypeInformation -Force

Answer 2

You can run it through a ForEach and look for Index to create an object, and then add members to it until it runs into Index again, at which point it outputs the previous object, and starts a new one. Then after that you add the last object to the array, and you're set. Then just output to CSV or whatever.

    $RawData = Get-Content C:\Path\To\input.txt
$Record = ""
$Array = $RawData | Where{$_ -Match "(.+?):(.+)"} | ForEach{If($Matches[1] -eq "Index"){if(![string]::IsNullOrWhiteSpace($Record)){$Record};$Record = [PSCustomObject]@{"Index"=$Matches[2].trim()}}Else{Add-Member -InputObject $Record -MemberType NoteProperty -Name $Matches[1] -Value $Matches[2].trim()}}
$Array += $Record
$Props = $Array | ForEach{$_ | Get-Member -MemberType Properties | Select -Expand Name} | Select -Unique
$Props | Where{($Array[0]|Get-Member -MemberType Properties | Select -Expand Name) -notcontains $_} | ForEach{$Array[0]|Add-Member $_ $null}
$Array | Export-Csv C:\Path\To\File.csv -NoTypeInformation

Edit: I realized my first answer had a pitfall where if the first record is missing a field (say, there was no LastName) that it wouldn't display that field for any of the following records. I have rectified that by getting a list of all unique fields from each record, and adding any missing ones to the first record with a null value.

Edit2: After looking at both Patrick's and my answers I realized that his runs much faster, so have created a modified version combining both our answers. Some technique of object creation taken from his, and line parsing taken from mine:

$RawData = Get-Content 'C:\temp\input.txt'
$Record = ""
$Array = @()
$Props = $RawData -replace "(.+?):.*","`$1"|select -Unique
ForEach($Line in $RawData){
    $Line -Match "(.+?):(.+)" | Out-Null
    If($Matches[1] -eq "Index"){
        If([string]::IsNullOrEmpty($Array[0])){$Array = @($Record)}else{$Array += $Record}
        $Record = ""|Select -Property $Props
        $Record.Index = $Matches[2].trim()
    }Else{
        $Record.($matches[1]) = $Matches[2].trim()
    }
}
$Array | Export-Csv 'C:\temp\export2.csv' -NoTypeInformation