If-then-ElseIf for parsing file?!

**kiwi** · Oct 16th, 2000, 06:10 AM

I've look and tried - can you help???

Can you masters tell me a) what I'm doing wrong b) how to fix it and c) how to start a new line when a new day is found:

Sample of Raw Data
22:31:23 04/10/00 AB 1
23:03:56 04/10/00 BA 2
23:04:38 04/10/00 AB 5
23:08:49 04/10/00 AB 1
05:31:38 05/10/00 BA 8
06:34:46 05/10/00 AB 10
06:43:53 05/10/00 AB 1
07:11:52 05/10/00 AB 1

result could be:
04/10/00,AB,6(the time),0023,0045,0444,5500,etc up to 13 of them
etc

Private Sub proc_finMC_Click()

Dim ab1_Count, ab2_Count, ab3_Count, ab4_Count, ab5_Count, ab6_Count As Long
Dim lLine As Long
Dim FileNumRead As Integer
Dim FileNumWrite As Integer
Dim sLine As String

Dim sFileName As String
sFileName = App.Path & "\MCexport.txt"
Dim sNewFile As String
sNewFile = App.Path & "\MCFin_Proc.txt"
FileNumRead = FreeFile

Open sFileName For Input As #FileNumRead
FileNumWrite = FreeFile
Open sNewFile For Output As #FileNumWrite

Do Until EOF(FileNumRead)
Line Input #FileNumRead, sLine
Loop

ab1_Count = 0: ab2_Count = 0: ab3_Count = 0: ab4_Count = 0: ab5_Count = 0: ab6_Count = 0

Do While Trim(Left(sLine, 2)) >= "0" And Trim(Left(sLine, 2)) >= "6" 'time btw xx-xx hrs
If Mid(sLine, 19, 2) = "AB" Then ' AtoB
If Trim(Mid(sLine, 22, 2)) = "1" Then 'Class = 1
ab1_Count = ab1_Count + 1
ElseIf CStr(Trim(Mid(sLine, 22, 2))) = "2" Then 'Class = 2
ab2_Count = ab2_Count + 1
ElseIf Trim(Mid(sLine, 22, 2)) = "3" Then 'Class = 3
ab3_Count = ab3_Count + 1
ElseIf Trim(Mid(sLine, 22, 2)) = "4" Then 'Class = 4
ab4_Count = ab4_Count + 1
ElseIf Trim(Mid(sLine, 22, 2)) = "5" Then 'Class = 5
ab5_Count = ab5_Count + 1
Else: 'Class = 6 or something else
ab6_Count = ab6_Count + 1
End If
End If
Loop
Print #FileNumWrite, "AB" & "," & ab1_Count & "," & ab2_Count & "," & ab3_Count & "," & ab4_Count & "," & ab5_Count & "," & ab6_Count
Close #FileNumRead
Close #FileNumWrite
MsgBox "Completed"

**Draez68** · Oct 16th, 2000, 09:38 AM

first off, what is it you are trying to do?

why dont you give us a little more of a description as to what this thing is suppose to do and then we'll go from there.

In your line of code you say that the results could be:
04/10/00,AB,6(the time),0023,0045,0444,5500,etc up to 13 of them

but no where in there do i see you writing the date out in the first part of that line

**kiwi** · Oct 16th, 2000, 04:25 PM

All good points - can you help from the info provided below?? Thanks in anticipation

I am trying to read through a text file of the following format

hh:mm:ss dd/mm/yy AB 1

The file (sample below) needs to be collated in the following manner:
FOR each new DAY, SPLIT that day into 4 parts (0-6, 7-12, 13-18 and 19-24)hours. THEN for all the instances of AB (& BA), determine how many times each of the values 1-13 occur
WRITE the output to a file

The output would look like the following

"filename",dd/mm/yy,6,AB,0001,0030,0040,0000,0001,0040,0050,0013,0050,4400,3300,1200,0000

Where dd/mm/yy is the current day,"6"(or 12,18,24) is the hour group, and the following data (13 lots) are the sums of the occurences

Raw data

13:01:12 04/10/00 AB 1
13:09:05 04/10/00 BA 1
13:09:43 04/10/00 BA 1
13:14:20 04/10/00 BA 13
13:16:28 04/10/00 BA 11
13:18:45 04/10/00 AB 1
13:20:12 04/10/00 BA 1
13:23:07 04/10/00 AB 1
13:26:32 04/10/00 AB 1
13:27:18 04/10/00 AB 1
13:28:17 04/10/00 AB 1
13:34:18 04/10/00 BA 1
13:34:41 04/10/00 BA 3
13:43:00 04/10/00 BA 1
13:43:36 04/10/00 AB 1
13:46:21 04/10/00 AB 1
13:51:24 04/10/00 BA 1
13:51:55 04/10/00 BA 1
14:01:26 04/10/00 BA 1
14:07:17 04/10/00 AB 1
14:11:55 04/10/00 BA 1
14:12:37 04/10/00 BA 1
14:18:45 04/10/00 AB 2
14:21:28 04/10/00 AB 1
14:25:17 04/10/00 BA 1
14:25:52 04/10/00 BA 1
14:32:01 04/10/00 BA 1
14:33:04 04/10/00 AB 1
14:37:14 04/10/00 AB 1
14:43:12 04/10/00 AB 1
14:49:45 04/10/00 BA 1
14:51:07 04/10/00 BA 9
14:54:51 04/10/00 AB 3
15:01:28 04/10/00 BA 1
15:04:06 04/10/00 BA 1
15:04:36 04/10/00 BA 1
15:07:18 04/10/00 AB 1
15:09:07 04/10/00 BA 1
15:12:48 04/10/00 AB 9
15:17:09 04/10/00 AB 7
15:20:31 04/10/00 BA 1
15:21:30 04/10/00 BA 1
15:23:06 04/10/00 AB 1
15:31:46 04/10/00 BA 4
15:34:24 04/10/00 AB 1
15:37:08 04/10/00 BA 1
15:39:38 04/10/00 AB 1
15:43:33 04/10/00 AB 1
15:44:42 04/10/00 AB 3
15:46:31 04/10/00 BA 1
15:47:27 04/10/00 AB 1
15:48:10 04/10/00 BA 1
15:49:25 04/10/00 BA 1
15:50:21 04/10/00 AB 1
15:54:43 04/10/00 BA 1
15:59:24 04/10/00 AB 1
16:00:05 04/10/00 AB 1
16:02:26 04/10/00 AB 1
16:03:58 04/10/00 AB 3
16:05:07 04/10/00 AB 1
16:05:51 04/10/00 BA 1
16:06:52 04/10/00 BA 1
16:07:28 04/10/00 AB 1
16:10:23 04/10/00 AB 1
16:10:56 04/10/00 AB 2
16:11:46 04/10/00 AB 1
16:18:15 04/10/00 BA 1
16:19:59 04/10/00 BA 1
16:22:40 04/10/00 BA 1
16:23:26 04/10/00 AB 1
16:25:10 04/10/00 AB 1
16:26:56 04/10/00 AB 1
16:28:35 04/10/00 AB 1
16:29:40 04/10/00 BA 1
16:32:30 04/10/00 AB 1
16:37:51 04/10/00 BA 1
16:40:00 04/10/00 AB 1
16:41:00 04/10/00 AB 1
16:41:57 04/10/00 BA 1
16:42:51 04/10/00 BA 1
16:58:51 04/10/00 BA 1
17:12:09 04/10/00 AB 1
17:20:22 04/10/00 AB 9
17:23:24 04/10/00 BA 1
17:24:47 04/10/00 AB 1
17:25:26 04/10/00 AB 1
17:27:41 04/10/00 BA 1
17:31:25 04/10/00 BA 1
17:35:24 04/10/00 BA 3
17:37:09 04/10/00 BA 1
17:37:52 04/10/00 BA 1
17:39:20 04/10/00 BA 1
17:40:40 04/10/00 AB 1
17:41:43 04/10/00 AB 1
17:50:00 04/10/00 AB 1
17:52:27 04/10/00 BA 1
18:01:19 04/10/00 BA 1
18:01:24 04/10/00 BA 1
18:08:52 04/10/00 BA 1
18:22:06 04/10/00 BA 1
18:26:35 04/10/00 BA 1
18:27:09 04/10/00 AB 1
18:28:39 04/10/00 AB 1
18:29:16 04/10/00 AB 1
18:29:26 04/10/00 BA 1
18:30:14 04/10/00 AB 1
18:32:41 04/10/00 BA 1
18:40:15 04/10/00 BA 1
18:41:05 04/10/00 BA 1
18:49:14 04/10/00 AB 1
18:50:06 04/10/00 AB 1
18:54:57 04/10/00 BA 1
18:55:19 04/10/00 AB 1
18:57:51 04/10/00 BA 1
19:00:34 04/10/00 AB 1
19:04:00 04/10/00 AB 1
19:04:55 04/10/00 BA 1
19:10:10 04/10/00 BA 1
19:12:33 04/10/00 AB 1
19:25:41 04/10/00 BA 1
19:25:50 04/10/00 AB 1
19:28:23 04/10/00 BA 1
19:33:33 04/10/00 AB 1
19:50:07 04/10/00 BA 1
19:56:07 04/10/00 AB 1
20:00:48 04/10/00 AB 1
20:11:22 04/10/00 AB 1
20:14:27 04/10/00 BA 1
20:15:16 04/10/00 BA 1
20:16:46 04/10/00 BA 1
20:53:20 04/10/00 BA 1
21:12:02 04/10/00 BA 1
21:13:48 04/10/00 BA 1
21:17:47 04/10/00 BA 1
21:19:26 04/10/00 AB 1
21:25:43 04/10/00 BA 1
21:32:44 04/10/00 BA 1
21:34:50 04/10/00 BA 1
21:44:24 04/10/00 BA 1
21:47:59 04/10/00 AB 1
22:03:46 04/10/00 BA 1
22:15:49 04/10/00 BA 1
22:31:23 04/10/00 AB 1
23:03:56 04/10/00 BA 1
23:04:38 04/10/00 AB 1
23:08:49 04/10/00 AB 1
05:31:38 05/10/00 BA 1
06:34:46 05/10/00 AB 1
06:43:53 05/10/00 AB 1
07:11:52 05/10/00 AB 1
07:14:32 05/10/00 BA 2
07:16:25 05/10/00 AB 1
07:31:15 05/10/00 AB 1
07:41:00 05/10/00 AB 1
07:44:10 05/10/00 AB 1
07:51:02 05/10/00 AB 1
07:53:36 05/10/00 AB 1
07:53:54 05/10/00 AB 1
07:54:15 05/10/00 AB 1
07:54:36 05/10/00 AB 1
07:54:51 05/10/00 AB 1
08:05:05 05/10/00 BA 1
08:13:15 05/10/00 BA 1
08:25:24 05/10/00 AB 1
08:28:56 05/10/00 BA 1
08:37:14 05/10/00 AB 1

**Draez68** · Oct 16th, 2000, 06:38 PM

alot of things on your's is not correct. for one you don't have a variable to keep the values in. by that i mean, as you read your line from the input file you dont save it anywhere. it just gets over read by the next line it reads.
i changed it around with arrays to show you what i mean. in your example you have only 6 of the possible 13 so that is how i left it. you can just expand what i have done from 6 to 13 and you should have a working program.

i tested this before i posted it with the sample data you attached in your post and it seems to work.... here it is:

Code:

Dim ab1_Count(4), ab2_Count(4), ab3_Count(4), ab4_Count(4), ab5_Count(4), ab6_Count(4) As Long
Dim ba1_Count(4), ba2_Count(4), ba3_Count(4), ba4_Count(4), ba5_Count(4), ba6_Count(4) As Long

Dim lLine As Long

Dim FileNumRead As Integer
Dim FileNumWrite As Integer
Dim sLine As String
Dim strDate As String
Dim sFileName As String
sFileName = "c:\MCexport.txt"
Dim sNewFile As String
sNewFile = "c:\MCFin_Proc.txt"
FileNumRead = FreeFile
strDate = "00/00/00"
Open sFileName For Input As #FileNumRead
    FileNumWrite = FreeFile
    Open sNewFile For Output As #FileNumWrite
    
        Do Until EOF(FileNumRead)
            Line Input #FileNumRead, sLine
            If Mid(sLine, 19, 2) = "AB" Then
                Select Case Val(Trim(Left(sLine, 2)))
                    Case 0 To 6
                         ' AtoB
                        If Trim(Mid(sLine, 22, 2)) = "1" Then 'Class = 1
                            ab1_Count(1) = ab1_Count(1) + 1
                        ElseIf CStr(Trim(Mid(sLine, 22, 2))) = "2" Then 'Class = 2
                            ab2_Count(1) = ab2_Count(1) + 1
                        ElseIf Trim(Mid(sLine, 22, 2)) = "3" Then 'Class = 3
                            ab3_Count(1) = ab3_Count(1) + 1
                        ElseIf Trim(Mid(sLine, 22, 2)) = "4" Then 'Class = 4
                            ab4_Count(1) = ab4_Count(1) + 1
                        ElseIf Trim(Mid(sLine, 22, 2)) = "5" Then 'Class = 5
                            ab5_Count(1) = ab5_Count(1) + 1
                        Else: 'Class = 6 or something else
                            ab6_Count(1) = ab6_Count(1) + 1
                        End If
                       
                    Case 7 To 12
                        
                        If Trim(Mid(sLine, 22, 2)) = "1" Then 'Class = 1
                            ab1_Count(2) = ab1_Count(2) + 1
                        ElseIf CStr(Trim(Mid(sLine, 22, 2))) = "2" Then 'Class = 2
                            ab2_Count(2) = ab2_Count(2) + 1
                        ElseIf Trim(Mid(sLine, 22, 2)) = "3" Then 'Class = 3
                            ab3_Count(2) = ab3_Count(2) + 1
                        ElseIf Trim(Mid(sLine, 22, 2)) = "4" Then 'Class = 4
                            ab4_Count(2) = ab4_Count(2) + 1
                        ElseIf Trim(Mid(sLine, 22, 2)) = "5" Then 'Class = 5
                            ab5_Count(2) = ab5_Count(2) + 1
                        Else: 'Class = 6 or something else
                            ab6_Count(2) = ab6_Count(2) + 1
                        End If
                        
                    Case 13 To 18
                        
                        If Trim(Mid(sLine, 22, 2)) = "1" Then 'Class = 1
                            ab1_Count(3) = ab1_Count(3) + 1
                        ElseIf CStr(Trim(Mid(sLine, 22, 2))) = "2" Then 'Class = 2
                            ab2_Count(3) = ab2_Count(3) + 1
                        ElseIf Trim(Mid(sLine, 22, 2)) = "3" Then 'Class = 3
                            ab3_Count(3) = ab3_Count(3) + 1
                        ElseIf Trim(Mid(sLine, 22, 2)) = "4" Then 'Class = 4
                            ab4_Count(3) = ab4_Count(3) + 1
                        ElseIf Trim(Mid(sLine, 22, 2)) = "5" Then 'Class = 5
                            ab5_Count(3) = ab5_Count(3) + 1
                        Else: 'Class = 6 or something else
                            ab6_Count(3) = ab6_Count(3) + 1
                        End If
                        
                    Case 19 To 24
                        
                        If Trim(Mid(sLine, 22, 2)) = "1" Then 'Class = 1
                            ab1_Count(4) = ab1_Count(4) + 1
                        ElseIf CStr(Trim(Mid(sLine, 22, 2))) = "2" Then 'Class = 2
                            ab2_Count(4) = ab2_Count(4) + 1
                        ElseIf Trim(Mid(sLine, 22, 2)) = "3" Then 'Class = 3
                            ab3_Count(4) = ab3_Count(4) + 1
                        ElseIf Trim(Mid(sLine, 22, 2)) = "4" Then 'Class = 4
                            ab4_Count(4) = ab4_Count(4) + 1
                        ElseIf Trim(Mid(sLine, 22, 2)) = "5" Then 'Class = 5
                            ab5_Count(4) = ab5_Count(4) + 1
                        Else: 'Class = 6 or something else
                            ab6_Count(4) = ab6_Count(4) + 1
                        End If
                        
                End Select
            Else
                Select Case Val(Trim(Left(sLine, 2)))
                    Case 0 To 6
                         ' BtoA
                        If Trim(Mid(sLine, 22, 2)) = "1" Then 'Class = 1
                            ba1_Count(1) = ba1_Count(1) + 1
                        ElseIf CStr(Trim(Mid(sLine, 22, 2))) = "2" Then 'Class = 2
                            ba2_Count(1) = ba2_Count(1) + 1
                        ElseIf Trim(Mid(sLine, 22, 2)) = "3" Then 'Class = 3
                            ba3_Count(1) = ba3_Count(1) + 1
                        ElseIf Trim(Mid(sLine, 22, 2)) = "4" Then 'Class = 4
                            ba4_Count(1) = ba4_Count(1) + 1
                        ElseIf Trim(Mid(sLine, 22, 2)) = "5" Then 'Class = 5
                            ba5_Count(1) = ba5_Count(1) + 1
                        Else: 'Class = 6 or something else
                            ba6_Count(1) = ba6_Count(1) + 1
                        End If
                       
                    Case 7 To 12
                        
                        If Trim(Mid(sLine, 22, 2)) = "1" Then 'Class = 1
                            ba1_Count(2) = ba1_Count(2) + 1
                        ElseIf CStr(Trim(Mid(sLine, 22, 2))) = "2" Then 'Class = 2
                            ba2_Count(2) = ba2_Count(2) + 1
                        ElseIf Trim(Mid(sLine, 22, 2)) = "3" Then 'Class = 3
                            ba3_Count(2) = ba3_Count(2) + 1
                        ElseIf Trim(Mid(sLine, 22, 2)) = "4" Then 'Class = 4
                            ba4_Count(2) = ba4_Count(2) + 1
                        ElseIf Trim(Mid(sLine, 22, 2)) = "5" Then 'Class = 5
                            ba5_Count(2) = ba5_Count(2) + 1
                        Else: 'Class = 6 or something else
                            ba6_Count(2) = ba6_Count(2) + 1
                        End If
                        
                    Case 13 To 18
                        
                        If Trim(Mid(sLine, 22, 2)) = "1" Then 'Class = 1
                            ba1_Count(3) = ba1_Count(3) + 1
                        ElseIf CStr(Trim(Mid(sLine, 22, 2))) = "2" Then 'Class = 2
                            ba2_Count(3) = ba2_Count(3) + 1
                        ElseIf Trim(Mid(sLine, 22, 2)) = "3" Then 'Class = 3
                            ba3_Count(3) = ba3_Count(3) + 1
                        ElseIf Trim(Mid(sLine, 22, 2)) = "4" Then 'Class = 4
                            ba4_Count(3) = ba4_Count(3) + 1
                        ElseIf Trim(Mid(sLine, 22, 2)) = "5" Then 'Class = 5
                            ba5_Count(3) = ba5_Count(3) + 1
                        Else: 'Class = 6 or something else
                            ba6_Count(3) = ba6_Count(3) + 1
                        End If
                        
                    Case 19 To 24
                        
                        If Trim(Mid(sLine, 22, 2)) = "1" Then 'Class = 1
                            ba1_Count(4) = ba1_Count(4) + 1
                        ElseIf CStr(Trim(Mid(sLine, 22, 2))) = "2" Then 'Class = 2
                            ba2_Count(4) = ba2_Count(4) + 1
                        ElseIf Trim(Mid(sLine, 22, 2)) = "3" Then 'Class = 3
                            ba3_Count(4) = ba3_Count(4) + 1
                        ElseIf Trim(Mid(sLine, 22, 2)) = "4" Then 'Class = 4
                            ba4_Count(4) = ba4_Count(4) + 1
                        ElseIf Trim(Mid(sLine, 22, 2)) = "5" Then 'Class = 5
                            ba5_Count(4) = ba5_Count(4) + 1
                        Else: 'Class = 6 or something else
                            ba6_Count(4) = ba6_Count(4) + 1
                        End If
                        
                End Select
            End If
            If Mid(sLine, 10, 8) <> strDate Then
               strDate = Mid(sLine, 10, 8)
               'ab
               Print #FileNumWrite, strDate & ", 6, " & "AB" & "," & Str(ab1_Count(1)) & "," & Str(ab2_Count(1)) & "," & Str(ab3_Count(1)) & "," & Str(ab4_Count(1)) & "," & Str(ab5_Count(1)) & "," & Str(ab6_Count(1))
               Print #FileNumWrite, strDate & ", 12, " & "AB" & "," & Str(ab1_Count(2)) & "," & Str(ab2_Count(2)) & "," & Str(ab3_Count(2)) & "," & Str(ab4_Count(2)) & "," & Str(ab5_Count(2)) & "," & Str(ab6_Count(2))
               Print #FileNumWrite, strDate & ", 18, " & "AB" & "," & Str(ab1_Count(3)) & "," & Str(ab2_Count(3)) & "," & Str(ab3_Count(3)) & "," & Str(ab4_Count(3)) & "," & Str(ab5_Count(3)) & "," & Str(ab6_Count(3))
               Print #FileNumWrite, strDate & ", 24, " & "AB" & "," & Str(ab1_Count(4)) & "," & Str(ab2_Count(4)) & "," & Str(ab3_Count(4)) & "," & Str(ab4_Count(4)) & "," & Str(ab5_Count(4)) & "," & Str(ab6_Count(4))
               'ba
               Print #FileNumWrite, strDate & ", 6, " & "BA" & "," & Str(ba1_Count(1)) & "," & Str(ba2_Count(1)) & "," & Str(ba3_Count(1)) & "," & Str(ba4_Count(1)) & "," & Str(ba5_Count(1)) & "," & Str(ba6_Count(1))
               Print #FileNumWrite, strDate & ", 12, " & "BA" & "," & Str(ba1_Count(2)) & "," & Str(ba2_Count(2)) & "," & Str(ba3_Count(2)) & "," & Str(ba4_Count(2)) & "," & Str(ba5_Count(2)) & "," & Str(ba6_Count(2))
               Print #FileNumWrite, strDate & ", 18, " & "BA" & "," & Str(ba1_Count(3)) & "," & Str(ba2_Count(3)) & "," & Str(ba3_Count(3)) & "," & Str(ba4_Count(3)) & "," & Str(ba5_Count(3)) & "," & Str(ba6_Count(3))
               Print #FileNumWrite, strDate & ", 24, " & "BA" & "," & Str(ba1_Count(4)) & "," & Str(ba2_Count(4)) & "," & Str(ba3_Count(4)) & "," & Str(ba4_Count(4)) & "," & Str(ba5_Count(4)) & "," & Str(ba6_Count(4))
               For x = 1 To 4
                ab1_Count(x) = 0
                ab2_Count(x) = 0
                ab3_Count(x) = 0
                ab4_Count(x) = 0
                ba1_Count(x) = 0
                ba2_Count(x) = 0
                ba3_Count(x) = 0
                ba4_Count(x) = 0
               Next x
            End If
        Loop
    Close #FileNumRead
Close #FileNumWrite
MsgBox "Completed"

let me know if you understand it or if you have any questions.

good luck!

**PaulLewis** · Oct 16th, 2000, 08:08 PM

Well I will not post my code here but let me know kiwi if it works for you.

You'll note that I do not employ if else endif at all in my code, but that doesn't mean it's wrong to do so.

As far as I'm concerned, if it works, and you learn from it then it's fine

Cheers

**Draez68** · Oct 16th, 2000, 09:45 PM

This is not the way I would do it either but changing the code too drastically might confuse Kiwi more than help him/her so I decided to change as little as I could.

**kiwi** · Oct 17th, 2000, 04:12 AM

Thanks people. I have read through this, and am doing my
best to understand the (simple) concepts of arrays

Can you show me how you would have done this??

I appreciate your help - there is nothing like examples to
aid learning VB etc.

OH - the variable x was not declared in your code
Kiwi

**PaulLewis** · Oct 17th, 2000, 04:57 AM

I posted you a sample at [email protected]

Let me know if you want it resent.

Regards

**Draez68** · Oct 17th, 2000, 08:27 AM

Hey Paul,
Why the heck dont you just explain it here so that others who might be looking for an answer to 'what are arrays' will also have the text to peruse?

Kiwi,
In any case, arrays are simple in concept but can get difficult.

An array in it's simplest form allows you to store many values under the same variable. for example, if you dimension an array of fruits to 5 then you basically have five containers under the same name. The following example dimensions fruits to 5 containers and as a string type:

dim fruits(5) as string

that gives you basically this:
fruit(0)
fruit(1)
fruit(2)
fruit(3)
fruit(4)

it's important to note that arrays begin at 0 and not 1 unless you state this in your code:
option explicit

doing that will make the first container in the array start at 1 instead of 0

so, if we do this:
option explicit
dim fruits(5) as string

then, we get this instead:
fruits(1)
fruits(2)
fruits(3)
fruits(4)
fruits(5)

see the difference?
the best reason for stating 'option explicit' is for when you are using a for...next or do while..loop since it's less confusing in the long run

be aware that if you use 'option explicit' it will make every array you use in your code begin at container one instead of zero.

ok, so let's say we went with the later example:

option explicit
dim fruits(5) as string

how do we use this? simple, check this out:

dim i as string
for i = 1 to 5
fruits(i) = "example - " & trim(str(i))
next i

that example will fill all five containers within the string variable "fruits" with text the results of which should be something like this:

fruits(1) = "example - 1"
fruits(2) = "example - 2"
fruits(3) = "example - 3"
fruits(4) = "example - 4"
fruits(5) = "example - 5"

the "trim(str(i))" part just uses the counter to change the text a little for each container so you can see the difference more easily. the "str" part changes the integer values to a string so they are all of the same type and trim just takes care of any spaces before or after the string you are converting (i do this just in case but it's not always necessary)

so, if you wanted to fill a text file with the values of this array you would want to do something like this:

dim j as integer
open "c:\test.txt" for output as #1
for j = 1 to 5
print line #1, fruits(j)
next j
close #1

once this is done you would have a text file with:

example - 1
example - 2
example - 3
example - 4
example - 5

That should be enough to get you started. Also, there are multi-dimensional arrays (sounds like something out of science fiction ehh?). To define them the format would be something like:

dim fruits(5,5) as string

that would dim five containers within each of five containers. the easiest way to think of it is as a classroom of desks (or for that matter an Excel worksheet).

in the classroom example it would be basically 5 rows down and 5 rows across of desks, visually like this:

1|2|3|4|5
----------
1| | | | | |
2| | | | | |
3| | | | | |
4| | | | | |
5| | | | | |

and it can get even whackier, but that's a whole other story.

Hope this helps!

**PaulLewis** · Oct 17th, 2000, 01:27 PM

And it would not have been much different to what you
posted on here already so I emailed it to his profile
address in case he could use it. You see when I finished
writing it, you had already posted your help so I just
emailed it instead (so as not to let the work go to waste
you know).

kiwi can post it up here if he uses it...otherwise it
really won't help anyone else at all I think

Cheers

**Draez68** · Oct 17th, 2000, 02:25 PM

whatever, just dont know why you wouldnt have posted it here in the first place so we can all benefit from the wisdom.

**PaulLewis** · Oct 17th, 2000, 04:40 PM

Here is the post. The whole form file is included in case you wish to see the controls on my form.

Code:

VERSION 5.00
Begin VB.Form Form1 
   Caption         =   "Form1"
   ClientHeight    =   2310
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   6075
   LinkTopic       =   "Form1"
   ScaleHeight     =   2310
   ScaleWidth      =   6075
   StartUpPosition =   3  'Windows Default
   Begin VB.TextBox txtOutputFile 
      Height          =   285
      Left            =   1920
      TabIndex        =   2
      Text            =   "d:\!dev\VB-World\Kiwi\output.txt"
      Top             =   840
      Width           =   3855
   End
   Begin VB.CommandButton cmdParse 
      Caption         =   "Just Do It"
      Height          =   375
      Left            =   1920
      TabIndex        =   1
      Top             =   1440
      Width           =   2175
   End
   Begin VB.TextBox txtInputFile 
      Height          =   285
      Left            =   1920
      TabIndex        =   0
      Text            =   "d:\!dev\VB-World\kiwi\input.txt"
      Top             =   240
      Width           =   3855
   End
   Begin VB.Label Label2 
      Alignment       =   1  'Right Justify
      Caption         =   "Source Data:"
      Height          =   255
      Left            =   240
      TabIndex        =   4
      Top             =   240
      Width           =   1575
   End
   Begin VB.Label Label1 
      Alignment       =   1  'Right Justify
      Caption         =   "Output To:"
      Height          =   255
      Left            =   240
      TabIndex        =   3
      Top             =   840
      Width           =   1575
   End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Private Enum DataTypes
  AB = 1
  BA = 2
End Enum

Private Enum DataPeriod
  p6 = 1
  p12 = 2
  p18 = 3
  p24 = 4
End Enum

Private Type SummaryRecord
  mCount(DataPeriod.p6 To DataPeriod.p24, 1 To 13, 1 To 2) As Integer
End Type

Private Type Summary
  mDate As Date
  m6 As SummaryRecord
  m12 As SummaryRecord
  m18 As SummaryRecord
  m24 As SummaryRecord
End Type

Private mySummary() As Summary
Private nSummary As Integer  'count
Private myIndex As Collection
Private mFilename As String



Private Sub cmdParse_Click()
  ParseFile txtInputFile, txtOutputFile
End Sub

Private Sub ParseFile(inputFile As String, outputFile As String)
  Set myIndex = New Collection
  nSummary = 0
  
  mFilename = inputFile
  Dim hInput As Long, hOutput As Long
  
  Dim tmp As String
  Dim tmpData() As String
  
  hInput = FreeFile
  Open inputFile For Input As #hInput
  While Not EOF(hInput)
    Line Input #hInput, tmp
    If tmp <> "" Then
      tmpData = Split(tmp, " ")
      AddToSummary tmpData
    End If
    
  Wend
  Close #hInput

  If outputFile <> "" Then
    hOutput = FreeFile
    Open outputFile For Output As #hOutput
    OutputSummary hOutput
    Close #hOutput
  
  End If
End Sub

Private Sub AddToSummary(tmpData() As String)
  Dim thisIndex As Integer
  Dim myTime As Date, myDate As Date
  Dim myType As Integer
  Dim myID As Integer
  Dim msg As String
  Dim c As Integer
  
  ' clear any previous errors
  Err.Clear
  On Error Resume Next
    'parse the date/time
    myTime = CDate(tmpData(0))
    myDate = CDate(tmpData(1))
    Select Case tmpData(2)
    Case "AB"
      myType = DataTypes.AB
    Case "BA"
      myType = DataTypes.BA
    End Select
    myID = CInt(tmpData(3))
  
    If Err <> 0 Then
      msg = "Could not parse data: "
      For c = 0 To UBound(tmpData)
        msg = msg & vbCrLf & tmpData(c)
      Next
      ReportError msg
    End If
  
  ' look for this date in the summary collection
  thisIndex = myIndex(CStr(myDate))
  
  If Err <> 0 Then
    ' not found - so create one
    Err.Clear
    nSummary = nSummary + 1
    thisIndex = nSummary - 1
    ReDim Preserve mySummary(thisIndex)
    
    mySummary(thisIndex).mDate = myDate + myTime
    myIndex.Add thisIndex, CStr(myDate)
  End If
  
  ' now figure out the time period
  With mySummary(thisIndex)
    Select Case myTime
    Case Is < TimeSerial(7, 0, 0)
      .m6.mCount(DataPeriod.p6, myID, myType) = .m6.mCount(DataPeriod.p6, myID, myType) + 1
      
    Case Is < TimeSerial(13, 0, 0)
      .m12.mCount(DataPeriod.p12, myID, myType) = .m12.mCount(DataPeriod.p12, myID, myType) + 1
      
    Case Is < TimeSerial(19, 0, 0)
      .m18.mCount(DataPeriod.p18, myID, myType) = .m18.mCount(DataPeriod.p18, myID, myType) + 1
    
    Case Else
      .m24.mCount(DataPeriod.p24, myID, myType) = .m24.mCount(DataPeriod.p24, myID, myType) + 1
    
    End Select
  End With
  
  
  On Error GoTo 0
  
End Sub
Private Sub OutputSummary(hfile As Long)
  Dim c As Integer
  For c = 1 To nSummary
    OutputSummaryRecord hfile, c
  Next
End Sub

Private Sub OutputSummaryRecord(hfile As Long, index As Integer)
  Dim c As Integer, d As Integer
  With mySummary(index - 1)
    Print #hfile, mFilename & "," & DateValue(.mDate) & "," & "6,AB,";
    For c = 1 To 13
      Print #hfile, .m6.mCount(DataPeriod.p6, c, 1);
      If c = 13 Then Print #hfile, Else Print #hfile, ",";
    Next
        
    Print #hfile, mFilename & "," & DateValue(.mDate) & "," & "6,BA,";
    For c = 1 To 13
      Print #hfile, .m6.mCount(DataPeriod.p6, c, 1);
      If c = 13 Then Print #hfile, Else Print #hfile, ",";
    Next
    
    
    Print #hfile, mFilename & "," & DateValue(.mDate) & "," & "12,AB,";
    For c = 1 To 13
      Print #hfile, .m12.mCount(DataPeriod.p12, c, 1);
      If c = 13 Then Print #hfile, Else Print #hfile, ",";
    Next
        
    Print #hfile, mFilename & "," & DateValue(.mDate) & "," & "12,BA,";
    For c = 1 To 13
      Print #hfile, .m12.mCount(DataPeriod.p12, c, 1);
      If c = 13 Then Print #hfile, Else Print #hfile, ",";
    Next
    
    
    Print #hfile, mFilename & "," & DateValue(.mDate) & "," & "18,AB";
    For c = 1 To 13
      Print #hfile, .m18.mCount(DataPeriod.p18, c, 1);
      If c = 13 Then Print #hfile, Else Print #hfile, ",";
    Next
        
    Print #hfile, mFilename & "," & DateValue(.mDate) & "," & "18,BA,";
    For c = 1 To 13
      Print #hfile, .m18.mCount(DataPeriod.p18, c, 1);
      If c = 13 Then Print #hfile, Else Print #hfile, ",";
    Next
    
    
    Print #hfile, mFilename & "," & DateValue(.mDate) & "," & "24,AB";
    For c = 1 To 13
      Print #hfile, .m24.mCount(DataPeriod.p24, c, 1);
      If c = 13 Then Print #hfile, Else Print #hfile, ",";
    Next
        
    Print #hfile, mFilename & "," & DateValue(.mDate) & "," & "24,BA,";
    For c = 1 To 13
      Print #hfile, .m24.mCount(DataPeriod.p24, c, 1);
      If c = 13 Then Print #hfile, Else Print #hfile, ",";
    Next
    
    
    Print #hfile, mFilename & "," & DateValue(.mDate) & "," & "6,AB,";
    For c = 1 To 13
      Print #hfile, .m6.mCount(DataPeriod.p6, c, 1);
      If c = 13 Then Print #hfile, Else Print #hfile, ",";
    Next
        
    Print #hfile, mFilename & "," & DateValue(.mDate) & "," & "6,BA,";
    For c = 1 To 13
      Print #hfile, .m6.mCount(DataPeriod.p6, c, 1);
      If c = 13 Then Print #hfile, Else Print #hfile, ",";
    Next
    
  End With
End Sub

Private Sub ReportError(msg As String)
  MsgBox msg, vbApplicationModal Or vbCritical Or vbOKOnly, "Error"
  End
End Sub

Cheers

**Draez68** · Oct 18th, 2000, 07:53 AM

Somehow I think this would have confused Kiwi more than helped since there's no explanation (at least none that you posted). LOL

**PaulLewis** · Oct 18th, 2000, 01:26 PM

Draez68, that's more or less along the lines of what I was saying. I only did the whole solution because in his profile (and the nickname) it appears he is a New Zealander like me

So, no apologies for posting an ugly undocumented piece of code because like I said before...YOU asked for it

haha

If I was doing it over, I would use ADO instead. ADO can treat text files as database tables. Then it would be a matter of moving through a recordset like "normal" and writing out to another table. If I do that solution (for fun) I'll be sure to document it well for ya and post it too

Cheers

**kiwi** · Oct 18th, 2000, 02:20 PM

cheers chaps

Yes - that code did throw me (a one eyed Cantab.)
Paul, if you really want to do this ADO for fun,
I would of course be really interested. Learn learn learn
I say.

kiwi

**Draez68** · Oct 18th, 2000, 04:54 PM

Paul,
Dont get me wrong.. i read through it and it's a very cool implementation. I just was saying that for a beginner it's alot to take in all in one setting being that you emailed it to 'em.

coding, dont ya just love it?

i'm getting a beta of VB7 from Microsoft. I am doing some work for them so I requested it. I wonder when it will show up!!!

**kiwi** · Oct 18th, 2000, 06:13 PM

Well guys, this is more a common dialog question: YES, I have looked through the posted replies first!. Would appreciate your input:

Heres the start of the code: I want to pass the open Freefile names handle? eg: if the file is kiwi.eco, then the "kiwi" string, to sNewFile eg the file that is being written to, giving it a new extension eg write a file named kiwi.txt

SO, I open kiwi.eco to read from, and I open kiwi.txt to write to. Of course "kiwi" could be anything. cheers cheers cheers

Private Sub procMC_Click()

Dim sLine As String
Dim FileNumWrite As Integer
Dim lData1, lData2, lData3, lData4 As String

Dim strFilter As String 'Common dialog filter string
Dim strFileName As String 'String of file to open
Dim FileHandleRead% ' Variable to hold file handle

strFilter = "MC500(*.eco)|*.eco|Text File(*.txt)|*.txt|All files(*.*)|*.*" 'Set the common dialog filter
cdMain.Filter = strFilter

cdMain.ShowOpen ' Open the common dialog

If cdMain.FileName <> "" Then ' Make sure the retrieved filename is not a blank string
strFileName = cdMain.FileName ' if it is not blank open the file
End If

Dim sNewFile As String
sNewFile = App.Path & "\MC5500 Pre_Proc.txt"
FileHandleRead% = FreeFile 'Get a free file handle and assign it to the file handle variable
Open strFileName For Input As #FileHandleRead% 'Open the file
FileNumWrite = FreeFile
Open sNewFile For Output As #FileNumWrite

Do Until UCase(Left(sLine, 2)) = "HH" Or EOF(FileHandleRead%) ' want to find the end of file
Line Input #FileHandleRead%, sLine
Loop

Screen.MousePointer = vbHourglass ' Change mouse pointer to hourglass.

Do Until EOF(FileHandleRead%) ' code here to process line of data

etc......................................

[Edited by kiwi on 10-18-2000 at 07:17 PM]

**PaulLewis** · Oct 18th, 2000, 10:05 PM

I still can't get over how nice it is to treat the text file as "just a datasource" which after all is all it is.

Here's what to do:
drop this file (called schema.ini) into your data directory. This is where your source data is kept. My source data was called input.txt.

Code:

[input.txt]
ColNameHeader=False
Format=FixedLength
MaxScanRows=25
CharacterSet=ANSI
Col1=MTIME Char Width 9
Col2=MDATE Char Width 9
Col3=MTYPE Char Width 3
Col4=MVAL Char Width 3

This file describes to ADO how to read the input.txt file.

Now, on a form, make sure you select Microsoft ActiveX Data Objects Library 2.x. Mine is 2.1 at present.

Now, drop this code on a new form (Mine is called form1) Add a button to the form. You will have to modify the connection string to reflect the actual path to your data files (do NOT put the file name in there though)

Code:

Option Explicit
Dim myRs As ADODB.Recordset
Dim myConn As ADODB.Connection

Private Sub Command1_Click()
  Set myRs = New ADODB.Recordset
  With myRs
    .Open "Select * from input.txt where [MVAL]='13'", myConn, adOpenStatic, adLockReadOnly
    .MoveFirst
    While Not .EOF
      Debug.Print .Fields(3).Name, .Fields(0), .Fields(1), .Fields(2), .Fields(3)
      .MoveNext
    Wend
  End With
End Sub

Private Sub Form_Load()
  Set myConn = New ADODB.Connection
  myConn.Open "DefaultDir=D:\!dev\VB-World\Kiwi;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;Extensions=asc,csv,tab,txt;FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"
End Sub

That's IT. I mean, the code will run a standard SQL query on the "table" which is known by it's file name and return results.

In my data file, there is only one line where MVAL = 13 and guess what? I only get one row returned.

I know this is a short post (for me I mean) but I wanted you to see how easy it was to manipulate text files with ADO. I think it rocks!

Cheers

**PaulLewis** · Oct 18th, 2000, 10:48 PM

Further to my last post, here is another button and sql I added to the project which simply print out a transormation of the input file. This recordset returns pretty much exactly what kiwi wants I think. Well worth a look I'd say!

(Saves alot of parsing anyhow - Of course, you have to write SQL but that's a small price to pay!!)

Code:

Private Sub Command3_Click()
  Dim sql As String
  sql = "TRANSFORM Count([MVAL]) AS Count " & _
        "SELECT [MDATE], (Int(Hour([MTIME])/6)+1)*6 AS Period, [MTYPE] AS Type " & _
        "FROM Input.txt " & _
        "GROUP BY [MDATE], (Int(Hour([MTIME])/6)+1)*6, [MTYPE] " & _
        "ORDER BY [MDATE], (Int(Hour([MTIME])/6)+1)*6, [MTYPE] " & _
        "PIVOT [MVAL]; "
  
  Dim fld As ADODB.Field
  Set myRs = New ADODB.Recordset
  
  With myRs
    .Open sql, myConn
    .MoveFirst
    For Each fld In .Fields
      Debug.Print fld.Name,
    Next
    Debug.Print

    Do Until .EOF
      For Each fld In .Fields
        Debug.Print fld.Value,
      Next
      .MoveNext
      Debug.Print
    Loop
  End With

End Sub

**kiwi** · Oct 18th, 2000, 10:54 PM

Yep, I'm impressed - its certainly short and sweet.
In what area(s) does this method work best?, apart from the
one its applied to here eg every dataset/recordset you'd ever come across??

And the best place to learn this stuff is....

Oh... any ideas about my lst posted question (above)

Thread: If-then-ElseIf for parsing file?!

Thread Tools

Display

ummmm

Did you get my email?

Draez68...It's not really that interesting

Alright...You asked for it

haha. What do you think I was trying to say?

You're not going to believe how easy it is with ADO

Posting Permissions