Extracting exact information from a text file using Powershell

Welcome Forums General PowerShell Q&A Extracting exact information from a text file using Powershell

Viewing 6 reply threads
  • Author
    Posts
    • #52998
      Participant
      Topics: 23
      Replies: 17
      Points: 1
      Rank: Member

      Hi All,

      I have a text file with the Contents below. I have to fetch only the — CTIA File Checks — info i.e the 3 lines and then Take out the OK or NOT OK value shown below.

      OK: ‘Nortel Data Load’, Expected: 2016-08-30 00:00:00, found: 2016-08-30 02:03:00
      NOT OK: ‘AVAYA Data Load’, Expected: 2016-08-30 00:00:00, found: 2016-08-30 02:14:56
      OK: ‘AUX Data Load’, Expected: 2016-08-30 00:00:00, found: 2016-08-30 02:29:27

      Here’s the Text file -> https://gist.github.com/anonymous/6dc2701ad4adb24a0f1ce09978502a36

    • #53000
      Participant
      Topics: 18
      Replies: 575
      Points: 42
      Rank: Member

      Here’s a start.

      gc .\text.txt | Select-String “CTIA” -Context 3

    • #53004
      Participant
      Topics: 23
      Replies: 17
      Points: 1
      Rank: Member

      Well, that gives the output as below :

      Connecting to ‘ABC-043’…
      OK

      > — CTIA File Checks —
      OK: ‘Nortel Data Load’, Expected: 2016-08-30 00:00:00, found: 2016-08-30 02:03:00
      NOT OK: ‘AVAYA Data Load’, Expected: 2016-08-30 00:00:00, found: 2016-08-30 02:14:56
      OK: ‘AUX Data Load’, Expected: 2016-08-30 00:00:00, found: 2016-08-30 02:29:27

      How to extract the exaxt OK and NOT OK from this?

      • #53011
        Participant
        Topics: 0
        Replies: 61
        Points: 0
        Rank: Member
        $file = Get-Content c:\myfile.txt
        $isCTIA = $False
        
        $lines = ForEach($line in $file)
        {
            If(-not $isCTIA)
            {
                If($line -match 'CTIA File Checks')
                {
                    $isCTIA = $True
                    Continue
                }
            }
            ElseIf (-not $line.Trim())
            {
                $isCTIA = $False
                Continue
            }
            Else
            {
                $line
            }
        }
        
        ForEach ($line in $lines)
        {
            $line.Split(":")[0]
        }

        Something along those lines?

    • #53013
      Participant
      Topics: 18
      Replies: 575
      Points: 42
      Rank: Member

      my bad, -context 0,3

      Here’s another way.

      (gc .\text.txt) -match “Nortel Data Load|AVAYA Data Load|AUX Data Load”

      Use methods such as trimstart,split,replace.

    • #53015
      Participant
      Topics: 23
      Replies: 17
      Points: 1
      Rank: Member

      Thanks Craig and Dan, that worked perfectly 🙂

    • #53027
      Participant
      Topics: 18
      Replies: 575
      Points: 42
      Rank: Member

      My co-workers have a tendency to plop my code into their scripts and never learn why. String manipulation is crucial to learning powershell. I’ve had this bookmarked for about six years.

      https://technet.microsoft.com/en-us/library/ee692804.aspx

      ((gc .\text.txt) -match “Nortel Data Load|AVAYA Data Load|AUX Data Load”) -split ‘OK: ‘ |?{$_ -match ‘data’}

      (gc .\text.txt | Select-String “CTIA” -Context 0,3) -split “`n” | %{$_ -split ‘ OK: ‘} | ?{$_ -match ‘data’}

    • #53033
      Participant
      Topics: 12
      Replies: 1618
      Points: 2,543
      Helping Hand
      Rank: Community Hero

      There are multiple ways to do it. I started by removing stuff I didn’t care about like whitespace and log comments:

      #Get the Log and remove any line that doesn't have at least 2 semicolons
      $log = Get-Content -Path C:\Users\Rob\Desktop\Archive\test.txt | Where {$_ -like "*:*:*"}
      

      Then you can use a WHERE statement because each row you care about in this case contains “Data load” or even just data in the file you provided:

      PS C:\Users\Rob> $log | Where{$_ -like "*Data*"}
      OK: 'Nortel Data Load', Expected: 2016-08-30 00:00:00, found: 2016-08-30 02:03:00
      OK: 'AVAYA Data Load', Expected: 2016-08-30 00:00:00, found: 2016-08-30 02:14:56
      OK: 'AUX Data Load', Expected: 2016-08-30 00:00:00, found: 2016-08-30 02:29:27
      

      or SELECT-STRING:

      PS C:\Users\Rob> $log | Select-String "Data"
      
      
      OK: 'Nortel Data Load', Expected: 2016-08-30 00:00:00, found: 2016-08-30 02:03:00
      OK: 'AVAYA Data Load', Expected: 2016-08-30 00:00:00, found: 2016-08-30 02:14:56
      OK: 'AUX Data Load', Expected: 2016-08-30 00:00:00, found: 2016-08-30 02:29:27
      
      
      Or you can even go a bit further and actually do a full parse of the log.  Not sure how to define the columns, but you should get the jest:
      
      
      #Get the Log and remove any line that doesn't have at least 2 semicolons
      $log = Get-Content -Path C:\Users\Rob\Desktop\Archive\test.txt | Where {$_ -like "*:*:*"}
      
      $results = foreach ($line in $log) {
          $arrLine = $line -split ","
      
          $props = [ordered]@{
              Status=$arrLine[0].Split(":")[0]
              Message=$arrLine[0].Split(":")[1]
              Threshold=$arrLine[1]
              Current=$arrLine[2]
      
          }
      
          New-Object -TypeName PSObject -Property $props
      }
      

      which gives you a object to work with:

      PS C:\Users\Rob> $results | Select -First 10
      
      Status Message                                          Threshold                      Current                    
      ------ -------                                          ---------                      -------                    
      OK      VW_PARTITIONED_FCT_CALL_DETAIL_END               Expected: 2016-08-30 06:42:58  found: 2016-08-30 06:56:30
      OK      VW_PARTITIONED_FCT_CALL_DETAIL_LEG               Expected: 2016-08-30 06:42:58  found: 2016-08-30 06:55:50
      OK      VW_CUS_PARTITIONED_FCT_TCD_CORRELATED            Expected: 2016-08-30 05:58:11  found: 2016-08-30 06:37:25
      OK      VW_CUS_PARTITIONED_FCT_TCD_CONTACT_CORRELATED    Expected: 2016-08-30 05:58:11  found: 2016-08-30 06:37:25
      OK      VW_CUS_PARTITIONED_FCT_SOFTPHONE_TRANSFER_EVENT  Expected: 2016-08-30 05:58:15  found: 2016-08-30 06:55:59
      OK      VW_CUS_PARTITIONED_FCT_AGENT_EVENT               Expected: 2016-08-29 23:30:00  found: 2016-08-29 23:59:59
      OK      VW_CUS_PARTITIONED_SUM_AGENT_EVENT_HH            Expected: 2016-08-29 23:30:00  found: 2016-08-29 23:30:00
      OK      VW_CUS_PARTITIONED_SUM_AGENT_HH                  Expected: 2016-08-29 23:30:00  found: 2016-08-29 23:30:00
      OK      VW_CUS_PARTITIONED_FCT_AVAYA_AGENT_EVENT         Expected: 2016-08-29 23:30:00  found: 2016-08-29 23:59:59
      OK      VW_CUS_PARTITIONED_SUM_AVAYA_AGENT_EVENT_HH      Expected: 2016-08-29 23:30:00  found: 2016-08-29 23:30:00
      

      Then you would parse the Message column and could remove the "Status":

      PS C:\Users\Rob> $results | Where{$_.Message -like "*Data*"}  | Select Message, Threshold, Current
      
      
      Message             Threshold                      Current                    
      -------             ---------                      -------                    
       'Nortel Data Load'  Expected: 2016-08-30 00:00:00  found: 2016-08-30 02:03:00
       'AVAYA Data Load'   Expected: 2016-08-30 00:00:00  found: 2016-08-30 02:14:56
       'AUX Data Load'     Expected: 2016-08-30 00:00:00  found: 2016-08-30 02:29:27
      
Viewing 6 reply threads
  • The topic ‘Extracting exact information from a text file using Powershell’ is closed to new replies.