PS1: Transposing a vertical list of records

14 Oct

Honestly, I don’t really know what term to call this other than “a vertical list of records”. What I mean a set of records that are listed vertically instead of having its field values in columns. Below table is what it looks like:



This output comes out from a really old CLI which extracts the inventory of hosts. The problem with the CLI is that it can only extract one host at a time and it displays in this vertical list format. To extract 100 host, you need to call the CLI 100 times and dump the output into a file, which is what it ended up looking like in the above.

It is important to note the following about the quality of the input data listed below. If the quality is random and doesn’t follow rules than the standard iteration code will not work:

  • All fields for each hosts are listed in order for each record
  • Fields with blank values are also list against their field name

The quick and dirty method is to get each line and reassemble them into columns in a delimited format. I use tab delimited as this opens in a spreadsheet well-formatted.

$hostlist = get-content ".\hostlist.txt"
$hostname = ""
$fldvals = ""
foreach ($hostrec in $hostlist) {

  # skip all comment and blank lines
  if ($hostrec -match "---" -or $hostrec -eq "") { continue;}

  $hostval = $hostrec.split(":",2)

  # create new record line as this is a new host name
  if ($hostname -ne $hostval[0]) {

    # flush previous host record to file
    if ($fldvals -ne "") { $fldvals | out-file .\hostrecords.txt -append ;
        $fldvals = ""}

    $hostname = $hostval[0]
    # create a new record for new host
    $fldvals = "$hostname`t" + $hostval[1].split("=",2)[1] + "`t"


  else {
    # append new field value to the end of the line
    $fldvals = $fldvals + $hostval[1].split("=",2)[1] + "`t"


The problem with the code above is that the field names are not listed in the fist line of the output file, like a proper delimited file. The problem with the above code is that the feild names will be duplicated for each record it reads. Since this is a quick a dirt implementation, the field names are not read.

To extract the field names, just change “$hostval[1].split(“=”,2)[1]” to “$hostval[1].split(“=”,2)[0]”. You can add additional logic to generate the field names in the first line and the record values on the next line and extract only the values for subsequent records. However, a better solution is using a hash table as described below.

The second version of this uses hash table. The output is a fully formatted tab delimited file with field names at the top. You will notice that the first field name is “@HOSTNAME”. The reason for the “@” sign is that the hash table doesn’t store key pairs in order. So when using GetEnumerator(), the field name doesn’t appear on the top of the field and you cannot proper extract the field names and values in top down sequence. So I have to resort to a symbol and sort the records before extracting them into a file.

$hostdump = get-content ".\input_hostlist.txt"
$hosts = @{"@HOSTNAME"=""}
Write-Host "Running ..."

foreach ($hosttxt in $hostdump) {
  if ($hosttxt -match "---" -or $line -eq "") { continue;}

  $hostrec = $hosttxt.split(":",2)
  $hostname = $hostrec[0]
  $hostval = $hostrec[1].split("=",2)[1]  # get field value
  $hostfld = $hostrec[1].split("=",2)[0]  # get field name

  # Get the field names
  $prev_fldvals = $hosts.'@HOSTNAME'
  # Only append field names if not duplicate.
  if ($hosts.'@HOSTNAME' -notmatch $hostfld) {
    $hosts.Set_Item("@HOSTNAME", $prev_fldvals + $hostfld + "`t")

  if (!$hosts.ContainsKey($hostname)) {
    $hosts.Add($hostname, $hostval + "`t")   # add new record

  else {
    $prev_hostval = $hosts.Get_Item($hostname) # get the previous set of field value strings
    $hosts.Set_Item($hostname, $prev_hostval + $hostval + "`t") # append new field value


$hosts.GetEnumerator() | Sort-Object | % { $_.Key + "`t" + $_.Value | out-file  .\extracted_output.txt -append}
Write-Host "DONE!!" 


Leave a comment

Posted by on October 14, 2016 in powershell



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: