idea to get value from this txt file

sal21 · Post by **sal21** » 28 Nov 2021, 10:10

Attached is a part of file downloaded from site. (is big 3.56 Gb)

No idea to store the related value into a related var...

my idea is to read line by line...

related from the first line in file.

NR = the value 1 in "number":"1"
VIA = the value Via Giovanni Zirretta in "street":"Via Giovanni Zirretta"
...
CITTA ....
DISTRETTO ....
REGIONE ....
CAP (Is the postcode) ....
ID ....
LAT "coordinates":[13.583336,37.270182]}}
LNG "coordinates":[13.583336,37.270182]}}

Have an idea, tks.

Post by **HansV** » 28 Nov 2021, 11:09

The code will be very slow with such a large file...

Code: Select all

Sub ReadFile()
    Dim sFile As String
    Dim f As Integer
    Dim sLine As String
    Dim p1 As Long
    Dim p2 As Long
    Dim NR As String
    Dim VIA As String
    Dim CITTA As String
    Dim DISTRETTO As String
    Dim REGIONE As String
    Dim CAP As String
    Dim ID As String
    Dim COORD As String
    Dim PARTS() As String
    Dim LNG As String
    Dim LAT As String
    sFile = "C:\Users\javog\Documents\Excel\Test1.txt"
    f = FreeFile
    Open sFile For Input As #f
    Do While Not EOF(f)
        Line Input #f, sLine
        p2 = 0
        p1 = InStr(p2 + 1, sLine, """number""") + Len("""number""") + 2
        p2 = InStr(p1 + 1, sLine, """")
        NR = Mid(sLine, p1, p2 - p1)
        p1 = InStr(p2 + 1, sLine, """street""") + Len("""street""") + 2
        p2 = InStr(p1 + 1, sLine, """")
        VIA = Mid(sLine, p1, p2 - p1)
        p1 = InStr(p2 + 1, sLine, """city""") + Len("""city""") + 2
        p2 = InStr(p1 + 1, sLine, """")
        CITTA = Mid(sLine, p1, p2 - p1)
        p1 = InStr(p2 + 1, sLine, """district""") + Len("""district""") + 2
        p2 = InStr(p1 + 1, sLine, """")
        DISTRETTO = Mid(sLine, p1, p2 - p1)
        p1 = InStr(p2 + 1, sLine, """region""") + Len("""region""") + 2
        p2 = InStr(p1 + 1, sLine, """")
        REGIONE = Mid(sLine, p1, p2 - p1)
        p1 = InStr(p2 + 1, sLine, """postcode""") + Len("""postcode""") + 2
        p2 = InStr(p1 + 1, sLine, """")
        CAP = Mid(sLine, p1, p2 - p1)
        p1 = InStr(p2 + 1, sLine, """id""") + Len("""id""") + 2
        p2 = InStr(p1 + 1, sLine, """")
        ID = Mid(sLine, p1, p2 - p1)
        p1 = InStr(p2 + 1, sLine, """coordinates""") + Len("""coordinates""") + 2
        p2 = InStr(p1 + 1, sLine, "]")
        COORD = Mid(sLine, p1, p2 - p1)
        PARTS = Split(COORD, ",")
        LNG = PARTS(0)
        LAT = PARTS(1)
        ' Do something with the variables
        Debug.Print NR, VIA, CITTA, DISTRETTO, REGIONE, CAP, ID, LNG, LAT
    Loop
    Close #f
End Sub

sal21 · Post by **sal21** » 28 Nov 2021, 12:55

HansV wrote: ↑

28 Nov 2021, 11:09

The code will be very slow with such a large file...

Code: Select all

Sub ReadFile()
    Dim sFile As String
    Dim f As Integer
    Dim sLine As String
    Dim p1 As Long
    Dim p2 As Long
    Dim NR As String
    Dim VIA As String
    Dim CITTA As String
    Dim DISTRETTO As String
    Dim REGIONE As String
    Dim CAP As String
    Dim ID As String
    Dim COORD As String
    Dim PARTS() As String
    Dim LNG As String
    Dim LAT As String
    sFile = "C:\Users\javog\Documents\Excel\Test1.txt"
    f = FreeFile
    Open sFile For Input As #f
    Do While Not EOF(f)
        Line Input #f, sLine
        p2 = 0
        p1 = InStr(p2 + 1, sLine, """number""") + Len("""number""") + 2
        p2 = InStr(p1 + 1, sLine, """")
        NR = Mid(sLine, p1, p2 - p1)
        p1 = InStr(p2 + 1, sLine, """street""") + Len("""street""") + 2
        p2 = InStr(p1 + 1, sLine, """")
        VIA = Mid(sLine, p1, p2 - p1)
        p1 = InStr(p2 + 1, sLine, """city""") + Len("""city""") + 2
        p2 = InStr(p1 + 1, sLine, """")
        CITTA = Mid(sLine, p1, p2 - p1)
        p1 = InStr(p2 + 1, sLine, """district""") + Len("""district""") + 2
        p2 = InStr(p1 + 1, sLine, """")
        DISTRETTO = Mid(sLine, p1, p2 - p1)
        p1 = InStr(p2 + 1, sLine, """region""") + Len("""region""") + 2
        p2 = InStr(p1 + 1, sLine, """")
        REGIONE = Mid(sLine, p1, p2 - p1)
        p1 = InStr(p2 + 1, sLine, """postcode""") + Len("""postcode""") + 2
        p2 = InStr(p1 + 1, sLine, """")
        CAP = Mid(sLine, p1, p2 - p1)
        p1 = InStr(p2 + 1, sLine, """id""") + Len("""id""") + 2
        p2 = InStr(p1 + 1, sLine, """")
        ID = Mid(sLine, p1, p2 - p1)
        p1 = InStr(p2 + 1, sLine, """coordinates""") + Len("""coordinates""") + 2
        p2 = InStr(p1 + 1, sLine, "]")
        COORD = Mid(sLine, p1, p2 - p1)
        PARTS = Split(COORD, ",")
        LNG = PARTS(0)
        LAT = PARTS(1)
        ' Do something with the variables
        Debug.Print NR, VIA, CITTA, DISTRETTO, REGIONE, CAP, ID, LNG, LAT
    Loop
    Close #f
End Sub

As usual no have words!
Perfect!

but possible to split the original big file for example with a block of 1.000.000 of lines, in C\TABULATI\SPLITFILE\ from C:\TABUALTI\BIGFILE.TXT

I thinks is a good idea....

Post by **HansV** » 28 Nov 2021, 13:01

Will you have to do this frequently? If it just for once, you can split the text file manually.

SpeakEasy · Post by **SpeakEasy** » 29 Nov 2021, 15:07

Given it is JSON, you could just leverage JSON to decode it ...

sal21 · Post by **sal21** » 29 Nov 2021, 15:11

SpeakEasy wrote: ↑
29 Nov 2021, 15:07
Given it is JSON, you could just leverage JSON to decode it ...

OK!
Example in VB6?
Tks.

SpeakEasy · Post by **SpeakEasy** » 29 Nov 2021, 15:28

Code: Select all

Public Sub Example()
    With CreateObject("scripting.filesystemobject").OpenTextFile("d:\downloads\test1.txt", ForReading)
        Do Until .AtEndOfStream
            jtext = .ReadLine
            ' deal with certain keyword capitalisation quirks
            jtext = Replace(jtext, "properties", "Properties")
            jtext = Replace(jtext, "number", "Number")
            jtext = Replace(jtext, "id", "ID")
            JSONDecode jtext
        Loop
    End With
End Sub

Sub JSONDecode(JSONString)
    With CreateObject("ScriptControl")
        .Language = "JScript"
        With .Eval("(" + JSONString + ")")
            NR = .Properties.Number
            VIA = .Properties.street
            CITA = .Properties.city
            DISTRETTO = .Properties.district
            REGIONE = .Properties.region
            CAP = .Properties.postcode
            ID = .Properties.ID
            LATLONG = .geometry.coordinates
        End With
    End With
    Debug.Print NR, VIA, CITTA, DISTRETTO, REGIONE, CAP, ID, LATLONG
End Sub

sal21 · Post by **sal21** » 29 Nov 2021, 16:32

SpeakEasy wrote: ↑

29 Nov 2021, 15:28

Code: Select all

Public Sub Example()
    With CreateObject("scripting.filesystemobject").OpenTextFile("d:\downloads\test1.txt", ForReading)
        Do Until .AtEndOfStream
            jtext = .ReadLine
            ' deal with certain keyword capitalisation quirks
            jtext = Replace(jtext, "properties", "Properties")
            jtext = Replace(jtext, "number", "Number")
            jtext = Replace(jtext, "id", "ID")
            JSONDecode jtext
        Loop
    End With
End Sub

Sub JSONDecode(JSONString)
    With CreateObject("ScriptControl")
        .Language = "JScript"
        With .Eval("(" + JSONString + ")")
            NR = .Properties.Number
            VIA = .Properties.street
            CITA = .Properties.city
            DISTRETTO = .Properties.district
            REGIONE = .Properties.region
            CAP = .Properties.postcode
            ID = .Properties.ID
            LATLONG = .geometry.coordinates
        End With
    End With
    Debug.Print NR, VIA, CITTA, DISTRETTO, REGIONE, CAP, ID, LATLONG
End Sub

error here

With .Eval("(" + JSONString + ")")

SpeakEasy · Post by **SpeakEasy** » 29 Nov 2021, 17:06

That error is being raised by the Jscript engine that is evaluating the JSON string - and what it is telling you is that the JSON string is incomplete (missing an expected bracket), which in turn suggests an error in the source text file

It'd be worth forensically examining JSONString at this point.

sal21 · Post by **sal21** » 29 Nov 2021, 17:26

SpeakEasy wrote: ↑
29 Nov 2021, 17:06
That error is being raised by the Jscript engine that is evaluating the JSON string - and what it is telling you is that the JSON string is incomplete (missing an expected bracket), which in turn suggests an error in the source text file

It'd be worth forensically examining JSONString at this point.

Resolved, the First line have a First free chat trunked!
I go ti the next second line and all work Great!
Tks for code.

SpeakEasy · Post by **SpeakEasy** » 30 Nov 2021, 10:17

Please note that I slightly streamlined the code since you copy-n-pasted it (very minor changes, that don't affect the functionality)

sal21 · Post by **sal21** » 01 Mar 2023, 11:29

HansV wrote: ↑

28 Nov 2021, 11:09

The code will be very slow with such a large file...

Code: Select all

Sub ReadFile()
    Dim sFile As String
    Dim f As Integer
    Dim sLine As String
    Dim p1 As Long
    Dim p2 As Long
    Dim NR As String
    Dim VIA As String
    Dim CITTA As String
    Dim DISTRETTO As String
    Dim REGIONE As String
    Dim CAP As String
    Dim ID As String
    Dim COORD As String
    Dim PARTS() As String
    Dim LNG As String
    Dim LAT As String
    sFile = "C:\Users\javog\Documents\Excel\Test1.txt"
    f = FreeFile
    Open sFile For Input As #f
    Do While Not EOF(f)
        Line Input #f, sLine
        p2 = 0
        p1 = InStr(p2 + 1, sLine, """number""") + Len("""number""") + 2
        p2 = InStr(p1 + 1, sLine, """")
        NR = Mid(sLine, p1, p2 - p1)
        p1 = InStr(p2 + 1, sLine, """street""") + Len("""street""") + 2
        p2 = InStr(p1 + 1, sLine, """")
        VIA = Mid(sLine, p1, p2 - p1)
        p1 = InStr(p2 + 1, sLine, """city""") + Len("""city""") + 2
        p2 = InStr(p1 + 1, sLine, """")
        CITTA = Mid(sLine, p1, p2 - p1)
        p1 = InStr(p2 + 1, sLine, """district""") + Len("""district""") + 2
        p2 = InStr(p1 + 1, sLine, """")
        DISTRETTO = Mid(sLine, p1, p2 - p1)
        p1 = InStr(p2 + 1, sLine, """region""") + Len("""region""") + 2
        p2 = InStr(p1 + 1, sLine, """")
        REGIONE = Mid(sLine, p1, p2 - p1)
        p1 = InStr(p2 + 1, sLine, """postcode""") + Len("""postcode""") + 2
        p2 = InStr(p1 + 1, sLine, """")
        CAP = Mid(sLine, p1, p2 - p1)
        p1 = InStr(p2 + 1, sLine, """id""") + Len("""id""") + 2
        p2 = InStr(p1 + 1, sLine, """")
        ID = Mid(sLine, p1, p2 - p1)
        p1 = InStr(p2 + 1, sLine, """coordinates""") + Len("""coordinates""") + 2
        p2 = InStr(p1 + 1, sLine, "]")
        COORD = Mid(sLine, p1, p2 - p1)
        PARTS = Split(COORD, ",")
        LNG = PARTS(0)
        LAT = PARTS(1)
        ' Do something with the variables
        Debug.Print NR, VIA, CITTA, DISTRETTO, REGIONE, CAP, ID, LNG, LAT
    Loop
    Close #f
End Sub

Hi bro.
I can have a line in the txt file similar:

{"type":"Feature","properties":{"hash":"0cc9b964362da9a8","number":"","street":"","unit":"","city":"","district":"","region":"","postcode":"","id":""},"geometry":null}

This line contain a value not important in my project.

How to skip this line?

note:
see line 7 in attached file

Post by **HansV** » 01 Mar 2023, 11:41

Code: Select all

Sub ReadFile()
    Dim sFile As String
    Dim f As Integer
    Dim sLine As String
    Dim p1 As Long
    Dim p2 As Long
    Dim NR As String
    Dim VIA As String
    Dim CITTA As String
    Dim DISTRETTO As String
    Dim REGIONE As String
    Dim CAP As String
    Dim ID As String
    Dim COORD As String
    Dim PARTS() As String
    Dim LNG As String
    Dim LAT As String
    sFile = "C:\Users\javog\Documents\Excel\Test.txt"
    f = FreeFile
    Open sFile For Input As #f
    Do While Not EOF(f)
        Line Input #f, sLine
        p2 = 0
        p1 = InStr(p2 + 1, sLine, """number""") + Len("""number""") + 2
        p2 = InStr(p1 + 1, sLine, """")
        NR = Mid(sLine, p1, p2 - p1)
        If NR <> "" Then
            p1 = InStr(p2 + 1, sLine, """street""") + Len("""street""") + 2
            p2 = InStr(p1 + 1, sLine, """")
            VIA = Mid(sLine, p1, p2 - p1)
            p1 = InStr(p2 + 1, sLine, """city""") + Len("""city""") + 2
            p2 = InStr(p1 + 1, sLine, """")
            CITTA = Mid(sLine, p1, p2 - p1)
            p1 = InStr(p2 + 1, sLine, """district""") + Len("""district""") + 2
            p2 = InStr(p1 + 1, sLine, """")
            DISTRETTO = Mid(sLine, p1, p2 - p1)
            p1 = InStr(p2 + 1, sLine, """region""") + Len("""region""") + 2
            p2 = InStr(p1 + 1, sLine, """")
            REGIONE = Mid(sLine, p1, p2 - p1)
            p1 = InStr(p2 + 1, sLine, """postcode""") + Len("""postcode""") + 2
            p2 = InStr(p1 + 1, sLine, """")
            CAP = Mid(sLine, p1, p2 - p1)
            p1 = InStr(p2 + 1, sLine, """id""") + Len("""id""") + 2
            p2 = InStr(p1 + 1, sLine, """")
            ID = Mid(sLine, p1, p2 - p1)
            p1 = InStr(p2 + 1, sLine, """coordinates""") + Len("""coordinates""") + 2
            p2 = InStr(p1 + 1, sLine, "]")
            COORD = Mid(sLine, p1, p2 - p1)
            PARTS = Split(COORD, ",")
            LNG = PARTS(0)
            LAT = PARTS(1)
            ' Do something with the variables
            Debug.Print NR, VIA, CITTA, DISTRETTO, REGIONE, CAP, ID, LNG, LAT
        End If
    Loop
    Close #f
End Sub

SpeakEasy · Post by **SpeakEasy** » 01 Mar 2023, 12:04

If you were still using the JSON vewrsion, then the following minor alteration to JSONDecode should handle it:

Code: Select all

Sub JSONDecode(JSONString)
    With CreateObject("ScriptControl")
        .Language = "JScript"
        With .Eval("(" + JSONString + ")")
            NR = .Properties.Number
            VIA = .Properties.street
            CITA = .Properties.city
            DISTRETTO = .Properties.district
            REGIONE = .Properties.region
            CAP = .Properties.postcode
            ID = .Properties.ID
            On Error Resume Next
            	LATLONG = .geometry.coordinates
            On Error GoTo 0
        End With
    End With
    If LATLONG <> "" Then Debug.Print NR, VIA, CITTA, DISTRETTO, REGIONE, CAP, ID, LATLONG
End Sub

sal21 · Post by **sal21** » 01 Mar 2023, 17:36

HansV wrote: ↑

01 Mar 2023, 11:41

Code: Select all

Sub ReadFile()
    Dim sFile As String
    Dim f As Integer
    Dim sLine As String
    Dim p1 As Long
    Dim p2 As Long
    Dim NR As String
    Dim VIA As String
    Dim CITTA As String
    Dim DISTRETTO As String
    Dim REGIONE As String
    Dim CAP As String
    Dim ID As String
    Dim COORD As String
    Dim PARTS() As String
    Dim LNG As String
    Dim LAT As String
    sFile = "C:\Users\javog\Documents\Excel\Test.txt"
    f = FreeFile
    Open sFile For Input As #f
    Do While Not EOF(f)
        Line Input #f, sLine
        p2 = 0
        p1 = InStr(p2 + 1, sLine, """number""") + Len("""number""") + 2
        p2 = InStr(p1 + 1, sLine, """")
        NR = Mid(sLine, p1, p2 - p1)
        If NR <> "" Then
            p1 = InStr(p2 + 1, sLine, """street""") + Len("""street""") + 2
            p2 = InStr(p1 + 1, sLine, """")
            VIA = Mid(sLine, p1, p2 - p1)
            p1 = InStr(p2 + 1, sLine, """city""") + Len("""city""") + 2
            p2 = InStr(p1 + 1, sLine, """")
            CITTA = Mid(sLine, p1, p2 - p1)
            p1 = InStr(p2 + 1, sLine, """district""") + Len("""district""") + 2
            p2 = InStr(p1 + 1, sLine, """")
            DISTRETTO = Mid(sLine, p1, p2 - p1)
            p1 = InStr(p2 + 1, sLine, """region""") + Len("""region""") + 2
            p2 = InStr(p1 + 1, sLine, """")
            REGIONE = Mid(sLine, p1, p2 - p1)
            p1 = InStr(p2 + 1, sLine, """postcode""") + Len("""postcode""") + 2
            p2 = InStr(p1 + 1, sLine, """")
            CAP = Mid(sLine, p1, p2 - p1)
            p1 = InStr(p2 + 1, sLine, """id""") + Len("""id""") + 2
            p2 = InStr(p1 + 1, sLine, """")
            ID = Mid(sLine, p1, p2 - p1)
            p1 = InStr(p2 + 1, sLine, """coordinates""") + Len("""coordinates""") + 2
            p2 = InStr(p1 + 1, sLine, "]")
            COORD = Mid(sLine, p1, p2 - p1)
            PARTS = Split(COORD, ",")
            LNG = PARTS(0)
            LAT = PARTS(1)
            ' Do something with the variables
            Debug.Print NR, VIA, CITTA, DISTRETTO, REGIONE, CAP, ID, LNG, LAT
        End If
    Loop
    Close #f
End Sub

OPS...

Why in line 5 the code dont check the null value of number?!

look what i get in debug.print of NR:

",

???

Post by **HansV** » 01 Mar 2023, 19:43

Try this:

Code: Select all

Sub ReadFile()
    Dim sFile As String
    Dim f As Integer
    Dim sLine As String
    Dim p1 As Long
    Dim p2 As Long
    Dim NR As String
    Dim VIA As String
    Dim CITTA As String
    Dim DISTRETTO As String
    Dim REGIONE As String
    Dim CAP As String
    Dim ID As String
    Dim COORD As String
    Dim PARTS() As String
    Dim LNG As String
    Dim LAT As String
    sFile = "C:\Users\javog\Documents\Excel\Test.txt"
    f = FreeFile
    Open sFile For Input As #f
    Do While Not EOF(f)
        Line Input #f, sLine
        p2 = 0
        p1 = InStr(p2 + 1, sLine, """number""") + Len("""number""") + 2
        p2 = InStr(p1, sLine, """")
        NR = Mid(sLine, p1, p2 - p1)
        If NR <> "" Then
            p1 = InStr(p2 + 1, sLine, """street""") + Len("""street""") + 2
            p2 = InStr(p1, sLine, """")
            VIA = Mid(sLine, p1, p2 - p1)
            p1 = InStr(p2 + 1, sLine, """city""") + Len("""city""") + 2
            p2 = InStr(p1, sLine, """")
            CITTA = Mid(sLine, p1, p2 - p1)
            p1 = InStr(p2 + 1, sLine, """district""") + Len("""district""") + 2
            p2 = InStr(p1, sLine, """")
            DISTRETTO = Mid(sLine, p1, p2 - p1)
            p1 = InStr(p2 + 1, sLine, """region""") + Len("""region""") + 2
            p2 = InStr(p1, sLine, """")
            REGIONE = Mid(sLine, p1, p2 - p1)
            p1 = InStr(p2 + 1, sLine, """postcode""") + Len("""postcode""") + 2
            p2 = InStr(p1, sLine, """")
            CAP = Mid(sLine, p1, p2 - p1)
            p1 = InStr(p2 + 1, sLine, """id""") + Len("""id""") + 2
            p2 = InStr(p1, sLine, """")
            ID = Mid(sLine, p1, p2 - p1)
            p1 = InStr(p2 + 1, sLine, """coordinates""") + Len("""coordinates""") + 2
            p2 = InStr(p1, sLine, "]")
            COORD = Mid(sLine, p1, p2 - p1)
            PARTS = Split(COORD, ",")
            LNG = PARTS(0)
            LAT = PARTS(1)
            ' Do something with the variables
            Debug.Print NR, VIA, CITTA, DISTRETTO, REGIONE, CAP, ID, LNG, LAT
        End If
    Loop
    Close #f
End Sub

Eileen's Lounge

idea to get value from this txt file

idea to get value from this txt file

Re: idea to get value from this txt file

Re: idea to get value from this txt file

Re: idea to get value from this txt file

Re: idea to get value from this txt file

Re: idea to get value from this txt file

Re: idea to get value from this txt file

Re: idea to get value from this txt file

Re: idea to get value from this txt file

Re: idea to get value from this txt file

Re: idea to get value from this txt file

Re: idea to get value from this txt file

Re: idea to get value from this txt file

Re: idea to get value from this txt file

Re: idea to get value from this txt file

Re: idea to get value from this txt file