Sign in to follow this  
Followers 0
Kovitt

Parse through data in a txt file

26 posts in this topic

Hello,

The script I am currently writing, runs certain queries in sqlplus and then outputs the resuts in a text file. The results look similar to:

CODE

CRT_TS CE_ID

--------- ----------

23-JUN-08 13300

23-JUN-08 13300

23-JUN-08 13300

23-JUN-08 13300

23-JUN-08 13300

23-JUN-08 13300

23-JUN-08 13300

23-JUN-08 13300

23-JUN-08 13300

23-JUN-08 13300

23-JUN-08 13300

CRT_TS CE_ID

--------- ----------

23-JUN-08 13300

23-JUN-08 13300

23-JUN-08 13300

23-JUN-08 13300

23-JUN-08 13300

23-JUN-08 13300

17 rows selected.

In this case all of the "ce_id's" are the same, but this is not always the case. I need to parse through these results and have it tell me what the ce_id's are and haw many times each one occur. In this case it would be 13300: 17 times.

Any suggestions?

Thanks!

Share this post


Link to post
Share on other sites



Just wondering... why not have the DB Engine count it for you?

> Select count(CE_ID) from your_table group by CE_ID;

Share this post


Link to post
Share on other sites

Hello Kovitt,

I changed the output file to include some different CE IDs for testing. Try this out:

CRT_TS CE_ID
--------- ----------
23-JUN-08 13300
23-JUN-08 13300
23-JUN-08 13300
23-JUN-08 13300
23-JUN-08 13300
23-JUN-08 13300
23-JUN-08 13300
23-JUN-08 13300
23-JUN-08 12300
23-JUN-08 12300
23-JUN-08 12300

CRT_TS CE_ID
--------- ----------
23-JUN-08 13300
23-JUN-08 13301
23-JUN-08 13300
23-JUN-08 13300
23-JUN-08 13300
23-JUN-08 13300

17 rows selected.

Global $UniqueIDs = ObjCreate( "Scripting.Dictionary" )

Func IsCeId( $line )
    Return 1 = StringRegExp( $line, "\A(\d){1,}\-\D{3}\-(\d){2}", 0 )
EndFunc

Func ExtractCeIDFromLine( $line )
    Local $temp = StringSplit( $line, " " )
    Return $temp[2]
EndFunc

Dim $file = FileOpen( @ScriptDir & "\output.txt", 0 )
While 1
    $line = FileReadLine($file)
    If @error = -1 Then ExitLoop
    If IsCeId( $line ) then 
        $id = ExtractCeIDFromLine( $line )
        If $UniqueIDs.Exists( $id ) Then
            $UniqueIDs( $id ) = $UniqueIDs( $id ) + 1
        Else
            $UniqueIDs.Add( $id, 1 )
        EndIF       
    EndIf
Wend

; Write out the CE ID counts
$keys = $UniqueIDs.Keys
For $i = 1 to UBound( $keys )
    $key = $keys[$i - 1]
    ConsoleWrite( $key & " : " & $UniqueIDs( $key ) & @CRLF)
Next

This should produce:

13300 : 13
12300 : 3
13301 : 1

Share this post


Link to post
Share on other sites

Perfect!!!! Thank you soooo much, I really appreciate it!!

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

Ok, so I added the code above to my script and it keeps saying that my Func(Func IsCeId( $line )) has no matching end func, but it does!!

;############################################################################
;AutoIt Version: 3.2.12.1
;
;Author: Keegan Ovitt
;Date: 6/23/08
;
;Desc: Perform the processes used to troubleshoot Job Watch errors. 
;
;
;############################################################################

;+++++++++++++++++++++++++Included User-Functions+++++++++++++++++++++++++++++
#Include <File.au3>
#Include <Array.au3>
#include <GUIConstantsEx.au3>
;+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Opt('MustDeclareVars', 1)

JobWatch()

Func JobWatch()
;^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^Variables^^^^^^^^^^^^^^^^^^^^^^^^^
Local $msg, $Jobs, $path, $results, $RollUpJobs, $Button_1, $Button_2, $Button_3, $Button_4, $Button_5, $Button_6, $Button_7, $Button_8
;^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^   
        ;----------------------------------------------------------------------
        ;Compiles rollupSelect.txt with the script executable
        ;----------------------------------------------------------------------
FileInstall("C:\Documents and Settings\OvittKe\MyDocuments\scripts\Job_Watch\rollupSelect.txt", @ScriptDir & "\", 0)
        #cs---------------------------------------------------------------
            Will create a dialog box that when displayed is centered
        #ce---------------------------------------------------------------
        GUICreate("JobWatch Exeption Handling",302, 198) 
        #cs---------------------------------------------------------------
            Creates buttons, sets button text, and button location
        #ce---------------------------------------------------------------
    
    ;===========================================Buttons
    $Button_1 = GUICtrlCreateButton("IFCT to OTPTx Rollups", 1, 2, 300)
                $Button_2 = GUICtrlCreateButton("Manual Releases Pending", 1, 26, 300)
    $Button_3 = GUICtrlCreateButton("CE Day Open Messages Posted", 1, 50, 300)
    $Button_4 = GUICtrlCreateButton("Build A Day Day + 0", 1, 74, 300)
    $Button_5 = GUICtrlCreateButton("Day Close Past Due", 1, 98, 300)
    $Button_6 = GUICtrlCreateButton("OTPT|OTPT2|ST_OUTp1-OUTPUTDB Rollups Pending", 1, 122, 300)
    $Button_7 = GUICtrlCreateButton("IFACT to OTPT|OTPT2|ST_OUTp1 Failed Rollups Pending", 1, 146, 300)
    $Button_8 = GUICtrlCreateButton("OTPT|ST_UDM ST_UDM Rollups Pending", 1, 170, 300)
    ;=====================================================================
        
        GUISetState() 
             While 1
             $msg = GUIGetMsg()
        Select
            Case $msg = $GUI_EVENT_CLOSE
                ExitLoop
            Case $msg = $Button_1
            ;***************************************************************************    
            ;IFCT to OTPTx Rollups
            ;***************************************************************************    
    Global $UniqueIDs = ObjCreate( "Scripting.Dictionary" )
        RunWait(@ComSpec & " /c Type rollupSelect.txt | sqlplus -s ******/*****@*** > RollupJobs.txt")
            $Jobs = FileRead("RollupJobs.txt")
            MsgBox(0, "Rollup Jobs", "Current Jobs: " & @CRLF & $Jobs)
            
            ;-------------------------------Functions----------------------------------------
                Func IsCeId( $line )
                Return 1 = StringRegExp( $line, "\A(\d){1,}\-\D{3}\-(\d){2}", 0 )
                EndFunc
                    
                Func ExtractCeIDFromLine( $line )
                    Local $temp = StringSplit( $line, " " )
                    Return $temp[2]
                EndFunc                 
            ;--------------------------------------------------------------------------------
                    
                    Dim $file = FileOpen( @ScriptDir & "\RollupJobs.txt", 0 )
                        While 1
                            $line = FileReadLine($file)
                            If @error = -1 Then ExitLoop
                            If IsCeId( $line ) then 
                            $id = ExtractCeIDFromLine( $line )
                            If $UniqueIDs.Exists( $id ) Then
                            UniqueIDs( $id ) = $UniqueIDs( $id ) + 1
                                Else
                                $UniqueIDs.Add( $id, 1 )
                            EndIf        
                            EndIf
                                
                    Wend
                    
                $keys = $UniqueIDs.Keys
                For $i = 1 to UBound( $keys )
                    $key = $keys[$i - 1]
                    ConsoleWrite( $key & " : " & $UniqueIDs( $key ) & @CRLF)
                Next
                    
            Case $msg = $Button_2
            ;***************************************************************************    
            ;Manual Releases Pending
            ;***************************************************************************             
            
            
            Case $msg = $Button_3
            ;***************************************************************************    
            ;CE Day Open Messages Posted
            ;***************************************************************************            
            
            
            Case $msg = $Button_4
            ;***************************************************************************    
            ;Build A Day Day + 0
            ;***************************************************************************
            
            
            Case $msg = $Button_5
            ;***************************************************************************    
            ;Day Close Past Due
            ;***************************************************************************
            
            
            Case $msg = $Button_6
            ;***************************************************************************    
            ;OTPT|OTPT2|ST_OUTp1-OUTPUTDB Rollups Pending
            ;***************************************************************************
            
            
            Case $msg = $Button_7
            ;***************************************************************************    
            ;IFACT to OTPT|OTPT2|ST_OUTp1 Failed Rollups Pending
            ;***************************************************************************    
            
            
            Case $msg = $Button_8
            ;***************************************************************************    
            ;OTPT|ST_UDM ST_UDM Rollups Pending
            ;***************************************************************************    
        
        
        EndSelect
    WEnd
EndFunc

Does anyone see the problem??

Thanks

Edited by Kovitt

Share this post


Link to post
Share on other sites

Hi,

just move your hole Functions Block to the End of the Code.. than it works :)

Complete Func JobWatch

;-------------------------------Functions----------------------------------------

; Your functions-....

;--------------------------------------------------------------------------------

Share this post


Link to post
Share on other sites

You can't declare functions inside of functions! Move these outside of the JobWatch function or just don't make them functions to begin with:

Func IsCeId( $line )
   Return 1 = StringRegExp( $line, "\A(\d){1,}\-\D{3}\-(\d){2}", 0 )
EndFunc
                   
Func ExtractCeIDFromLine( $line )
   Local $temp = StringSplit( $line, " " )
   Return $temp[2]
EndFunc

Share this post


Link to post
Share on other sites

Worked!!!

Thanks a ton!

Share this post


Link to post
Share on other sites

Now my problem is that it is displaying

: 13

instead of

13300 : 13

:)

Share this post


Link to post
Share on other sites

Now my problem is that it is displaying

: 13

instead of

13300 : 13

:)

I did some further investigation :P

It is only displaying the nuber of rows, not the actual individual ce_ids and their numbers..

Any Ideas of why?

Thanks, you guys are such a big help!

Share this post


Link to post
Share on other sites

Hmmmm, I can't figure it out.... Sorry Bro

Share this post


Link to post
Share on other sites
:) bump :P

Share this post


Link to post
Share on other sites

Here:

;############################################################################
;AutoIt Version: 3.2.12.1
;
;Author: Keegan Ovitt and members of the AutoIt community
;Date: 6/23/08
;
;Desc: Perform the processes used to troubleshoot Job Watch errors.
;
;
;############################################################################

;+++++++++++++++++++++++++Included User-Functions+++++++++++++++++++++++++++++
#Include <File.au3>
#Include <Array.au3>
#include <GUIConstantsEx.au3>
;+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Opt('MustDeclareVars', 1)

;-------------------------------Functions----------------------------------------
Func IsCeId( $line )
    Return 1 = StringRegExp( $line, "\A(\d){1,}\-\D{3}\-(\d){2}", 0 )
EndFunc
   
Func ExtractCeIDFromLine( $line )
    Local $temp = StringSplit( $line, " " )
    Return $temp[2]
EndFunc  

Func IFCTtoOTPTxRollups()
    Local $jobs, $key, $id
    Local $UniqueIDs = ObjCreate( "Scripting.Dictionary" )  
    
    $jobs = FileOpen( @ScriptDir & "\RollupJobs.txt", 0 )
    
    While 1
        Local $line = FileReadLine($jobs)
        If @error = -1 Then ExitLoop
        If IsCeId( $line ) then
            Local $id = ExtractCeIDFromLine( $line )
            If $UniqueIDs.Exists( $id ) Then
                $UniqueIDs( $id ) = $UniqueIDs( $id ) + 1
            Else
                $UniqueIDs.Add( $id, 1 )
            EndIf      
        EndIf                                               
    Wend
           
    Local $keys = $UniqueIDs.Keys
    For $i = 1 to UBound( $keys )
        $key = $keys[$i - 1]
        ConsoleWrite( $key & " : " & $UniqueIDs( $key ) & @CRLF)
    Next
    
    FileClose( $jobs )
EndFunc

;--------------------------------------------------------------------------------
        
;^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^Variables^^^^^^^^^^^^^^^^^^^^^^^^^
Global $msg, $path, $results, $RollUpJobs, $Button_1, $Button_2, $Button_3, $Button_4, $Button_5, $Button_6, $Button_7, $Button_8

GUICreate("JobWatch Exeption Handling",302, 198)
   
;===========================================Buttons
$Button_1 = GUICtrlCreateButton("IFCT to OTPTx Rollups", 1, 2, 300)
$Button_2 = GUICtrlCreateButton("Manual Releases Pending", 1, 26, 300)
$Button_3 = GUICtrlCreateButton("CE Day Open Messages Posted", 1, 50, 300)
$Button_4 = GUICtrlCreateButton("Build A Day Day + 0", 1, 74, 300)
$Button_5 = GUICtrlCreateButton("Day Close Past Due", 1, 98, 300)
$Button_6 = GUICtrlCreateButton("OTPT|OTPT2|ST_OUTp1-OUTPUTDB Rollups Pending", 1, 122, 300)
$Button_7 = GUICtrlCreateButton("IFACT to OTPT|OTPT2|ST_OUTp1 Failed Rollups Pending", 1, 146, 300)
$Button_8 = GUICtrlCreateButton("OTPT|ST_UDM ST_UDM Rollups Pending", 1, 170, 300)
;=====================================================================
   
GUISetState()

While 1
    $msg = GUIGetMsg()
    Select
        Case $msg = $GUI_EVENT_CLOSE
            ExitLoop
        Case $msg = $Button_1
            IFCTtoOTPTxRollups()
    EndSelect
WEnd

Share this post


Link to post
Share on other sites

Seriously, Thank you sooo much.. I know this takes a long time to read through and figure out where I went wrong. I reallly really appreciate it!!! :)

Share this post


Link to post
Share on other sites

Sooo, I am still having the same issue..

The out put is just

: 604

That is how many rows the query pulls up.. I dont know how to get it to dislay the different ce_ids and how often the occur.

:)

Share this post


Link to post
Share on other sites

#16 ·  Posted (edited)

If this data is already in SQL you should use a SQL query to do the heavy lifting:

SELECT CE_ID, COUNT(*) as "Instances of CE_ID"
 FROM tblTable
 GROUP BY CE_ID;
Edited by weaponx

Share this post


Link to post
Share on other sites

If this data is already in SQL you should use a SQL query to do the heavy lifting:

SELECT CE_ID, COUNT(*) as "Instances of CE_ID"
 FROM tblTable
 GROUP BY CE_ID;
LOL wow.. If only I knew it was that easy.. As you can tell I am new to SQL.. You just saved me about 70 lines of script.... lol

Thank you :P

:)

Share this post


Link to post
Share on other sites

Proof of concept (tested in MySQL):

Populate:

-- phpMyAdmin SQL Dump
-- version 2.10.2
-- http://www.phpmyadmin.net
-- 
-- Host: localhost:3306
-- Generation Time: Jun 24, 2008 at 03:58 PM
-- Server version: 4.1.13
-- PHP Version: 5.2.0

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

-- 
-- Database: `misc`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `Test`
-- 

CREATE TABLE `Test` (
  `crt_ts` tinytext NOT NULL,
  `ce_id` tinytext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 
-- Dumping data for table `Test`
-- 

INSERT INTO `Test` (`crt_ts`, `ce_id`) VALUES 
('23-JUN-08', '13300'),
('23-JUN-08', '13300'),
('23-JUN-08', '13300'),
('23-JUN-08', '13300'),
('23-JUN-08', '13300'),
('23-JUN-08', '13300'),
('23-JUN-08', '13300'),
('23-JUN-08', '13300'),
('23-JUN-08', '12300'),
('23-JUN-08', '12300'),
('23-JUN-08', '12300'),
('23-JUN-08', '13300'),
('23-JUN-08', '13301'),
('23-JUN-08', '13300'),
('23-JUN-08', '13300'),
('23-JUN-08', '13300'),
('23-JUN-08', '13300');

Query:

SELECT CE_ID, COUNT(*) as "Instances of CE_ID"
FROM misc.Test
GROUP BY CE_ID;

Result:

"CE_ID","Instances of CE_ID"
"12300",3
"13300",13
"13301",1

Share this post


Link to post
Share on other sites

Nice!!, It indeed works..

Output

CODE

CE_ID Instances of CE_ID

---------- ------------------

12311 14

12313 6

12314 8

12315 20

12316 11

12317 10

12319 20

12322 6

12323 6

12325 1

12329 4

CE_ID Instances of CE_ID

---------- ------------------

12330 3

12331 5

12332 10

12335 3

12336 5

12338 3

12343 3

12344 9

12346 5

12347 4

Thanks to everyone that has helped me with this!!! :)

Share this post


Link to post
Share on other sites

I think people tend to overlook the power of SQL/MySQL queries.

http://dev.mysql.com/doc/refman/5.0/en/functions.html

I keep a chm manual on my desktop for MySQL, PHP, and AutoIt at all times. I just wish there was one for C++...

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0