Author Posts

January 18, 2018 at 12:07 pm

Hello Team,
I am not sure where to start from. I have a log file which will have multiple table entries shown as below.
Table Name : AA
MAX(ADD_DTM) MAX(MNT_DTM)
————————- ————————-
11-MAR-17 11-MAR-17

Table Name : ENTPRS_RSCTN
ENTPRS_RSCTN_COUNT
——————
34113

I need to search for Table name : AA. Once table AA is found, i need to check the column MAX(ADD_DTM) and store its value in a variable. similiarly, i need to store the value of MAX(MNT_DTM). How can i achieive this?
I will have multiple name tables. Once anyone shows how it has to be done for 1 table, i iwll replicate ofr other tables.

January 18, 2018 at 12:29 pm

Because it does not look like a standard format like csv you will have to parse it by yourself. The following video might help you to pick the proper way for this task:
Sophisitcated Techniques of Plain Text Parsing

January 18, 2018 at 12:37 pm

Hello olaf,
It is the spool file from generated from Oracle.and the format is standard. sometimes, I select 2 columns and sometimes 1 column

January 18, 2018 at 12:58 pm

OK, it might be an Oracle standard and if you find a module specialized for parsing Oracle spool file format it would make your life much easier but as long as it is not an industry standard like csv you will have to create something by yourself. You could try to find something in the Powershell Gallery or Microsoft Technet Script Center and adapt it to your needs.

January 18, 2018 at 4:00 pm

I replied to a different question a few months ago. It had an example. Not sure if you can look for that on this site or not. I think the right solution for you would be the PowerShell 5 command ConvertFrom-String

There is a lot of information out there, I found this one to be the best. You must read this carefully and follow all of the steps using that here-string. hint: @""@ don't forget the second @

https://foxdeploy.com/2015/01/13/walkthrough-part-two-advanced-parsing-with-convertfrom-string/

Good luck!

January 18, 2018 at 4:05 pm

found the other post. Olaf had a linkin that thread too.

https://powershell.org/forums/topic/parsing-a-text-file-with-tab-as-delimiter/#post-82498

Hopefully that previous post helps too.

January 18, 2018 at 5:14 pm

#region Sample data
$TempFile = [System.IO.Path]::GetTempFileName()
@'
Table Name : AA
MAX(ADD_DTM) MAX(MNT_DTM)
————————- ————————-
11-MAR-17 11-MAR-17

Table Name : ENTPRS_RSCTN
ENTPRS_RSCTN_COUNT
——————
34113
'@ | Out-File $TempFile
#endregion

#region Read log file
$LogLines = Get-Content $TempFile
"Read $($LogLines.Count) lines"
#endregion

#region Get table list
$i=0
$TableList = foreach ($Line in $LogLines) {
    if ($Line -match 'Table Name') { 
        [PSCustomObject][Ordered]@{
            TableName    = $Line.Split(':')[1].Trim()
            TableLine    = $i
            ColumnNames  = ''
            ColumnValues = ''
        }
    }
    $i++
}
"Identified $($TableList.Count) tables:"
$TableList | FT TableName,TableLine -a 
#endregion

#region Read column data
foreach ($Table in $TableList) {
    if ($LogLines.Count -gt $Table.TableLine+3) {
        if ($LogLines[$Table.TableLine+1].Trim() -match ' ') { 
            $Table.ColumnNames = $LogLines[$Table.TableLine+1].Split(' ')
        } else {
            $Table.ColumnNames = $LogLines[$Table.TableLine+1].Trim()
        }
        if ($LogLines[$Table.TableLine+3].Trim() -match ' ') { 
            $Table.ColumnValues = $LogLines[$Table.TableLine+3].Split(' ')
        } else {
            $Table.ColumnValues = $LogLines[$Table.TableLine+3].Trim()
        }
    }
}
"and the following column names/values:"
$TableList | FT TableName,ColumnNames,ColumnValues -a 
#endregion
Read 9 lines
Identified 2 tables:

TableName    TableLine 
---------    --------- 
AA                   0                         
ENTPRS_RSCTN         5                         


and the following column names/values:

TableName    ColumnNames                  ColumnValues          
---------    -----------                  ------------          
AA           {MAX(ADD_DTM), MAX(MNT_DTM)} {11-MAR-17, 11-MAR-17}
ENTPRS_RSCTN ENTPRS_RSCTN_COUNT           34113     

January 18, 2018 at 5:49 pm

Wow .... sorry if that sounds ungrateful or too critical but IMHO what you just did is just wrong and unnecessary. Not your code – probably it's good, I didn't test it but do you really think that's the best way to help the original poster when you do his job? Why do you do something like this? You don't have to prove anything to no one.

January 18, 2018 at 7:25 pm

I agree. I have an affliction, whereby I cannot help myself sometimes.. lol

January 18, 2018 at 11:56 pm

To some degree, we all suffer from the over-helping syndrome. 8^}
Part of why we are here. 8^}

Well, that and learning what others are trying, just because.

January 19, 2018 at 10:14 am

Thanks Sam. It worked as expected. Thank you for writing entire code for me.

January 19, 2018 at 10:16 am

Thanks John. I learnt about ConvertFrom-String. Hope to use it in future. You guys Rock.

January 19, 2018 at 12:39 pm

Hi Sam,
Everything worked as expected. I want to send the output displayed on the screen in an email.I used "out-string" and captured the output in a variable but the output is not elegant. How can i format the output and present it in an email. I do not want to send it as an attachment
Thanks for your help.

January 19, 2018 at 12:49 pm

I will not be able to download OR upload any files. I need to do this using powershell commands

January 19, 2018 at 12:53 pm

Can you copy and paste?
Google/Bing manual installation of a PS module