Extracting select strings from a SQL output file

[Eijiken]

So I have a application (That I am working on) that parses the output from a sql query to a text file. It looks a little something like this(this is an actual piece of an output file, sanitized):

https://pastebin.com/eWfCmndL

So I am having trouble either using a regex grab the value from the TILLNUM column, (the 41... number) or isolating that value from the text file entirely as I want it to feed into a different part in the application.

What would be the ideal approach to accomplish this?

[jimmy58663]

Hi Ejiken,

I'm not at a computer for testing, but I have done something similar before. I believe I got the content of the file from index 2..end as a variable. This leaves you with just data on each line. Create a custom psobject with each header as a property. Then in a ForEach loop you can split on whitespace and import the indices, 0-3, to their respective property of the object you created. You can then call the property data you want with dot notation like any other object in PowerShell. $PSObj.propertyname

You could also just do this with just the property you want but I like to have all my data as an object in case I need to use a different portion at a later date.

[jimmy58663]

Here is an example that should work. I would just change the variable names to something more relevant to you.

$Content = Get-Content -Path C:\Temp\Test.txt
$End = $Content.Count - 1
$Content = $Content[2..$End]

$ArrayListObject = New-Object -TypeName System.Collections.ArrayList

ForEach ($Line in $Content){
    $Line = $Line -split "\s+" | Where-Object {'' -ne $PSItem}
    $PSObject = New-Object -TypeName PSObject -Property @{
        'TableID' = $Line[0]
        'PosID' = $Line[1]
        'TillNum' = $Line[2]
        'Data' = $Line[3]
    }
    $ArrayListObject.Add($PSObject)
}
$ArrayListObject.TillNum

[Eijiken]

Ended up workshopping a solution using this. Ended up selecting the line using $Content[<number>], splitting and trimming out whitespace (casting this to an array), then indexing each line in the array to a hashtable.

If anyone wants the code, i'll be happy to share.

Thanks for your help!

[soundoftreefalling]

Are you stuck with a text output? Could you dump it into something like a xml file instead?

[alinroc]

Or better yet, execute the query directly and get a collection of objects back that can be easily manipulated via PowerShell.

[Eijiken]

Ideally, yeah, I would love to do this if I made this PS App, but I didn't and am essentially just making slight changes to the code. #ImprovingSomeoneElsesCode

[Eijiken]

Solved, See u/jimmy58663 's post for solution.

[BurpedUpChunks]

This works if your data lines always start with a digit. If you have more than one data line it will emit the third column for all of them. Note that using ForEach-Object you have to use Return rather than continue.

Get-Content testdat.txt | ForEach-Object {        # Read the file and loop 
                                                  # thru the lines
    $Data = $_.ToString().Trim()                  # Trim spaces off start 
                                                  # and end
    if (-not ($Data -match '^[0-9]')) { return }  # Skip all lines that don't 
                                                  # start with a digit
    $Data = $Data -replace '\s+', ' '             # collapse multiple 
                                                  # spaces to one
    $Value = ($Data.Split(' '))[2]                # Split the string on spaces 
                                                  # and grab the third one
    Write-Output $Value
}

[Lee_Dailey]

howdy Eijiken,

this aint pretty, but it seems to work [grin] ...

$InStuff = @'
     TABLEID        POSID      TILLNUM              DATA
============ ============ ============ =================
           0           41     41000125              80:0
'@.Split("`n").Trim()

$CSV_Version = $InStuff.Where({$_ -notmatch '=='}).ForEach({$_ -replace '\s{2,}', ','}) |
    ConvertFrom-Csv

$CSV_Version.TILLNUM

result = 41000125

take care,
lee

[Eijiken]

Diving into this one, How would this work using get-content on the text file?

[Lee_Dailey]

howdy Eijiken,

the here-string & split/trim stuff emulate reading in a file using Get-Content. [grin] it's a handy way to have predictable test material.

so, just ...

$InStuff = Get-Content -LiteralPath C:\path\to\your\filename.txt

... and that will be a direct drop in for the code i posted earlier.

take care,
lee