check until value in txt change - i'm on vb6

[sal21]

Based thie values in txt file change....

first line
ISTAT = Split(strLine, ";")(1) -001001

for example chnage in line 22 -001002 ... run mycode

naturally with a loop on the txt file line by line

[HansV]

Keep track of the previous value of ISTAT:

Code: Select all

Sub Test()
    Dim ff As Integer
    Dim arrLines() As String
    Dim i As Long
    Dim strLine As String
    Dim ISTAT As String
    Dim ISTAT_Old As String
    ff = FreeFile
    Open "TEST.TXT" For Input As #ff
    arrLines = Split(Input(Log(ff), #ff), vbCrLf)
    Close #ff
    For i = 0 To UBound(arrLines)
        strLine = arrLines(i)
        ISTAT = Split(strLine, ";")(1)
        If ISTAT <> ISTAT_Old Then
            Call MyCode
            ISTAT_Old = ISTAT
        End If
    Next i
End Sub

[sal21]

Keep track of the previous value of ISTAT:

Code: Select all

Sub Test()
    Dim ff As Integer
    Dim arrLines() As String
    Dim i As Long
    Dim strLine As String
    Dim ISTAT As String
    Dim ISTAT_Old As String
    ff = FreeFile
    Open "TEST.TXT" For Input As #ff
    arrLines = Split(Input([b]Log[/b](ff), #ff), vbCrLf)
    Close #ff
    For i = 0 To UBound(arrLines)
        strLine = arrLines(i)
        ISTAT = Split(strLine, ";")(1)
        If ISTAT <> ISTAT_Old Then
            Call MyCode
            ISTAT_Old = ISTAT
        End If
    Next i
End Sub

lof...

I think is air code

But wth about first line just go to mycode, becouse ISTAT_Old is =""...

Hummmm...

[HansV]

Yes it was air code, and it should indeed have been LOF. You can add another check.

Code: Select all

Sub Test()
    Dim ff As Integer
    Dim arrLines() As String
    Dim i As Long
    Dim strLine As String
    Dim ISTAT As String
    Dim ISTAT_Old As String
    ff = FreeFile
    Open "TEST.TXT" For Input As #ff
    arrLines = Split(Input(LOF(ff), #ff), vbCrLf)
    Close #ff
    For i = 0 To UBound(arrLines)
        strLine = arrLines(i)
        ISTAT = Split(strLine, ";")(1)
        If ISTAT <> ISTAT_Old And i > 0 Then
            Call MyCode
            ISTAT_Old = ISTAT
        End If
    Next i
End Sub

[sal21]

Yes it was air code, and it should indeed have been LOF. You can add another check.

Code: Select all

Sub Test()
    Dim ff As Integer
    Dim arrLines() As String
    Dim i As Long
    Dim strLine As String
    Dim ISTAT As String
    Dim ISTAT_Old As String
    ff = FreeFile
    Open "TEST.TXT" For Input As #ff
    arrLines = Split(Input(LOF(ff), #ff), vbCrLf)
    Close #ff
    For i = 0 To UBound(arrLines)
        strLine = arrLines(i)
        ISTAT = Split(strLine, ";")(1)
        If ISTAT <> ISTAT_Old And i > 0 Then
            Call MyCode
            ISTAT_Old = ISTAT
        End If
    Next i
End Sub

MY CODE:

Code: Select all

Sub Test()

    Dim ff As Integer
    Dim arrLines() As String
    Dim I As Long
    Dim strLine As String
    Dim ISTAT As String, M As Long, F As Long, TOT As Long
    Dim ISTAT_Old As String, InputArray() As String
        
    Erase InputArray
    ff = FreeFile
    Close #ff
    Open "C:\Lavori_Vb6\MAPPA_ITALIA\FILES\RCS\Dati_cittadinanza_2023.csv" For Input As #ff
    InputArray = Split(Input(LOF(ff), ff), vbLf)
    Close #ff
    
    M = 0
    F = 0
    TOT = 0
    
    For I = 0  To UBound(InputArray) 
    
        strLine = InputArray(I)
        
        ISTAT = Split(strLine, ";")(1)
        M = M + Split(strLine, ";")(7)
        F = F + Split(strLine, ";")(8)
        TOT = TOT + Split(strLine, ";")(9)
        
        If ISTAT <> ISTAT_Old And I > 1 Then
            'Call MyCode
            Stop
            ISTAT_Old = ISTAT
            M = 0
            F = 0
            TOT = 0
        End If
    Next I
    
End Sub

In effect i need to summ M(maschi) F(femmine) TOT Totale. For each cahangment of ISTAT

For ISTAT =001001 have

M = 1120
F = 1221
TOT = 2341

ecc...

in Call MyCode i have a sql to store this value in access table

[HansV]

Does it work?

[sal21]

Does it work?

NO!

[HansV]

What is the problem with it?

[sal21]

What is the problem with it?

The problem is ISTAT_Old=""

naturally...

My new code:

Code: Select all

Sub TEST_HANS()
    
    Dim ff As Integer
    Dim arrLines() As String
    Dim I As Long
    Dim strLine As String
    Dim ISTAT As String, M As Long, F As Long, TOT As Long, COMUNE As String
    Dim ISTAT_Old As String, InputArray() As String, ST As String, STATO As String
    
    Erase InputArray
    ff = FreeFile
    Close #ff
    Open "C:\Lavori_Vb6\MAPPA_ITALIA\FILES\RCS\Dati_cittadinanza_2002.csv" For Input As #ff
    InputArray = Split(Input(LOF(ff), ff), vbLf)
    Close #ff
    
    M = 0
    F = 0
    TOT = 0
    
    For I = 0 To UBound(InputArray) 
        
        strLine = InputArray(I)
        
        If Len(Split(strLine, ";")(1)) = 6 And Len(Split(strLine, ";")(3)) = 3 Then
            
            ISTAT = Split(strLine, ";")(1)
            ST = Split(strLine, ";")(3)
            STATO = UCase(Split(strLine, ";")(4))
            M = M + Split(strLine, ";")(7)
            F = F + Split(strLine, ";")(8)
            TOT = TOT + Split(strLine, ";")(9)
            
            If ISTAT <> ISTAT_Old Then
                
                ISTAT_Old = ISTAT
                
                Debug.Print "ISTAT: " & ISTAT & " MASCHI: " & M & " FEMMINE: " & F & " TOT: " & TOT
                
                M = 0
                F = 0
                TOT = 0
                
            End If
            
        End If
        
    Next I
    
End Sub

[HansV]

And does this work?

[sal21]

And does this work?

Naturally iuf the first line is ISTAT=001001 and ISTAT_Old=""

The condition:

If ISTAT <> ISTAT_Old Then

is true!

[HansV]

That's why added the check ... And I > 0

[sal21]

That's why added the check ... And I > 0

Have you tested on my text file?
Sorry

[HansV]

Yes, with the added condition. The output is

ISTAT: 001001 MASCHI: 1213 FEMMINE: 1354 TOT: 2567
ISTAT: 001002 MASCHI: 1837 FEMMINE: 1744 TOT: 3581
ISTAT: 001003 MASCHI: 306 FEMMINE: 308 TOT: 614
ISTAT: 001004 MASCHI: 789 FEMMINE: 832 TOT: 1621
ISTAT: 001006 MASCHI: 2916 FEMMINE: 3112 TOT: 6028

[sal21]

Ops but istat=001001 are 21s Lines.
The code summ only 2 Lines.
Sorry me
Peraphs i post a wrong question

In effect i need tu summ value by block of ISTAT

[HansV]

New version. I made it more efficient too.

Code: Select all

Sub TEST_HANS()
    Dim ff As Integer
    Dim arrLines() As String
    Dim I As Long
    Dim strLine As String
    Dim ISTAT As String, M As Long, F As Long, TOT As Long, COMUNE As String
    Dim ISTAT_Old As String, InputArray() As String, ST As String, STATO As String
    Dim arrParts() As String

    ff = FreeFile
    Close #ff
    Open "TEST.TXT" For Input As #ff
    InputArray = Split(Input(LOF(ff), ff), vbLf)
    Close #ff

    For I = 0 To UBound(InputArray)
        strLine = InputArray(I)
        arrParts = Split(strLine, ";")
        ISTAT = arrParts(1)
        ST = arrParts(3)
        If Len(ISTAT) = 6 And Len(ST) = 3 Then
            If ISTAT <> ISTAT_Old Then
                If I > 0 Then
                    Debug.Print "ISTAT: " & ISTAT & " MASCHI: " & M & " FEMMINE: " & F & " TOT: " & TOT
                    M = 0
                    F = 0
                    TOT = 0
                End If
                ISTAT_Old = ISTAT
            End If
            STATO = UCase(arrParts(4))
            M = M + arrParts(7)
            F = F + arrParts(8)
            TOT = TOT + arrParts(9)
        End If
    Next I
End Sub

[sal21]

New version. I made it more efficient too.

Code: Select all

Sub TEST_HANS()
    Dim ff As Integer
    Dim arrLines() As String
    Dim I As Long
    Dim strLine As String
    Dim ISTAT As String, M As Long, F As Long, TOT As Long, COMUNE As String
    Dim ISTAT_Old As String, InputArray() As String, ST As String, STATO As String
    Dim arrParts() As String

    ff = FreeFile
    Close #ff
    Open "TEST.TXT" For Input As #ff
    InputArray = Split(Input(LOF(ff), ff), vbLf)
    Close #ff

    For I = 0 To UBound(InputArray)
        strLine = InputArray(I)
        arrParts = Split(strLine, ";")
        ISTAT = arrParts(1)
        ST = arrParts(3)
        If Len(ISTAT) = 6 And Len(ST) = 3 Then
            If ISTAT <> ISTAT_Old Then
                If I > 0 Then
                    Debug.Print "ISTAT: " & ISTAT & " MASCHI: " & M & " FEMMINE: " & F & " TOT: " & TOT
                    M = 0
                    F = 0
                    TOT = 0
                End If
                ISTAT_Old = ISTAT
            End If
            STATO = UCase(arrParts(4))
            M = M + arrParts(7)
            F = F + arrParts(8)
            TOT = TOT + arrParts(9)
        End If
    Next I
End Sub

my code:

Code: Select all

Sub TEST_HANS()

    Dim ff As Integer
    Dim arrLines() As String
    Dim I As Long
    Dim strLine As String
    Dim ISTAT As String, M As Long, F As Long, TOT As Long, COMUNE As String
    Dim ISTAT_Old As String, InputArray() As String, ST As String, STATO As String
    Dim arrParts() As String

    ff = FreeFile
    Close #ff
    Open "C:\Lavori_Vb6\MAPPA_ITALIA\FILES\RCS\Dati_cittadinanza_2023.csv" For Input As #ff
    InputArray = Split(Input(LOF(ff), ff), vbLf)
    Close #ff

    For I = 0 To UBound(InputArray)
    
        strLine = InputArray(I)
        arrParts = Split(strLine, ";")
        ISTAT = arrParts(1)
        ST = arrParts(3)
        
        If Len(ISTAT) = 6 And Len(ST) = 3 Then
        
            If ISTAT <> ISTAT_Old Then
            
                If I > 0 Then
                    Debug.Print "ISTAT: " & ISTAT & " MASCHI: " & M & " FEMMINE: " & F & " TOT: " & TOT
                    M = 0
                    F = 0
                    TOT = 0
                End If
                
                ISTAT_Old = ISTAT
            End If
            
            STATO = UCase(arrParts(4))
            M = M + arrParts(7)
            F = F + arrParts(8)
            TOT = TOT + arrParts(9)
        End If
        
    Next I
    
End Sub

... First occurence in debug.print :

ISTAT: 001001 MASCHI: 0 FEMMINE: 0 TOT: 0

note:
attached a part of original txt file
the original file contain a first line not important for my project

[HansV]

Code: Select all

Sub TEST_HANS()

    Dim ff As Integer
    Dim arrLines() As String
    Dim I As Long
    Dim strLine As String
    Dim ISTAT As String, M As Long, F As Long, TOT As Long, COMUNE As String
    Dim ISTAT_Old As String, InputArray() As String, ST As String, STATO As String
    Dim arrParts() As String

    ff = FreeFile
    Close #ff
    Open "C:\Lavori_Vb6\MAPPA_ITALIA\FILES\RCS\Dati_cittadinanza_2023.csv" For Input As #ff
    InputArray = Split(Input(LOF(ff), ff), vbLf)
    Close #ff

    For I = 1 To UBound(InputArray)

        strLine = InputArray(I)
        arrParts = Split(strLine, ";")
        ISTAT = arrParts(1)
        ST = arrParts(3)

        If Len(ISTAT) = 6 And Len(ST) = 3 Then

            If ISTAT <> ISTAT_Old Then

                If I > 1 Then
                    Debug.Print "ISTAT: " & ISTAT & " MASCHI: " & M & " FEMMINE: " & F & " TOT: " & TOT
                    M = 0
                    F = 0
                    TOT = 0
                End If

                ISTAT_Old = ISTAT
            End If

            STATO = UCase(arrParts(4))
            M = M + arrParts(7)
            F = F + arrParts(8)
            TOT = TOT + arrParts(9)
        End If

    Next I

End Sub

[p45cal]

Hans,
I ran your latest code in Excel vba on the latest file from Sal21 (the one including headers) and got this:
ISTAT: 001002 MASCHI: 1229 FEMMINE: 1339 TOT: 2568
ISTAT: 001003 MASCHI: 1871 FEMMINE: 1798 TOT: 3669
ISTAT: 001004 MASCHI: 241 FEMMINE: 223 TOT: 464
ISTAT: 001006 MASCHI: 776 FEMMINE: 852 TOT: 1628
ISTAT: 001007 MASCHI: 3095 FEMMINE: 3197 TOT: 6292
ISTAT: 001008 MASCHI: 133 FEMMINE: 119 TOT: 252

There is no 001001 and the results seem out of kilter.
I ran the same csv file in Power Query and got:
Codice Istat M F All
001001 1229 1339 2568
001002 1871 1798 3669
001003 241 223 464
001004 776 852 1628
001006 3095 3197 6292
001007 133 119 252
001008 7620 7997 15617

I think, in your code, a change from:

Code: Select all

Debug.Print "ISTAT: " & ISTAT & " MASCHI: " & M & " FEMMINE: " & F & " TOT: " & TOT

to:

Code: Select all

Debug.Print "ISTAT: " & ISTAT_Old & " MASCHI: " & M & " FEMMINE: " & F & " TOT: " & TOT

and a final (same line) after

Code: Select all

Next I

of:

Code: Select all

Debug.Print "ISTAT: " & ISTAT_Old & " MASCHI: " & M & " FEMMINE: " & F & " TOT: " & TOT

results in:
ISTAT: 001001 MASCHI: 1229 FEMMINE: 1339 TOT: 2568
ISTAT: 001002 MASCHI: 1871 FEMMINE: 1798 TOT: 3669
ISTAT: 001003 MASCHI: 241 FEMMINE: 223 TOT: 464
ISTAT: 001004 MASCHI: 776 FEMMINE: 852 TOT: 1628
ISTAT: 001006 MASCHI: 3095 FEMMINE: 3197 TOT: 6292
ISTAT: 001007 MASCHI: 133 FEMMINE: 119 TOT: 252
ISTAT: 001008 MASCHI: 7620 FEMMINE: 7997 TOT: 15617

which matches the Power Query output
…if I've understood properly, and I hope I'm not treading on someone's toes.

[HansV]

Yes, that's better - thanks!