idea to get value from this txt file
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
idea to get value from this txt file
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.
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.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: idea to get value from this txt file
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
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: idea to get value from this txt file
As usual no have words!HansV wrote: ↑28 Nov 2021, 11:09The 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
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....
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: idea to get value from this txt file
Will you have to do this frequently? If it just for once, you can split the text file manually.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 564
- Joined: 27 Jun 2021, 10:46
Re: idea to get value from this txt file
Given it is JSON, you could just leverage JSON to decode it ...
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
-
- 4StarLounger
- Posts: 564
- Joined: 27 Jun 2021, 10:46
Re: idea to get value from this txt file
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
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: idea to get value from this txt file
error hereSpeakEasy wrote: ↑29 Nov 2021, 15:28Code: 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
With .Eval("(" + JSONString + ")")
You do not have the required permissions to view the files attached to this post.
-
- 4StarLounger
- Posts: 564
- Joined: 27 Jun 2021, 10:46
Re: idea to get value from this txt file
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.
It'd be worth forensically examining JSONString at this point.
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: idea to get value from this txt file
Resolved, the First line have a First free chat trunked!SpeakEasy wrote: ↑29 Nov 2021, 17:06That 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.
I go ti the next second line and all work Great!
Tks for code.
-
- 4StarLounger
- Posts: 564
- Joined: 27 Jun 2021, 10:46
Re: idea to get value from this txt file
Please note that I slightly streamlined the code since you copy-n-pasted it (very minor changes, that don't affect the functionality)
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: idea to get value from this txt file
Hi bro.HansV wrote: ↑28 Nov 2021, 11:09The 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
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
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: idea to get value from this txt 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\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
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 564
- Joined: 27 Jun 2021, 10:46
Re: idea to get value from this txt file
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
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: idea to get value from this txt file
OPS...HansV wrote: ↑01 Mar 2023, 11:41Code: 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
Why in line 5 the code dont check the null value of number?!
look what i get in debug.print of NR:
",
???
You do not have the required permissions to view the files attached to this post.
Last edited by sal21 on 01 Mar 2023, 17:38, edited 1 time in total.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: idea to get value from this txt file
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
Best wishes,
Hans
Hans