How to extract and sort specific elements from a huge txt file to csv?

Question

I'm trying to create a powershell script to extract all lines containing "ERROR" and its database path to the item into a huge logs txt file and sort it into a csv file. Example of error :

2022-04-17 00:00:00.9999|ERROR|texte:texte|texte \\DATABASE\Path\Path\Path\Path\Item[Item Name] (ID:########-####-####-###-############ Rank:#). description of the error. 

I would then like to recover the date and the full path to the element in error (\DATABASE\Path\Path\Path\Path\Item[Item Name]) as well as the description of the error and delete the duplicates. Also I don't know if it is possible to directly separate the date, the path and the message in three columns in the csv file.

Example of logs (screenshot) :

2022-04-17 00:00:00.9999|ERROR|ANDataCache:Configuration|################# Error when adding input attributes to data cache (Failed:8/Total:12) [99.9999999999999 ms].
2022-04-17 00:00:00.9999|ERROR|ANCalculationEngine:Configuration|Failed to initialize \\DATABASE\Path1\Path2\Path3\Path4\Item[1. Item Name]  (ID:########-####-####-###-############ Rank:#). Failed to resolve required input 'input A name'
Failed to resolve required input 'input B name'
No output is defined.
2022-04-17 00:00:00.9999|WARN|ANTimeClassManagerHelper:Configuration|Ignoring partial cache signup errors for \\DATABASE\Path1\Path2\Path3\Path4\Item[1. Item Name]  (ID:########-####-####-###-############ Rank:#). Failed to signup some input(s) for receiving updates. 
 Net Volume in Tank: Point not found 'Point Name'.
2022-04-17 00:00:00.9999|ERROR|ANCalculationEngine:Configuration|Failed to initialize \\DATABASE\Path1\Path2\Path3\Path4\Item[1. Item Name] (ID:########-####-####-###-############ Rank:#). Failed to resolve required input 'input name'
There is no time rule configured for this analysis.
No output is defined.
2022-04-17 00:00:00.9999|WARN|###########:#########|############[#####] Ignoring attempt to remove non-existent calculation '\\DATABASE\Path1\Path2\Path3\Path4\Item[1. Item Name] (ID:########-####-####-###-############ Rank:#)'
2022-04-17 00:00:00.9999|ERROR|ANDataCache:Configuration|DataCache:################ Error when adding input attributes to data cache (Failed:8/Total:12) [99.9999999999999 ms]. 

Example of expected result (according to the example above)

(I just want to retrieve ERRORS with path ("\DATABASE\Path\Path\Path\Path\Item[Item Name]"), not the WARNINGS logs or the ERRORS without path)

I started writing this:

$File = "logs.txt"
$Pattern = '(\[ERROR\[^\\]+(?<DatabasePath>[^\\]]+\])(?<ErrorText>[^\r\n]+=)'
$Content = Get-Content $File
[regex]::Matches($Content, $Pattern).Value | Set-Content "output.csv" 

Or to just retrieve the path :

$File = "logs.txt"
$Pattern = '(?<=\\DATABASE\\).+?(?=])'
$Content = Get-Content $File
[regex]::Matches($Content, $Pattern).Value | Set-Content "output.csv"

But in the second case "DATABASE" does not appear in the output file.

Thank you in advance for your answers.

Answer 1

The regex is likely to be improved but for the time being this might help you get what you're looking for. I encourage you to check this regex101 link to test the current regex (and maybe improve it) if there is something not working.

$re = [regex]"(?m)(?<date>^[\d-]+\s[\d:.]+)\|ERROR\|.*?(?<path>\\[\\\w\s\[.\]]+).*?\.(?<description>[\w\s'\r?\n.]+$)"
& {
    $content = Get-Content $File -Raw
    foreach($match in $re.Matches($content)) {
        $date, $path, $description = $match.Groups['date','path','description']
        [pscustomobject]@{
            Date = $date.Value -as [datetime]
            Path = $path.Value.Trim()
            Description = ($description.Value -replace '\r?\n', ' ').Trim()
        }
    }
} | Export-Csv "output.csv" -NoTypeInformation

The output I got using the sample data provided in the question looks like this, which can be exported as a proper CSV:

PS /> $output | Format-Table

Date                  Path                                                  Description
----                  ----                                                  -----------
4/17/2022 12:00:00 AM \\DATABASE\Path1\Path2\Path3\Path4\Item[1. Item Name] Failed to resolve required input 'input A name'. F… 
4/17/2022 12:00:00 AM \\DATABASE\Path1\Path2\Path3\Path4\Item[1. Item Name] Failed to resolve required input 'input name'. The…

PS /> $output[0].Description

Failed to resolve required input 'input A name' Failed to resolve required input 'input B name' No output is defined.

You can remove -as [datetime] if you want to keep the date format as you currently have it in your file.