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]}}
You can use FSO to read big files too. Something like OpenTextFile, ReadLine and AtEndOfStream functions should be enough to read your big file one line at a time
Code:
Option Explicit
Private Sub Form_Load()
With CreateObject("Scripting.FileSystemObject").OpenTextFile("D:\TEMP\bbb.txt", 1)
Do While Not .AtEndOfStream
Debug.Print .ReadLine
Loop
End With
End Sub
Not UTF8-encoded ones (which this one is, when it contains JSON).
Olaf
True, but I immediately dismissed this limitation of FSO as a particular problem here as it seems most of the file is in ASCII with the interesting LAT/LON coordinates purely digits so the UTF-8 encoding will most probably be indistringuishable from plain ASCII file (if there is no BOM).
Well, quite a few italian City- (or Street-) Names will contain italian accents like: è, à , ì ...
And that's where the FSO.ReadLine will give you: "street":"è, à , ì" instead...
(just tested this here on my "german locale").
@ArnoutDV
Yes for large Files > 4GB there's e.g.:
- New_c.FSO.OpenFileStream ... if you want to do buffer-based line-parsing "by hand"
- or better, the cCSV-Class, which can be switched to "pure Line-Parsing" (with ColDelimiterChar and QuoteChar-Props = vbNullChar)
Code:
Private oCSV As cCSV
Set oCSV = New_c.CSV(vbNullChar, vbNullChar, CP_UTF8)
oCSV.ParseFile(...)
Also of consideration should be the "target-DB-Format"...
SQLite allows basically unlimited FileSize... whereas a JET4.0 *.mdb cannot be larger than 2GB IIRC.
Edit: Here's a complete RC6.cCSV-based example (for the given Test1.txt File of the OP)
Code:
Option Explicit
Implements ICSVCallback
Private oCSV As cCSV
Private Sub Form_Load()
Set oCSV = New_c.CSV(vbNullChar, vbNullChar, CP_UTF8)
oCSV.ParseFile "c:\temp\test1.txt", Me
End Sub
Private Function ICSVCallback_NewValue(ByVal RowNr As Long, ByVal ColNr As Long, B() As Byte, ByVal BValStartPos As Long, ByVal BValLen As Long) As Long
If BValLen = 0 Then Exit Function
Dim oJson As cCollection, oProp As cCollection, oGeom As cCollection
Set oJson = New_c.JSONDecodeToCollection(oCSV.GetStringValue(B, BValStartPos, BValLen))
Set oProp = oJson("properties")
Set oGeom = oJson("geometry")
Debug.Print RowNr; " "; oJson("type"),
Debug.Print oProp("city"), oProp("street");
Debug.Print oGeom("coordinates")(0), oGeom("coordinates")(1)
End Function
HTH
Olaf
Last edited by Schmidt; Dec 3rd, 2021 at 09:38 AM.
FWIW, here a Full-Demo, which should be able to import the ~5Mio Records from the "huge file" (3.5GB),
with about 5K Records per second (so it will need roughly 1000 seconds total = about 15-20 minutes).
Project will need a reference to RC6 - and an adjustment regarding the DBFile- and TextPaths in Form_Click:
Code:
Option Explicit
Implements ICSVCallback
Private Cnn As cConnection, CSV As cCSV
Private Sub Form_Click()
ImportTo "c:\temp\JSONImports.db3", "c:\temp\test1.txt"
End Sub
Private Sub ImportTo(DBFile As String, TextFile As String)
If New_c.FSO.FileExists(DBFile) Then New_c.FSO.DeleteFile DBFile 'delete the previous DB-File, if there is one
Set Cnn = New_c.Connection(DBFile, DBCreateNewFileDB)
With Cnn.NewFieldDefs
.Add "ID Integer Primary Key"
.Add "DISTRETTO Text Collate NoCase"
.Add "REGIONE Text Collate NoCase"
.Add "CITTA Text Collate NoCase"
.Add "VIA Text Collate NoCase"
.Add "NR Text Collate NoCase"
.Add "CAP Text Collate NoCase"
.Add "LAT Double"
.Add "LNG Double"
Cnn.CreateTable "GeoImport"
End With
Cnn.BeginTrans
Set CSV = New_c.CSV(vbNullChar, vbNullChar, CP_UTF8)
CSV.ParseFile TextFile, Me
Cnn.CommitTrans
Caption = "Records imported: " & Cnn.GetRs("Select Count(*) From GeoImport")(0)
End Sub
Private Function ICSVCallback_NewValue(ByVal RowNr As Long, ByVal ColNr As Long, B() As Byte, ByVal BValStartPos As Long, ByVal BValLen As Long) As Long
If BValLen = 0 Then Exit Function
Dim oJson As cCollection, oProp As cCollection, oGeom As cCollection
Set oJson = New_c.JSONDecodeToCollection(CSV.GetStringValue(B, BValStartPos, BValLen))
Set oProp = oJson("properties")
Set oGeom = oJson("geometry")
Cnn.ExecCmd "Insert Into GeoImport(ID, DISTRETTO, REGIONE, CITTA, VIA, NR, CAP, LAT, LNG) Values(?,?,?,?,?,?,?,?,?)", _
oProp("id"), oProp("district"), oProp("region"), oProp("city"), _
oProp("street"), oProp("number"), oProp("postcode"), _
oGeom("coordinates")(1), oGeom("coordinates")(0)
If RowNr Mod 1000 = 0 Then DoEvents: Me.Caption = RowNr 'simple Progress, any 1000 imported Records
End Function
FWIW, here a Full-Demo, which should be able to import the ~5Mio Records from the "huge file" (3.5GB),
with about 5K Records per second (so it will need roughly 1000 seconds total = about 15-20 minutes).
Project will need a reference to RC6 - and an adjustment regarding the DBFile- and TextPaths in Form_Click:
Code:
Option Explicit
Implements ICSVCallback
Private Cnn As cConnection, CSV As cCSV
Private Sub Form_Click()
ImportTo "c:\temp\JSONImports.db3", "c:\temp\test1.txt"
End Sub
Private Sub ImportTo(DBFile As String, TextFile As String)
If New_c.FSO.FileExists(DBFile) Then New_c.FSO.DeleteFile DBFile 'delete the previous DB-File, if there is one
Set Cnn = New_c.Connection(DBFile, DBCreateNewFileDB)
With Cnn.NewFieldDefs
.Add "ID Integer Primary Key"
.Add "DISTRETTO Text Collate NoCase"
.Add "REGIONE Text Collate NoCase"
.Add "CITTA Text Collate NoCase"
.Add "VIA Text Collate NoCase"
.Add "NR Text Collate NoCase"
.Add "CAP Text Collate NoCase"
.Add "LAT Double"
.Add "LNG Double"
Cnn.CreateTable "GeoImport"
End With
Cnn.BeginTrans
Set CSV = New_c.CSV(vbNullChar, vbNullChar, CP_UTF8)
CSV.ParseFile TextFile, Me
Cnn.CommitTrans
Caption = "Records imported: " & Cnn.GetRs("Select Count(*) From GeoImport")(0)
End Sub
Private Function ICSVCallback_NewValue(ByVal RowNr As Long, ByVal ColNr As Long, B() As Byte, ByVal BValStartPos As Long, ByVal BValLen As Long) As Long
If BValLen = 0 Then Exit Function
Dim oJson As cCollection, oProp As cCollection, oGeom As cCollection
Set oJson = New_c.JSONDecodeToCollection(CSV.GetStringValue(B, BValStartPos, BValLen))
Set oProp = oJson("properties")
Set oGeom = oJson("geometry")
Cnn.ExecCmd "Insert Into GeoImport(ID, DISTRETTO, REGIONE, CITTA, VIA, NR, CAP, LAT, LNG) Values(?,?,?,?,?,?,?,?,?)", _
oProp("id"), oProp("district"), oProp("region"), oProp("city"), _
oProp("street"), oProp("number"), oProp("postcode"), _
oGeom("coordinates")(1), oGeom("coordinates")(0)
If RowNr Mod 1000 = 0 Then DoEvents: Me.Caption = RowNr 'simple Progress, any 1000 imported Records
End Function
Olaf
sorry bro but nerver used RC6, is a Microsoft dll, or ?
and
and an adjustment regarding the DBFile ???
in other case have error in : Cnn As cConnection - user define type not defined
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
FWIW, here a Full-Demo, which should be able to import the ~5Mio Records from the "huge file" (3.5GB),
with about 5K Records per second (so it will need roughly 1000 seconds total = about 15-20 minutes).
Project will need a reference to RC6 - and an adjustment regarding the DBFile- and TextPaths in Form_Click:
Code:
Option Explicit
Implements ICSVCallback
Private Cnn As cConnection, CSV As cCSV
Private Sub Form_Click()
ImportTo "c:\temp\JSONImports.db3", "c:\temp\test1.txt"
End Sub
Private Sub ImportTo(DBFile As String, TextFile As String)
If New_c.FSO.FileExists(DBFile) Then New_c.FSO.DeleteFile DBFile 'delete the previous DB-File, if there is one
Set Cnn = New_c.Connection(DBFile, DBCreateNewFileDB)
With Cnn.NewFieldDefs
.Add "ID Integer Primary Key"
.Add "DISTRETTO Text Collate NoCase"
.Add "REGIONE Text Collate NoCase"
.Add "CITTA Text Collate NoCase"
.Add "VIA Text Collate NoCase"
.Add "NR Text Collate NoCase"
.Add "CAP Text Collate NoCase"
.Add "LAT Double"
.Add "LNG Double"
Cnn.CreateTable "GeoImport"
End With
Cnn.BeginTrans
Set CSV = New_c.CSV(vbNullChar, vbNullChar, CP_UTF8)
CSV.ParseFile TextFile, Me
Cnn.CommitTrans
Caption = "Records imported: " & Cnn.GetRs("Select Count(*) From GeoImport")(0)
End Sub
Private Function ICSVCallback_NewValue(ByVal RowNr As Long, ByVal ColNr As Long, B() As Byte, ByVal BValStartPos As Long, ByVal BValLen As Long) As Long
If BValLen = 0 Then Exit Function
Dim oJson As cCollection, oProp As cCollection, oGeom As cCollection
Set oJson = New_c.JSONDecodeToCollection(CSV.GetStringValue(B, BValStartPos, BValLen))
Set oProp = oJson("properties")
Set oGeom = oJson("geometry")
Cnn.ExecCmd "Insert Into GeoImport(ID, DISTRETTO, REGIONE, CITTA, VIA, NR, CAP, LAT, LNG) Values(?,?,?,?,?,?,?,?,?)", _
oProp("id"), oProp("district"), oProp("region"), oProp("city"), _
oProp("street"), oProp("number"), oProp("postcode"), _
oGeom("coordinates")(1), oGeom("coordinates")(0)
If RowNr Mod 1000 = 0 Then DoEvents: Me.Caption = RowNr 'simple Progress, any 1000 imported Records
End Function
Olaf
ok, downloaded rc6 and the code work.
But wath type of database is .db3?
I have opened but is unreadable!