Parse through data in a txt file

Kovitt · June 23, 2008

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

CRT_TS CE_ID

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

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!

hhzz · June 23, 2008

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

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

zfisherdrums · June 24, 2008

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

Kovitt · June 24, 2008

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

Kovitt · June 24, 2008

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 June 24, 2008 by Kovitt

badzox · June 24, 2008

Hi,

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

Complete Func JobWatch

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

; Your functions-....

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

zorphnog · June 24, 2008

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

Kovitt · June 24, 2008

Worked!!!

Thanks a ton!

Kovitt · June 24, 2008

Now my problem is that it is displaying

: 13

instead of

13300 : 13

Kovitt · June 24, 2008

Now my problem is that it is displaying
: 13
instead of
13300 : 13

I did some further investigation

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!

XWave · June 24, 2008

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

Kovitt · June 24, 2008

bump

zfisherdrums · June 24, 2008

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

Kovitt · June 24, 2008

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!!!

Kovitt · June 24, 2008

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.

weaponx · June 24, 2008

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 June 24, 2008 by weaponx

Kovitt · June 24, 2008

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

weaponx · June 24, 2008

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

Kovitt · June 24, 2008

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!!!

weaponx · June 24, 2008

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++...

Sign In

Parse through data in a txt file

Recommended Posts

Kovitt

hhzz

zfisherdrums

Kovitt

Kovitt

badzox

zorphnog

Kovitt

Kovitt

Kovitt

XWave

Kovitt

zfisherdrums

Kovitt

Kovitt

weaponx

Kovitt

weaponx

Kovitt

weaponx

Create an account or sign in to comment

Create an account

Sign in

Browse

AutoIt Resources

Release

Beta