Results 1 to 17 of 17

Thread: read JSON FILE

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Mar 2005
    Posts
    2,580

    read JSON 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.
    Attached Files Attached Files

  2. #2
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: read JSON FILE

    So you have 3.5GB of data in JSON format.
    What do you want to do with the data?

    Do you want to store it in a database?

  3. #3

    Thread Starter
    PowerPoster
    Join Date
    Mar 2005
    Posts
    2,580

    Re: read JSON FILE

    Quote Originally Posted by Arnoutdv View Post
    So you have 3.5GB of data in JSON format.
    What do you want to do with the data?

    Do you want to store it in a database?
    YES BRO!
    Store in Access dartabase

    note:
    the lines in file are approx 5.000.xxx
    Last edited by luca90; Dec 2nd, 2021 at 04:15 AM.

  4. #4
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: read JSON FILE

    It will not be straight forward to use VB6 for importing this file.
    The standard VB6 File IO routines are limited to a file size of 2GB.

    Have a look at this thread how to deal with files > 2GB
    https://www.vbforums.com/showthread....File-I-O-Class

  5. #5
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: read JSON FILE

    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
    cheers,
    </wqw>

  6. #6
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: read JSON FILE

    Quote Originally Posted by wqweto View Post
    You can use FSO to read big files too.
    Not UTF8-encoded ones (which this one is, when it contains JSON).

    Olaf

  7. #7
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: read JSON FILE

    Quote Originally Posted by Schmidt View Post
    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).

    cheers,
    </wqw>

  8. #8
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: read JSON FILE

    @Olaf, does RC5/6 contain a fileIO library for dealing with >2GB (text) files?

  9. #9
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: read JSON FILE

    Quote Originally Posted by wqweto View Post
    ...it seems most of the file is in ASCII
    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.

  10. #10
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: read JSON FILE

    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

  11. #11

    Thread Starter
    PowerPoster
    Join Date
    Mar 2005
    Posts
    2,580

    Re: read JSON FILE

    Quote Originally Posted by Schmidt View Post
    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

    path file i have understand, naturally
    Last edited by luca90; Dec 3rd, 2021 at 04:53 PM.

  12. #12
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,852

    Re: read JSON FILE

    I want a dartabase too ... *pouts*


    Name:  dartabase.png
Views: 279
Size:  6.2 KBName:  Exclaim.png
Views: 273
Size:  1.0 KB
    Last edited by Elroy; Dec 3rd, 2021 at 05:43 PM.
    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.

  13. #13
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

  14. #14

    Thread Starter
    PowerPoster
    Join Date
    Mar 2005
    Posts
    2,580

    Re: read JSON FILE

    Quote Originally Posted by Schmidt View Post
    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!

  15. #15
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: read JSON FILE

    An extension is just an extension.
    If you would take the time to actually read the answers you would notice that the database used is SQLite.

  16. #16

    Thread Starter
    PowerPoster
    Join Date
    Mar 2005
    Posts
    2,580

    Re: read JSON FILE

    Quote Originally Posted by Arnoutdv View Post
    An extension is just an extension.
    If you would take the time to actually read the answers you would notice that the database used is SQLite.
    Where you notice me sqlLite?
    Peraphs i'm distract...

  17. #17
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: read JSON FILE

    Quote Originally Posted by luca90 View Post
    ok, downloaded rc6 and the code work.
    When you say "it works", did you already import your large 3.5GB-file with it?

    If yes, out of interest:
    - how long did it take roughly?
    - and what size is the resulting SQLite-DB (the *.db3-file)?

    As for "opening the DB", you can do that via the RC6-reference in VB6-code -
    or with one of the SQLite-Manager-Apps out there.

    Olaf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width