CODEWITHSUNDEEP
powershell
Spas Daskalov
Spas Daskalov

Reputation: 3

Import text file with specific format and convert to csv/generate psobject

I have a situation that I have to read a log file with many dumped user attributes generated in the past with out-file command.

The format of the text is:

Num                        : 1
Property2                  : Some Char
Property3                  : Some Text
Property4                  : CN=Some Text,OU=Some Text,DC=Some Text,DC=Some Text,DC=Some Text,DC=net
Property5                  : Some Text
Property6                  : Some Text@Some Text.Some Text.Some Text.net
Property7                  : Some Text
Property8                  : Multiple line text
                             Multiple line text
                             Multiple line text
                             Multiple line text
Property9                  : Some Char

Num                        : 2
Property2                  : Some Char
Property3                  : Some Text
Property4                  : CN=Some Text,OU=Some Text,DC=Some Text,DC=Some Text,DC=Some Text,DC=net
Property5                  : Some Text
Property6                  : Some Text@Some Text.Some Text.Some Text.net
Property7                  : Some Text
Property8                  : Multiple line text
                             Multiple line text
                             Multiple line text
                             Multiple line text
Property9                  : Some Char

Is there a way to import this and to have a structure property - value. Something like PSobject? I have to do a search in for each user in his multiline property (Property 8) based on criteria and to extract only dose users that match this criteria.

Upvotes: 0

Views: 306

Answers (1)

Theo
Theo

Reputation: 61253

To parse this and format the way you want in a CSV file, I'd suggest using an odered Hashtable to parse the content of the file and afterwards convert this into an array of PsCustomObjects for outputting the file with headers

# create an ordered Hashtable to store the results
$hash = [ordered]@{}
foreach ($line in (Get-Content -Path 'D:\Test\TheInputLOgFile.txt')) {
    if ($line.IndexOf(':') -gt 0) {
        $name, $value = $line -split '\s*:\s*', 2
        $hash[$name] = $value
    }
    else {
        $hash[$name] += [Environment]::NewLine + $line.Trim()
    }
}

If you output the hash straight away you'll get

$hash | Format-List

Name  : Property1
Value : Some Char

Name  : Property2
Value : Some Char

Name  : Property3
Value : Some Text

Name  : Property4
Value : CN=Some Text,OU=Some Text,DC=Some Text,DC=Some Text,DC=Some Text,DC=net

Name  : Property5
Value : Some Text

Name  : Property6
Value : Some Text@Some Text.Some Text.Some Text.net

Name  : Property7
Value : Some Text

Name  : Property8
Value : Multiple line text
        Multiple line text
        Multiple line text
        Multiple line text

Name  : Property9
Value : Some Char

To turn this into a CSV with headers Attribute and Value, you can do this:

$hash.GetEnumerator() | Foreach-Object {
    [PsCustomObject]@{
        'Attribute' = $_.Name
        'Value' = $_.Value
    }
} | Export-Csv -Path 'D:\Test\LogResult.csv' -UseCulture -NoTypeInformation

When you double-click this file to open in Excel (and set the cell alignment to Wrap text), it will look like this:

output

EDIT

If you have more items in the input file like in your new example, you will need an extra loop for every block of data in there.

This can be done like below:

# split the content of the file in text blocks on the double NewLine
$blocks = (Get-Content -Path 'D:\Test\TheInputLogFile.txt' -Raw) -split '(\r?\n){2,}'
# loop through each textblock, split the lines, build and convert 
# the the Hashtable and capture the results in variable $result
$result = $blocks | ForEach-Object {
    # create an ordered Hashtable to store the results
    $hash = [ordered]@{}
    foreach($line in ($_ -split '\r?\n')) {
        if ($line.IndexOf(':') -gt 0) {
            $name, $value = $line -split '\s*:\s*', 2
            $hash[$name] = $value
        }
        elseif ($line -match '\S') {
            $hash[$name] += [Environment]::NewLine + $line.Trim()
        }
    }
    $hash.GetEnumerator() | Foreach-Object {
        [PsCustomObject]@{
            'Attribute' = $_.Name
            'Value'     = $_.Value
        }
    }
}
$result | Export-Csv -Path 'D:\Test\LogResult.csv' -UseCulture -NoTypeInformation

The output when opened in Excel will now show:

Output2

Upvotes: 1

Related Questions