Text values analysis

Hi everyone , have a nice day :)

Take a look at this

2007-01-06-0000,20,20,0,0,4,4,16,16,0,0,0,0,0,0,0,0
2007-01-06-0001,28,28,0,0,2,2,25,25,0,0,0,0,1,1,0,0
2007-01-06-0002,18,18,0,0,1,1,15,15,0,0,0,0,2,2,0,0
2007-01-06-0003,20,20,0,0,3,3,17,17,0,0,0,0,0,0,0,0
2007-01-06-0004,16,16,1,1,1,1,13,13,0,0,0,0,1,1,0,0
2007-01-06-0005,18,18,0,0,2,2,16,16,0,0,0,0,0,0,0,0
2007-01-06-0006,17,17,0,0,1,1,16,16,0,0,0,0,0,0,0,0
2007-01-06-0007,31,31,0,0,3,3,27,27,0,0,0,0,1,1,0,0
2007-01-06-0008,23,23,1,1,2,2,20,20,0,0,0,0,0,0,0,0
2007-01-06-0009,19,19,1,1,1,1,17,17,0,0,0,0,0,0,0,0
2007-01-06-0010,22,22,0,0,2,2,19,19,0,0,0,0,1,1,0,0
2007-01-06-0011,20,20,1,1,2,2,16,16,0,0,0,0,1,1,0,0
2007-01-06-0012,16,16,0,0,2,2,12,12,0,0,0,0,2,2,0,0

......................

2007-01-06-2352,20,20,0,0,1,1,19,19,0,0,0,0,0,0,0,0
2007-01-06-2353,25,25,1,1,3,3,19,19,0,0,0,0,2,2,0,0
2007-01-06-2354,15,15,0,0,1,1,13,13,0,0,0,0,1,1,0,0
2007-01-06-2355,17,17,1,1,1,1,14,14,0,0,0,0,1,1,0,0
2007-01-06-2356,21,21,0,0,2,2,19,19,0,0,0,0,0,0,0,0
2007-01-06-2357,19,19,0,0,3,3,15,15,0,0,0,0,1,1,0,0
2007-01-06-2358,18,18,0,0,4,4,12,12,0,0,0,0,2,2,0,0
2007-01-06-2359,28,28,0,0,2,2,25,25,0,0,0,0,1,1,0,0


these are contents of my text file .. start and end of it .. as you can see it has date in begining followed by time ( just before first comma ) and then few fields (values as numbers ) separated by comma .Now what i want to do is that since .. this file is for specific day and fields are taken after every minute (as you can see the time ) .. i want accumulated values of each hour for one specfic day ( jus like you sumup sixty values for every hour to give one value ) ... like .. VB analyzes contents of each line and based on time it adds corresponding comma separated values ( 0000 to 0059 ) for first hour ... and then for second hour ( 0100 to 0159 ) and so on ... i hope i m making myself clear enuff .. if not lemme know

Regards and thanks in advance

You may want to think about using the Split Function

You can use the Split() function, using comma as a separator, to separate each line into date/time and each of the 16 values. Add each value in an array of 16X24, the first value into the first of the 16 elements for that hour, etc.

Once you have some code, if you have specific problems, post the code that's giving you the problem.

OOOOOOOOOOkk . so here it is .. what i have done so far and it is kinda working aswell

assume that i have opened my text file and saved its content in string "txt"
after that i did something like this

VB Code:

lines = Split(txt, vbNewLine)
Open App.Path & "\Air1ainap.txt" For Output As #FB
Debug.Print UBound(lines)
For z = 1 To 1440
'Debug.Print z
For i = z To z + 59
 
 
strvalues = Split(lines(i), ",")
 var1 = var1 + strvalues(2)
 var2 = var2 + strvalues(8)
 
Next i
Debug.Print var1; var2
Print #FB, var1, var2, vbNewLine;
 
var1 = 0
var2 = 0
z = z + 59
Next z
          Close #FB

all variables declared before this code ... this code is actually summing up data on hourly basis and putting required data in separate file !!!!!!
now my constraint is sometimes .. input file doesnt have correct values .. like it can miss storing any one random minute data and making values to be summed up 59 from 60 .. and in such case .. my program crashes
example of this scenario is as ..

2007-01-06-0000,20,20,0,0,4,4,16,16,0,0,0,0,0,0,0,0
2007-01-06-0001,28,28,0,0,2,2,25,25,0,0,0,0,1,1,0,0
2007-01-06-0002,18,18,0,0,1,1,15,15,0,0,0,0,2,2,0,0
2007-01-06-0003,20,20,0,0,3,3,17,17,0,0,0,0,0,0,0,0
2007-01-06-0004,16,16,1,1,1,1,13,13,0,0,0,0,1,1,0,0
2007-01-06-0005,18,18,0,0,2,2,16,16,0,0,0,0,0,0,0,0
2007-01-06-0006,17,17,0,0,1,1,16,16,0,0,0,0,0,0,0,0
2007-01-06-0008,23,23,1,1,2,2,20,20,0,0,0,0,0,0,0,0
2007-01-06-0009,19,19,1,1,1,1,17,17,0,0,0,0,0,0,0,0
2007-01-06-0010,22,22,0,0,2,2,19,19,0,0,0,0,1,1,0,0
2007-01-06-0011,20,20,1,1,2,2,16,16,0,0,0,0,1,1,0,0
2007-01-06-0012,16,16,0,0,2,2,12,12,0,0,0,0,2,2,0,0

......................

2007-01-06-2352,20,20,0,0,1,1,19,19,0,0,0,0,0,0,0,0
2007-01-06-2353,25,25,1,1,3,3,19,19,0,0,0,0,2,2,0,0
2007-01-06-2354,15,15,0,0,1,1,13,13,0,0,0,0,1,1,0,0
2007-01-06-2355,17,17,1,1,1,1,14,14,0,0,0,0,1,1,0,0
2007-01-06-2356,21,21,0,0,2,2,19,19,0,0,0,0,0,0,0,0
2007-01-06-2357,19,19,0,0,3,3,15,15,0,0,0,0,1,1,0,0
2007-01-06-2358,18,18,0,0,4,4,12,12,0,0,0,0,2,2,0,0
2007-01-06-2359,28,28,0,0,2,2,25,25,0,0,0,0,1,1,0,0


there is no data of "2007-01-06-0007" ...
which in ideal case should be there to make total values of 24 hours as 1440 well if data is missing .. i can afford to lose that accuracy like assuming that data as "0" or taking previous minute value .. but definitely i can not afford my program crashing just for this simple server mistake ..
any ideas about how to overcome this :cry:

Regards and thanks

First of all "my program crashes" means nothing.. if you are getting an error, you should always tell us what the error message is, and where it occured.


If you mean that "lines" contains fewer than 1440 items (and you are therefore getting a "Subscript out of range" error), simply use UBound(lines) instead of 1440 on the For loop.

If the file is small then load textstream to a string using Input(LOF(filenumber),#filenumber. You can then use InStr() and InStrRev() to get the positions of the date (date will have to be formatted accordingly for InStr(), InStrRev()) and copy that to a string (eg. strDay As String) using Mid$(). Mid$() length is InStrRev() - InStr(). Then transfer result of Split(strDay, vbCrLf) to an array (eg. strDayLines() As String). Since time always starts with 0000 and increments, then the array index corresponds to the time, as follows:

strDayLines(0) = "2007-01-06-0000,20,20,0,0,4,4,16,16,0,0,0,0,0,0,0,0"
strDayLines(1) = "2007-01-06-0001,28,28,0,0,2,2,25,25,0,0,0,0,1,1,0,0"
'etc etc
strDayLines(2352) = "2007-01-06-2352,20,20,0,0,1,1,19,19,0,0,0,0,0,0,0,0"
strDayLines(2353) = "2007-01-06-2353,25,25,1,1,3,3,19,19,0,0,0,0,2,2,0,0"

So if you want time aggregates for the first hour, for the given day. You iterate from array index 0 to 59... for 3rd hour you iterate from 120 to 179... for nth hour you iterate from (n-1)*60 to (n-1)*60+59.

Before proceeding to the aggregates, I suggest you update your array first. Get rid of the date info (since you already limited it to a day during transfer to strDay), and the time info (since it already corresponds with the array index). Also replace the commas with "+" using Replace() function. You should end up with:

strDayLines(0) = "20+20+0+0+4+4+16+16+0+0+0+0+0+0+0+0"
strDayLines(1) = "28+28+0+0+2+2+25+25+0+0+0+0+1+1+0+0"
'etc etc
strDayLines(2352) = "20+20+0+0+1+1+19+19+0+0+0+0+0+0+0+0"
strDayLines(2353) = "25+25+1+1+3+3+19+19+0+0+0+0+2+2+0+0"

Up to you if you will use the same array to store the sum per array element, or if you will use another array. Either way, you can now use the script control (using its Eval() method) to get the sum per array element.

VB Code:

Dim oScr As ScriptControl  'requires reference
Dim lHour As Long  'you will pass a value to this
Dim lIndex As Long
Dim lSubTotal As Long
 
If (lHour -1) * 60 +59 <= UBound(strDayLines) Then 
   Set oScr = New SCriptControl
   oScr.Language = "vbscript"
   For lIndex = (lHour-1)*60 To (lHour-1)*60 +59
      lSubTotal = lSubTotal + oScr.Eval(strDayLines(lIndex))
   Next
End If
'return lSubTotal

Hmmm...missed the part about missing minute data. Rather than relying on array indices in my previous post, you can just further limit strDay using InStr() and InStrRev() but this time searching for date-time combination instead of just date. strDay will then contain info for the date and time specified.... you then use Split() on strDay and go on as before... iteration will become:

For lIndex = 0 To UBound(strDayLines)

EDIT: BTW, for every array element if there's a trailing comma (which will become a trailing +) either append a zero or truncate the trailing comma. Otherwise Eval() will error on the invalid expression.

Thanks a lot
its almost resolved just one last query :D
i m doing this

VB Code:

for i = 1 to ubound(x)
 
 
next i
 Exit Sub
 
errorhandler:
   Open App.Path & "\Errors.txt" For Output As FB
        Debug.Print "An Error has occured at Block " & z / 60&; vbNewLine
        
Resume Next
'Close FB
End Sub

this is code of my error handler , and if my program gets more then one error , this Errors.txt only shows me the last error , is there way i can put whole error log in this file ?

Erm.. your error handler code is not valid!

You do not set up FB - you should have a line (just before the Open line) like: FB = FreeFile
..you do not close the file, which should be (before the Resume Next line) like: Close FB
..and you also do not write anything to the file!

As to how to keep data that is already in the file, simply change the word Output (on the Open line) to Append.