Text file data to table format

Hi,

I have a text file with below data. If we see this data the words “buildNumber”, “result”, “name” and “displayName” are repeating for every 4 lines. now i want to convert these 4 words into columns and the correpsonding values should come under those columns. how can do this in powershell. Please help… i searched google for the code, but no luck.

"buildNumber":  "20210303.1",
"result":  "succeeded",
"name":  "LDAP Module",
"displayName":  "Rana-R.reddy",
"buildNumber":  "20210301.2",
"result":  "succeeded",
"name":  "LDAP Module",
"displayName":  "phil.ben",
"buildNumber":  "20210301.1",
"result":  "succeeded",
"name":  "LDAP Module",
"displayName":  "phil.ben",
"buildNumber":  "20210303.4",
"result":  "succeeded",
"name":  "LDAP Module",
"displayName":  "Rana-R.reddy",

the expected ouput should be like below:

buildNumber result name displayName
20210303.1 succeeded LDAP Module Rana-R.reddy
20210301.2 succeeded LDAP Module phil.ben
20210301.1 succeeded LDAP Module phil.ben
20210303.4 succeeded LDAP Module Rana-R.reddy

You might get some inspiration here:

I don’t think you’ll find code on the internet that specifically solves your unique problem. Olaf gave a good example that might inspire you.

I’d break down your solution like this:

  1. Read in all the data from the file. Use Get-Content
  2. Remove extraneous characters from each line. Use a loop to iterate over each line. Get-Content will already make each line it’s own item in an array. You can use -replace to remove the extra characters.
  3. Separate the property from it’s value. On each line (in the loop), use -split to split on the ": "
  4. Assign the property and value to a custom object. Use [pscustomobject] with a hashtable.
  5. If the property is displayName, add the object to a collection and repeat for all the rows.

This should get you started. Here’s the relevant documentation to review using Get-Help.
Get-Content
ForEach-Object
about_object_creation (look for [pscustomobject])
about_arrays
about_split
about_regular_expression (look for -replace operator)

Post back some code and maybe I can help you out.

try this

$txt = "$home\desktop\1.txt"

$lines = Get-Content -Path $txt
$hts = [System.Collections.ArrayList]::new()

for ($i = 0; $i -lt $lines.Length; $i++) {
    if ($i % 4 -eq 0){
        $ht = [System.Collections.Hashtable]::new()
    }

    $two = $lines[$i].Split(':')
    $pro = $two[0].Trim().Trim(',').Trim('“').Trim('”')
    $val = $two[1].Trim().Trim(',').Trim('“').Trim('”')
    $ht.Add($pro,$val)

    if ($i % 4 -eq 3){
        $null = $hts.Add($ht)
    }
}

$head = "buildNumber`tresult`tname`tdisplayName"
$strs = $hts | ForEach-Object {
    $str = $_.buildNumber + "`t" + $_.result + "`t" + $_.name + "`t" + $_.displayName
    $str
}

Set-Content -Path "$home\desktop\ok.txt" -Value $head
Add-Content -Path "$home\desktop\ok.txt" -Value $strs

result

$data = Get-Content .\Textfiledatatotableformat.txt

$buildNumber    = [System.Collections.ArrayList]@()
$result         = [System.Collections.ArrayList]@()
$name           = [System.Collections.ArrayList]@()
$displayName    = [System.Collections.ArrayList]@()

foreach ($d in $data) {
    $d = $d -replace '(“)|(”)|(,)', ''
    $s = $d -split ': '
    switch ($s[0]) {
        "buildNumber"   {$buildNumber.Add($s[1])}
        "result"        {$result.Add($s[1])}
        "name"          {$name.Add($s[1])}
        "displayName"   {$displayName.Add($s[1])}
        Default {}
    }
}

$dataObject = for ($i = 0; $i -lt $buildNumber.Count; $i++) {
    [PSCustomObject]@{
        buildNumber     = $buildNumber[$i]
        result          = $result[$i]
        name            = $name[$i]
        displayName     = $displayName[$i]
    }
}

$ dataObject returns:

buildNumber result    name        displayName
----------- ------    ----        -----------
20210303.1  succeeded LDAP Module Rana-R.reddy
20210301.2  succeeded LDAP Module phil.ben
20210301.1  succeeded LDAP Module phil.ben
20210303.4  succeeded LDAP Module Rana-R.reddy

However, I am curious if there is a way to build a PSCustomObject in the switch block.

Since the properties are always listed in the same order with displayname being last…

$obj = @{}
Get-Content $Testfile.FullName | ForEach-Object {
    $property, $value = $_ -replace '"|“|”|,' -split ': '
    $obj.Add($property,$value)
    if ($property -eq "displayName") { [pscustomobject]$obj; $obj=@{} }
}

Results:

buildNumber result    name        displayName 
----------- ------    ----        ----------- 
20210303.1  succeeded LDAP Module Rana-R.reddy
20210301.2  succeeded LDAP Module phil.ben    
20210301.1  succeeded LDAP Module phil.ben    
20210303.4  succeeded LDAP Module Rana-R.reddy

Nice, @ralphmwr , thanks for sharing.