Home > Back-end >  Powershell convert columns of txt file to JSON format
Powershell convert columns of txt file to JSON format

Time:10-27

I have the following output (in a file .txt)

Average:     CPU    %usr   %nice    %sys %iowait    %irq   %soft  %steal  %guest  %gnice   %idle
Average:     all    0.21    0.00    0.08    0.00    0.00    0.00    0.00    0.00    0.00   99.71
Average:       0    1.01    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00   98.99
Average:       1    0.00    0.00    0.52    0.00    0.00    0.00    0.00    0.00    0.00   99.48

I need the number of CPU (1rst column) and the last one (%idle) to be parsed in a Json.

For example

[
  {
    "Num": "all",
    "Data": "99.71"
  },
  {
    "Num": "0",
    "Data": "98.99"
  },
  {
    "Num": "1",
    "Data": "99.48"
  }
]

but Instead I get the following output:

[
  "Num_all 99.71",
  "Num_0 98.99",
  "Num_1 99.48"
]

The Json is valid but unfortunately this is not the output expected.

My code:

$file = Join-Path $PSScriptRoot cpu.txt

#Create .temp file with the stats  
$cpu_stats = mpstat -P ALL 1 2 | grep Average > $file

#Print the first column (cpu num) and 11th (Average CPU)
$info_json = Get-Content $file | Select-object -skip 1| Foreach {"Num_$(($_ -split '\s ',12)[1,11])"} | ConvertTo-Json

#Print result (Json)
Write-Output $info_json

How can I get that with PowerShell? Thanks in advance

CodePudding user response:

You need to construct objects ([pscustomobject] instances) with .Num and .Data properties to get the JSON format you want:

Get-Content $file | 
  ForEach-Object {
    # Extract the column values of interest...
    $cpu, $idle = (-split $_)[1, -1]
    # ... and use them to construct and output a custom object.
    [pscustomobject] @{
      Num = $cpu
      Data = $idle
    }
  } | ConvertTo-Json
  • I've used the unary form of the -split operator to split each line by nonempty runs of whitespace (while ignoring leading and trailing whitespace).

  • Since the %idle column is the last one, it can be referred to with index -1.

  • $cpu, $idle = ... is a multi-assignment that assigns the two array elements returned individually to the specified variables.

  • [pscustomobject] @{ ... } is syntactic sugar for constructing a [pscustomobject] instance via a hashtable (@{ ... }).[1]


[1] Note that no separate hashtable is actually constructed when using this syntax. Also, unlike with true hashtables, the order of properties (keys) as specified is preserved in this case.

  • Related