PowerShell: Parse Non-delimited Array Into Columns

Question

I'm trying to parse an un-structured array into columns when that array is missing delimiters. The way the array looks from a data operation after inputted into a text file is below:

DC1
49600 Reachable 
49601 Reachable 
49602 Reachable 
49603 Reachable 
49604 Reachable 
49605 Reachable 
49606 Reachable 
49607 Reachable 
DC2
49600 Reachable 
49601 Reachable 
49602 Reachable 
49603 Reachable 
49604 Reachable 
49605 Reachable 
49606 Reachable 
49607 Reachable

The data array inside the an HTML email looks like this:
Screenshot #1:

I need it to be two columns instead, like this:
Screenshot #2:

What I've tried is a few things, first, assigning the array to a variable, $Result. I then out-file that to a text file and then email $Result in the body of an HTML email. What winds up happening is the email body looks like the blob of text in Screenshot #1. I then tried various "join" statements in various combinations to even worse results. After banging my head for several hours, I've decided to post here.

EDIT: Changed Subject line of question and the parts of the question where I mentioned strings - we are talking about arrays here, not strings. (Thanks Santiago)

Spaces or tabs can be delimiters as well. I'd expect you simply have to split your sample data into two sets and use ConvertFrom-Csv with the delimiter "\s". Anyway you should share the code you have so far. — Olaf
– Olaf, Commented Nov 29, 2021 at 23:23

Santiago Squarzon · Accepted Answer · 2021-11-30 01:05:56Z

3

The code is awfully complicated but should get you what you need. As you can see, it should work with multiple DCxx.

Note: The Here-String @'...'@ is there just as an example to test the code, if this is read from a file, be sure to use the -Raw switch of Get-Content.

$result = [ordered]@{}

@'
DC1
49600 Reachable 
49601 Reachable 
DC2
49602 Reachable 
49603 Reachable 
49604 Reachable 
49605 Reachable 
49606 Reachable 
49607 Reachable 
DC3
49600 Reachable 
49601 Reachable 
49602 Reachable 
DC4 
49603 Reachable
49604 Reachable 
49605 Reachable 
49606 Reachable 
49607 Reachable
'@ -split '\b(?=DC\d+)\b' -ne '' | ForEach-Object {
    
    $parse = ($_ -split '\r?\n' -ne '').ForEach('Trim')
    $header = $parse[0]
    $data = $parse[1..($parse.Count - 1)]
    $result.Add($header, $data)

}

$maxIter = $result.Keys.ForEach({$result[$_].Count}) | Measure-Object -Maximum

$toHTML = for($i = 0; $i -lt $maxIter.Maximum; $i++)
{
    $out = [ordered]@{}

    foreach($key in $result.Keys)
    {
        $out.Add($key, $result[$key][$i])
    }

    [pscustomobject]$out
}

$style = @'
<style>
* {
  font-family: Calibri, sans-serif;
  padding: 0px;
  margin: 0px;
  text-align: left;
}

table {
  border-collapse: collapse;
  width: 50%;
  font-size: 13px;
  margin: 10px;
}

th {
  background-color: #708090;
  color: white;
  font-size: 16px;
  font-weight: bold;
  padding: 5px 5px 5px 15px;
  text-align: left
}

td {
  padding: 5px 5px 5px 15px;
}

tr:nth-child(odd) {
  background-color: #f2f2f2;
}
</style>
'@

$toHTML | ConvertTo-Html -PreContent $style | Out-File ./test.html
Invoke-Item ./test.html

edited Nov 30, 2021 at 1:05

answered Nov 30, 2021 at 0:41

Santiago Squarzon

65.5k5 gold badges26 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

T-Heron Over a year ago

If I change the first line of yours to: $report = [ordered]@{} and remove the Here-String@'...'@, leaving everything else intact against my data set, results in a blank HTML file along with an error: Cannot find an overload for "Add" and the argument count: "2". Thank you for the effort you already put into this, Santiago.

T-Heron Over a year ago

Reason why I changed $Result to $Report: Since I already had $Result defined previously, my thinking was to assign the data set to a new variable, and do the post-processing against that new variable to get to the nice columnar report you demonstrated.

Santiago Squarzon Over a year ago

@T-Heron did you change all instances of $result for $report on the code? Reading the file put together with the code (after removing the here-string) should be (Get-Content path/to/file -Raw) -split '\b(?=DC\d+)\b' -ne '' | ForEach-.....

T-Heron Over a year ago

My $result is a string of data, which I'm now trying to use your code against to change it into a columnar report ($result is not actually read from a txt file, it's read from memory; I'm as of now doing away with reading from a txt file). Therefore, since I have a $result, I only need to work against that. So, I changed only your top line from $result = [ordered]@{} to $report = [ordered]@{} and changed nothing else, except for removal of the example data in between the @'...'@ (and the surrounding @ from both sides of the example data of course).

T-Heron Over a year ago

For better context, I get $result like this, which gives me that big string of data to work against, then I'm trying to run your code: $Result = Foreach($DC in $DCList){ Write-output $DC; (Test-RPC -ComputerName $DC); }

|

Collectives™ on Stack Overflow

PowerShell: Parse Non-delimited Array Into Columns

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related