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

User avatar
sal21
PlatinumLounger
Posts: 4402
Joined: 26 Apr 2010, 17:36

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

Post by 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
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

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

Post by 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
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4402
Joined: 26 Apr 2010, 17:36

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

Post by sal21 »

HansV wrote:
19 Apr 2024, 12:32
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...

User avatar
HansV
Administrator
Posts: 78783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

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

Post by 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
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4402
Joined: 26 Apr 2010, 17:36

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

Post by sal21 »

HansV wrote:
19 Apr 2024, 14:49
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

User avatar
HansV
Administrator
Posts: 78783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

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

Post by HansV »

Does it work?
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4402
Joined: 26 Apr 2010, 17:36

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

Post by sal21 »

HansV wrote:
19 Apr 2024, 18:23
Does it work?
NO!

User avatar
HansV
Administrator
Posts: 78783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

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

Post by HansV »

What is the problem with it?
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4402
Joined: 26 Apr 2010, 17:36

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

Post by sal21 »

HansV wrote:
19 Apr 2024, 18:57
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

User avatar
HansV
Administrator
Posts: 78783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

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

Post by HansV »

And does this work?
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4402
Joined: 26 Apr 2010, 17:36

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

Post by sal21 »

HansV wrote:
19 Apr 2024, 19:45
And does this work?
Naturally iuf the first line is ISTAT=001001 and ISTAT_Old=""

The condition:

If ISTAT <> ISTAT_Old Then

is true!

User avatar
HansV
Administrator
Posts: 78783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

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

Post by HansV »

That's why added the check ... And I > 0
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4402
Joined: 26 Apr 2010, 17:36

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

Post by sal21 »

HansV wrote:
19 Apr 2024, 19:54
That's why added the check ... And I > 0
Have you tested on my text file?
Sorry

User avatar
HansV
Administrator
Posts: 78783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

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

Post by 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
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4402
Joined: 26 Apr 2010, 17:36

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

Post by 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

User avatar
HansV
Administrator
Posts: 78783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

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

Post by 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
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4402
Joined: 26 Apr 2010, 17:36

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

Post by sal21 »

HansV wrote:
19 Apr 2024, 21:57
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
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

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

Post by 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
Best wishes,
Hans

User avatar
p45cal
2StarLounger
Posts: 154
Joined: 11 Jun 2012, 20:37

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

Post by 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.

User avatar
HansV
Administrator
Posts: 78783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

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

Post by HansV »

Yes, that's better - thanks!
Best wishes,
Hans