Jump to content
Sign in to follow this  

Parsing a text file, modifying it, rewriting it

Recommended Posts


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
If Header
Get Date & Time of Line
Get Count of Line
If Time >= 0800 AND <= 1800
If exists (CountTotalForDateDay)
 CountTotalForDateDay = CountTotalForDateDay + CountForCurrentLine
 CountTotalForDateDay = CountForCurrentLine
if exists (CountTotalForDateNight)
 CountTotalForDateNight = CountTotalForDateNight + CountForCurrentLine
 CountTotalForDateNight = CountForCurrentLine
if Hour = 23
Write CountTotalForDateDay & Night to file

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.



Share this post

Link to post
Share on other sites

#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])
        ConsoleWrite('Date: ' & $aMatch[$i+1] & @LF & @TAB & 'Business Hours: ' & $iBusiness_Hours & @LF & @TAB & 'Non-Business Hours: ' & $iNotBusiness_Hours & @LF & @LF)

Edited by Authenticity

Share this post

Link to post
Share on other sites

Wow, thats an awesome response. Thanks!

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