Parsing a text file, modifying it, rewriting it

[Khab]

Hey all,

I'm hoping someone here can point me in the right direction for a script I'm trying to sort out. Basically, the situation is we have a basic application that generates an hourly count for users, and it is saved in a basic table in a database, then it exports it to a text file.

We get something like this

DateTime           Count
20.06.09 0700   25
20.06.09 0800   27

but for each hour of each day (i.e. date 00 through date 23). And the text file includes in this list all the days, so there is no delimeted between days. There is always a full day worth (i.e. always ends at 2300, never cut off at 1600 or anything).

What I need to do, is go through, and add up for each day, the total counts based on hour, e.g. a 'Business Hours' total giving each days total between 0800 and 1800, and a 'Non Business Hours' total giving the rest.

I have not completed the code yet, as I am a bit lost how to do this, but some pseudocode for what I expect the program to do (and most of it I have no problem coding) would be

Open Text File
ReadLine
If Header
WriteLineToNewFile
endif
Get Date & Time of Line
Get Count of Line
If Time >= 0800 AND <= 1800
If exists (CountTotalForDateDay)
 CountTotalForDateDay = CountTotalForDateDay + CountForCurrentLine
Else 
 CountTotalForDateDay = CountForCurrentLine
endif
Else
if exists (CountTotalForDateNight)
 CountTotalForDateNight = CountTotalForDateNight + CountForCurrentLine
else
 CountTotalForDateNight = CountForCurrentLine
endif
endif
WriteCurrentLineToNewFile
if Hour = 23
Write CountTotalForDateDay & Night to file
endif

So essentially, I want to add up the totals each day, for "day time" hours and "night time" hours, and insert those lines in between each change of date in the text file (or a new text file, it doesn't matter).

The part I'm really struggling with is, when reading the lines, how to determine the Date, Time, and Count values. I'm expecting this will be with a regex, but for the life of me I can't fathom it.

Any advice would be much appreciated.

Cheers

Khab

[Authenticity]

expandcollapse popup

#include <Array.au3>

Dim $sStr = _
'DateTime           Count' & @CRLF & _
'20.06.09 0700  25' & @CRLF & _
'20.06.09 0800  27' & @CRLF & _ ;...
'20.06.09 2300  33' & @CRLF & _
'21.06.09 0100  02' & @CRLF & _
'21.06.09 0900  11' & @CRLF & _
'21.06.09 1300  15' & @CRLF & _
'21.06.09 1600  22' & @CRLF & _
'21.06.09 2200  13' & @CRLF & _
'21.06.09 2300  06'

Dim $aMatch, $aLines, $aSomething
Dim $iBusiness_Hours, $iNotBusiness_Hours

$aMatch = StringRegExp($sStr, '((?m)^((?:[012]?\d|3[01])\.(?:0?\d|1[012])\.\d\d)[^\r]*(?:\r\n)?+(?:(?=\2)(?R))*)', 3)

If IsArray($aMatch) Then
    For $i = 0 To UBound($aMatch)-1 Step 2
        $iBusiness_Hours = 0
        $iNotBusiness_Hours = 0
        $aLines = StringSplit($aMatch[$i], @CRLF, 1)
        
        For $j = 1 To $aLines[0]
            $aSomething = StringRegExp($aLines[$j], '(\d+)\s*(\d+)$', 1)
            If UBound($aSomething) = 2 Then
                Switch Int($aSomething[0])
                    Case 800 To 1800
                        $iBusiness_Hours += Int($aSomething[1])
                    Case Else
                        $iNotBusiness_Hours += Int($aSomething[1])
                EndSwitch
            EndIf
        Next
        ConsoleWrite('Date: ' & $aMatch[$i+1] & @LF & @TAB & 'Business Hours: ' & $iBusiness_Hours & @LF & @TAB & 'Non-Business Hours: ' & $iNotBusiness_Hours & @LF & @LF)
    Next
EndIf

Edited June 25, 2009 by Authenticity

[Khab]

Wow, thats an awesome response. Thanks!