B4J Question (SOLVED) How To Convert a Large JSON to CSV?

[Yafuhenk]

Hi,

I read several posts about converting a json- to a csv file. I also had a look at the JsonTree. But all those example processes the complete json file as one single string.
But what if the string is too large to fit into memory?

I would appreciate if I could get some hints how to convert the following VB.Net code to B4J.

Using Sr As New StreamReader(JsonFileName)
    Using reader As New JsonTextReader(Sr)
        reader.SupportMultipleContent = True
        Dim serializer = New JsonSerializer()
        While reader.Read()
            If reader.TokenType = JsonToken.StartObject Then
                Dim cc As JsonApiClient.SAPODataSalesCollection = serializer.Deserialize(Of JsonApiClient.SAPODataSalesCollection)(reader)
                For Each c In cc.D.Results
                    myimportstreamwriter.WriteLine(MyLine)
                Next
            End If
        End While
    End Using
End Using
myimportstreamwriter.Close()
myimportstreamwriter.Dispose()

Thanks!

 

[emexes]

If your JSON file is an array of objects, you could just read and process the JSON one object at a time.

Each object is enclosed in { } brackets, and you just read the file from the opening { of an object, keeping track of how deep you are inside the { } brackets by incrementing a counter every time you see an opening { and decrementing it every time you see a closing }.

When the counter returns to 0, then you've just hit the closing } of the object, and you can process the resulting {object} with your favourite JSON parser and append its data as csv to your output file.

Then read the next object from the input file and repeat the process.

 

[Yafuhenk]

Thanks for your comment.
I read the file char by char with the following code

Dim tr As TextReader
tr.Initialize(File.OpenInput(xui.DefaultFolder, jsonfile))
Dim buffer(4096) As Char
Dim c As Int = 0
Do While c <> -1
    c = tr.Read(buffer, 0, buffer.Length)
    For Each ch As char in buffer
        Select Case ch
        Case "{"
       
        Case "}"
       
        Case "["
       
        Case "]"
    Next
Loop

The json file I have contains both "{" ,"}" and "[", "]".
I will see if I can write to code to get the objects tomorrow.
Any comments, suggestions are appreciated.

 

[emexes]

I read the file char by char with the following code
I will see if I can write to code to get the objects tomorrow.
Any comments, suggestions are appreciated.

Stage one is to neaten-up the file-reading. I think the main issues were:

1/ the for-each loop probably processed all 4096 Chars in buffer, regardless of how many Chars had actually been read (ie c)

2/ the buffer was processed, and *then* the tr.Read result was checked to see if we've reached the end of the file

Dim JsonFileName As String = "large-file.json"      '26 MB from https://github.com/json-iterator/test-data/blob/master/large-File.json

Dim tr As TextReader
tr.Initialize(File.OpenInput(File.DirAssets, JsonFileName))

Dim buffer(4096) As Char

Do While True    'grr...
    Dim NumCharsRead As Int = tr.Read(buffer, 0, buffer.Length)
    If NumCharsRead < 0 Then
        Exit 'do
    End If

    For I = 0 To NumCharsRead - 1
        Dim ch As Char = buffer(I)
        
        ...
    Next
Loop

tr.Close

 

[emexes]

I read the file char by char with the following code

So far, so good. This code:

Dim JsonFileName As String = "large-file.json"      '26 MB from https://github.com/json-iterator/test-data/blob/master/large-File.json

Dim tr As TextReader
tr.Initialize(File.OpenInput(File.DirAssets, JsonFileName))

Dim buffer(4096) As Char

Dim StartTime As Long = DateTime.Now

Dim TotalNumCharsRead As Int = 0
Do While True    'grr...
    Dim NumCharsRead As Int = tr.Read(buffer, 0, buffer.Length)
    If NumCharsRead < 0 Then
        Exit 'do
    End If

    TotalNumCharsRead = TotalNumCharsRead + NumCharsRead
       
    'For I = 0 To NumCharsRead - 1
    '    Dim ch As Char = buffer(I)
    '  
    '    ...
    'Next
Loop

Dim EndTime As Long = DateTime.Now
Log("Read " & TotalNumCharsRead & " characters in " & (EndTime - StartTime) & " ms")

tr.Close

results in:

Waiting for debugger to connect...
Program started.
Read 26098894 characters in 517 ms
Program terminated (StartMessageLoop was not called).

I think I can live with that.

Also what's nice is that it read 26,098,894 characters from a 26,141,343 byte file, which is what I would expect from a UTF-8 file that contains non-ASCII (ie multi-byte) characters.

 

[emexes]

I read the file char by char with the following code

Put file processing into its own Sub:

Sub HandleJsonFile(JsonDir As String, JsonFile As String) As Int

    Dim tr As TextReader
    tr.Initialize(File.OpenInput(JsonDir, JsonFile))

    Dim buffer(4096) As Char

    Dim objectsb As StringBuilder
    Dim NumObjects As Int = 0

    Dim ObjectLevel As Int = 0
    Do While True    'grr...
        Dim NumCharsRead As Int = tr.Read(buffer, 0, buffer.Length)
        If NumCharsRead < 0 Then
            Exit 'do
        End If

        For I = 0 To NumCharsRead - 1    'buffer might not have been entirely filled
            Dim ch As Char = buffer(I)
 
            If ObjectLevel = 0 Then    'not yet collecting object into string
                If ch = "{" Then
                    ObjectLevel = 1    'or ObjectLevel = ObjectLevel + 1 if you are a glutton for punishment
                    objectsb.Initialize
                    objectsb.Append(ch)    'it'd be nice to have matching confirmatory { and } at outer ends of object string
                End If
            Else    'currently collecting object into string
                objectsb.Append(ch)
         
                Select ch
                    Case "{":    'object inside object
                        ObjectLevel = ObjectLevel + 1
                 
                    Case "}":
                        ObjectLevel = ObjectLevel - 1    'back out a level
                        If ObjectLevel = 0 Then    'reached end of outer object that we were collecting into a string
                            NumObjects = NumObjects + 1
                            Log(objectsb.Length)
                            '''HandleJsonObject(objectsb.ToString)
                        End If
                 
                End Select
            End If
        Next
    Loop

    tr.Close

    Return NumObjects
 
End Sub

and tested with this:

Dim StartTime As Long = DateTime.now

Dim NumObjects As Int = HandleJsonFile(File.DirAssets, "large-file.json")    '26 MB from https://github.com/json-iterator/test-data/blob/master/large-File.jsonJsonFileName)

Dim EndTime As Long = DateTime.Now
Log("Read " & NumObjects & " objects in " & (EndTime - StartTime) & " ms")

results in this:

Waiting for debugger to connect...
Program started.
571
...
1253
1440
532
Read 487 objects in 3856 ms
Program terminated (StartMessageLoop was not called).

Still ok, considering.

 

[Yafuhenk]

@emexes
Thanks, this is by far more than I hoped for.
I looked for the "Buy me a good cup of coffee"-button but I couldn't find it!

I did some tests as well.

This small json works as expected and give three objects:

[{"sales manager":"Pascal","turnover":"100"},{"sales manager":"Jasmine","turnover":"200"},{"sales manager":"Paul","turnover":"150"}]

This one has another structure more like the one I have to work with.
It returns only one object, which contains the complete string/file

{"d":{"results":[{"sales manager":"Pascal","turnover":"100"},{"sales manager":"Jasmine","turnover":"200"},{"sales manager":"Paul","turnover":"150"}]}}

Should I simply throw away the characters until I read "[" or is there a more sophisticated way of doing this?

 

[emexes]

Should I simply throw away the characters until I read "[" or is there a more sophisticated way of doing this?

That'll work.

Or a slightly "nicer" way of doing it:

edit: sorry, Einstein here forgot that the aim was to process the JSON as it came in from the file, rather than having it all in one big looooooong string

Sub RemoveOuterArrayBrackets(Json As String) As String
   
    Dim ArrayStart As Int = Json.IndexOf("[")
    Dim ArrayEnd As Int = Json.LastIndexOf("]")

    If ArrayStart < 0 Or ArrayEnd < 0 Then
        Return Json    'if no array [ and ], then nothing to do
    End If
   
    Return Json.SubString2(ArrayStart + 1, ArrayEnd)
   
End Sub

 

[emexes]

If the underlying JSON "records" that correspond to CSV lines, aren't nested, then the parsing becomes a crapload easier.

Until your nemesis who is producing this data in as inconvenient a format as possible to make your life miserable keep your work life interesting, discovers that JSON can be nested, eg:

{"sales manager":{"first name":"Willy","middle name":"Jump","last name":"Fences","branch":"Redmond"},"turnover":"123456"}

 

[emexes]

If the underlying JSON "records" that correspond to CSV lines, aren't nested, then the parsing becomes a crapload easier.

Sub HandleSimpleJsonFile(JsonDir As String, JsonFile As String) As Int

    Dim tr As TextReader
    tr.Initialize(File.OpenInput(JsonDir, JsonFile))

    Dim buffer(4096) As Char

    Dim objectsb As StringBuilder
    Dim NumObjects As Int = 0

    Dim InObjectFlag As Boolean
    Do While True    'grr...
        Dim NumCharsRead As Int = tr.Read(buffer, 0, buffer.Length)
        If NumCharsRead < 0 Then
            Exit 'do
        End If

        For I = 0 To NumCharsRead - 1    'buffer might not have been entirely filled
            Dim Ch As Char = buffer(I)
 
            If Ch = "{" Then
                objectsb.Initialize
                objectsb.Append(Ch)    'it'd be nice to have matching confirmatory { and } at outer ends of object string
                InObjectFlag = True
            Else if InObjectFlag Then
                'currently collecting object into string
                objectsb.Append(Ch)
        
                If Ch = "}" Then
                    NumObjects = NumObjects + 1
                    HandleSimpleJsonObject(objectsb.ToString)
                    InObjectFlag = False
                End If
            End If
        Next
    Loop

    tr.Close

    Return NumObjects
 
End Sub

Sub HandleSimpleJsonObject(Json As String)
 
    Log(Json)
 
End Sub

Waiting for debugger to connect...
Program started.
{"service manager":"Noelle April","turnover":"156"}
{"commission agent":"Elon Julius","turnover":"665"}
{"sales assistant":"Landen Boden","turnover":"585"}
...
{"commission agent":"Memphis Malachi","turnover":"669"}
{"sales manager":"Araceli Nellie","turnover":"496"}
{"sales manager":"Cristian Sawyer","turnover":"819"}
Read 1234 objects in 231 ms
Program terminated (StartMessageLoop was not called).

{"d":{"results":[{"service manager":"Noelle April","turnover":"156"}
{"commission agent":"Elon Julius","turnover":"665"}{"sales assistant":"Landen Boden","turnover":"585"}{"sales manager":"Yisroel Armando","turnover":"826"}
{"marketing manager":"Oakley Liv","turnover":"584"}
{"sales assistant":"Jordan Kamryn","turnover":"691"}
{"sales manager":"Azael Aarav","turnover":"770"}{"service manager":"Bristol Callie","turnover":"246"}{"sales apprentice":"Marianna Deborah","turnover":"635"}
{"sales manager":"Andre Loyal","turnover":"699"}{"sales assistant":"Charles Gael","turnover":"253"}
{"marketing manager":"Leo Lennon","turnover":"298"}
{"sales manager":"Milani Lainey","turnover":"349"}{"sales assistant":"Jayden Silas","turnover":"436"}{"sales manager":"Ellis Everly","turnover":"52"}{"commission agent":"Maverick Fernando","turnover":"672"}{"marketing manager":"Cason Nikolai","turnover":"636"}
{"area manager":"Jackson Troy","turnover":"678"}
{"service manager":"Mary Zoie","turnover":"567"}{"service manager":"Arlo Harrison","turnover":"109"}{"service manager":"Arian Jacoby","turnover":"164"}
{"sales assistant":"Kalel Bellamy","turnover":"435"}
{"marketing manager":"Greta Berkley","turnover":"793"}
{"area manager":"Isaias Cody","turnover":"124"}{"marketing manager":"Monica Khloe","turnover":"497"}
{"commission agent":"James Zev","turnover":"399"}{"sales manager":"Lyanna Cali","turnover":"185"}{"sales assistant":"Adrianna Journey","turnover":"584"}
{"sales assistant":"Justin Major","turnover":"285"}
{"area manager":"Murphy Annabelle","turnover":"645"}{"marketing manager":"Stephen Colt","turnover":"773"}{"marketing manager":"Ronin Tyson","turnover":"738"}
{"commission agent":"Karsyn Nora","turnover":"102"}
{"area manager":"Amias Zayden","turnover":"586"}{"service manager":"Sean Drake","turnover":"724"}
{"service manager":"Aaron Huxley","turnover":"87"}{"commission agent":"Dakota Lauryn","turnover":"337"}
{"sales assistant":"Hamza Ocean","turnover":"372"}
{"area manager":"Valentina Avalynn","turnover":"158"}
{"area manager":"Yisroel Mccoy","turnover":"719"}
{"commission agent":"Micah Braylen","turnover":"132"}{"commission agent":"Luella Cassandra","turnover":"544"}
{"sales apprentice":"Kye Azael","turnover":"810"}
{"service manager":"Remy Cassidy","turnover":"676"}{"sales apprentice":"Valerie Kailey","turnover":"571"}
{"commission agent":"Frankie Amoura","turnover":"730"}
{"sales manager":"Jett Kellan","turnover":"347"}{"sales apprentice":"Ian Kace","turnover":"789"}
{"marketing manager":"Mercy Savanna","turnover":"118"}{"area manager":"Davion Baylor","turnover":"605"}
{"commission agent":"Zavier Adler","turnover":"529"}{"commission agent":"Galilea Madelyn","turnover":"148"}
{"area manager":"Paulina Cora","turnover":"709"}
{"sales manager":"Apollo Trey","turnover":"685"}{"marketing manager":"Alora Paola","turnover":"427"}{"sales apprentice":"Sean Ellis","turnover":"234"}{"commission agent":"Elaina Kimber","turnover":"487"}
{"sales apprentice":"Demi Phoenix","turnover":"582"}{"sales manager":"Wes Titus","turnover":"280"}
{"sales apprentice":"Wesson Christopher","turnover":"92"}
{"service manager":"Presley Arleth","turnover":"395"}
{"marketing manager":"Dominick Cory","turnover":"306"}{"area manager":"Katelyn Raegan","turnover":"818"}{"sales apprentice":"Jayceon Kason","turnover":"159"}{"marketing manager":"Nicolas Archer","turnover":"614"}{"commission agent":"Laylah Charlee","turnover":"615"}
{"sales apprentice":"Avalynn Sandra","turnover":"419"}
{"area manager":"Wrenley Rosie","turnover":"340"}
{"sales apprentice":"Meadow Estrella","turnover":"448"}{"sales assistant":"Destiny Nayeli","turnover":"818"}
{"sales assistant":"Colter Simon","turnover":"527"}{"sales assistant":"Tiana Ashlyn","turnover":"334"}
{"sales manager":"Travis Jonah","turnover":"71"}
{"sales apprentice":"Elianna Charli","turnover":"424"}{"sales assistant":"Stevie Priscilla","turnover":"114"}
{"area manager":"Alana Mylah","turnover":"613"}
{"sales manager":"Gordon Finnegan","turnover":"108"}{"sales apprentice":"Archie Damir","turnover":"648"}
{"area manager":"Bentley Greyson","turnover":"795"}
{"sales manager":"Nikolai Avi","turnover":"845"}
{"service manager":"Demetrius Mitchell","turnover":"679"}{"commission agent":"Lillie Alanna","turnover":"157"}
{"sales manager":"Porter Augustus","turnover":"192"}
{"marketing manager":"Orion Winston","turnover":"575"}
{"area manager":"Ephraim Osman","turnover":"687"}{"area manager":"James Colten","turnover":"278"}
{"marketing manager":"Elijah Robert","turnover":"748"}
{"sales assistant":"Israel Atlas","turnover":"546"}{"commission agent":"Kallie Kennedi","turnover":"555"}
{"sales apprentice":"Melina Virginia","turnover":"442"}
{"sales apprentice":"Annika Astrid","turnover":"391"}
{"commission agent":"Carly Sienna","turnover":"681"}
{"sales manager":"Alessia Leilani","turnover":"421"}{"sales manager":"Kaliyah Lea","turnover":"694"}{"sales manager":"Makai Adler","turnover":"248"}
{"sales apprentice":"Clementine Sandra","turnover":"306"}
{"sales assistant":"Zora Dakota","turnover":"167"}
{"marketing manager":"Madelynn Mckinley","turnover":"836"}
{"commission agent":"Adelaide Xiomara","turnover":"609"}{"sales apprentice":"Hayden Kalani","turnover":"628"}
{"sales manager":"Collin Stefan","turnover":"518"}
{"marketing manager":"Bria Nora","turnover":"235"}{"sales assistant":"Jamal Osiris","turnover":"342"}{"sales assistant":"Kartier Ezekiel","turnover":"660"}{"sales assistant":"Judson Jovanni","turnover":"376"}{"sales assistant":"Julien Diego","turnover":"672"}
{"commission agent":"Harlan George","turnover":"554"}{"commission agent":"Jaycee Mia","turnover":"202"}{"sales assistant":"Armani Elliot","turnover":"809"}{"marketing manager":"Travis Boden","turnover":"245"}
{"marketing manager":"Brennan Hezekiah","turnover":"510"}{"sales apprentice":"Jase Titus","turnover":"262"}
{"service manager":"Gabriela Haley","turnover":"493"}
{"sales apprentice":"Shawn Michael","turnover":"717"}{"area manager":"Karson Nicolas","turnover":"194"}{"sales assistant":"Ronin Sage","turnover":"135"}
{"area manager":"Myra Sabrina","turnover":"198"}{"service manager":"Monroe Tinsley","turnover":"321"}{"sales apprentice":"Jaylen Bo","turnover":"721"}
{"sales manager":"Matteo Kingston","turnover":"729"}{"service manager":"Sawyer Melvin","turnover":"328"}{"service manager":"Leonidas Brayan","turnover":"811"}
{"sales assistant":"Micah Winnie","turnover":"207"}{"service manager":"Dillon Kristian","turnover":"545"}{"commission agent":"Nina Josie","turnover":"311"}
{"sales apprentice":"Madelynn Kiara","turnover":"764"}{"sales apprentice":"Lauryn Eleanor","turnover":"277"}
{"sales apprentice":"Zelda Jazlyn","turnover":"298"}
{"sales apprentice":"Colby Beau","turnover":"637"}
{"sales manager":"Robert Brian","turnover":"729"}{"sales apprentice":"Eithan Brayan","turnover":"832"}
{"area manager":"Jefferson Johan","turnover":"196"}
{"commission agent":"Avah Noa","turnover":"347"}{"sales manager":"Finn Casey","turnover":"230"}
{"sales apprentice":"Kayson Matthias","turnover":"664"}{"marketing manager":"Ellis Camille","turnover":"268"}{"commission agent":"Lincoln Hudson","turnover":"373"}
{"marketing manager":"Landry Amira","turnover":"220"}{"area manager":"Kathryn Alaiya","turnover":"718"}{"area manager":"Brycen Hank","turnover":"540"}
{"sales apprentice":"Kye Mohammad","turnover":"726"}{"sales manager":"Ashlyn Zariah","turnover":"578"}
{"service manager":"Juliana Palmer","turnover":"406"}{"sales apprentice":"Priscilla Athena","turnover":"258"}
{"service manager":"Ian Carmelo","turnover":"338"}
{"service manager":"Thiago Justin","turnover":"665"}{"area manager":"Denver Brayan","turnover":"573"}
{"sales manager":"Santiago Issac","turnover":"172"}{"marketing manager":"Lucy Liana","turnover":"344"}{"marketing manager":"Nancy Khaleesi","turnover":"521"}{"marketing manager":"Aviana Jovie","turnover":"309"}
{"service manager":"Alexis Kylian","turnover":"653"}{"sales assistant":"Angelina Simone","turnover":"431"}
{"sales assistant":"Karla Zendaya","turnover":"321"}
{"sales assistant":"Saige Angel","turnover":"416"}
{"sales assistant":"Sylas Kylen","turnover":"479"}{"sales apprentice":"Reina Galilea","turnover":"218"}{"marketing manager":"Axel Uriah","turnover":"475"}
{"sales apprentice":"Rafael Hugh","turnover":"718"}{"commission agent":"Janiyah Shiloh","turnover":"254"}{"marketing manager":"Lillian Maliyah","turnover":"100"}{"service manager":"Kalani Nora","turnover":"778"}
{"marketing manager":"Emilio Jackson","turnover":"827"}
{"commission agent":"Noa Vida","turnover":"88"}{"area manager":"Aila Sutton","turnover":"736"}
{"sales apprentice":"Ruth Noah","turnover":"212"}
{"area manager":"Augustine Mack","turnover":"358"}{"marketing manager":"Peyton Silas","turnover":"326"}{"commission agent":"Lylah Guinevere","turnover":"388"}
{"commission agent":"Barbara Ariel","turnover":"600"}{"sales apprentice":"Delilah Valery","turnover":"194"}
{"service manager":"Alisson Sierra","turnover":"519"}
{"area manager":"Soren Zayd","turnover":"510"}{"sales apprentice":"Misael Evan","turnover":"506"}{"service manager":"Kyrie Aarav","turnover":"795"}
{"service manager":"Dustin Kiaan","turnover":"654"}{"area manager":"Elena London","turnover":"388"}
{"sales apprentice":"Jackson Ahmad","turnover":"184"}
{"sales assistant":"Paul Emir","turnover":"767"}
{"area manager":"Killian Reuben","turnover":"631"}
{"sales apprentice":"Ella Nicole","turnover":"826"}{"service manager":"Jasiah Ruben","turnover":"785"}
{"marketing manager":"Aldo Ben","turnover":"476"}
{"marketing manager":"Murphy Aiyana","turnover":"388"}{"sales assistant":"Bruno Ricardo","turnover":"90"}{"marketing manager":"Sylas Walker","turnover":"204"}
{"area manager":"Jakobe Ruben","turnover":"306"}{"sales apprentice":"Alyssa Delilah","turnover":"677"}{"marketing manager":"Annabelle Robin","turnover":"491"}
{"sales assistant":"Aitana Sarah","turnover":"783"}{"marketing manager":"Javier Shawn","turnover":"783"}
{"marketing manager":"Dream Cadence","turnover":"494"}{"marketing manager":"Rosa Nala","turnover":"831"}{"sales manager":"Kimberly Katie","turnover":"63"}{"marketing manager":"Sawyer Hannah","turnover":"531"}
{"area manager":"Tinsley Alexis","turnover":"750"}{"sales manager":"Omari Hunter","turnover":"285"}
{"marketing manager":"Zaid Angel","turnover":"586"}
{"sales manager":"Kellen Darian","turnover":"87"}{"service manager":"Harlee Azalea","turnover":"604"}
{"sales apprentice":"Elisa Danielle","turnover":"147"}
{"marketing manager":"Hayes Genesis","turnover":"344"}{"sales manager":"Hayden Travis","turnover":"371"}{"area manager":"Evan Peter","turnover":"835"}
{"sales apprentice":"Daniel Avery","turnover":"211"}{"sales assistant":"Sean Harlem","turnover":"683"}
{"commission agent":"Amira Abigail","turnover":"591"}
{"sales manager":"Lyra Palmer","turnover":"668"}{"sales manager":"April Kamryn","turnover":"220"}{"sales manager":"Della Felicity","turnover":"456"}{"service manager":"Logan Maurice","turnover":"416"}
{"marketing manager":"Rex Calvin","turnover":"57"}
{"commission agent":"Daleyza Kara","turnover":"744"}{"area manager":"Aileen Aleena","turnover":"421"}{"sales apprentice":"Colson Kylan","turnover":"783"}
{"area manager":"Kenna Nellie","turnover":"202"}
{"marketing manager":"Harlee Genesis","turnover":"330"}{"area manager":"Gabriela Rosalia","turnover":"292"}
{"sales assistant":"Jordan Alexis","turnover":"166"}{"service manager":"Ignacio Jared","turnover":"734"}
{"area manager":"Conrad Bruno","turnover":"83"}
{"commission agent":"Tiana Sophia","turnover":"445"}{"service manager":"Karter Elsa","turnover":"688"}
{"area manager":"Luca Johan","turnover":"737"}
{"sales manager":"Giselle Sienna","turnover":"507"}{"sales manager":"Stormi Kamari","turnover":"268"}
{"marketing manager":"Gordon Hugo","turnover":"83"}
{"commission agent":"Alexa Ellison","turnover":"426"}{"sales apprentice":"Jenna Antonella","turnover":"197"}
{"marketing manager":"Nelson Amir","turnover":"359"}
{"sales apprentice":"Alayna Millie","turnover":"714"}{"sales manager":"Azaria Keira","turnover":"272"}
{"sales assistant":"Elliot Scarlet","turnover":"375"}{"sales assistant":"Brylee Elora","turnover":"583"}{"service manager":"Romeo Felix","turnover":"436"}{"commission agent":"Rafael Jagger","turnover":"781"}
{"marketing manager":"Leon Ashton","turnover":"81"}{"sales apprentice":"Gia Emerie","turnover":"369"}{"sales assistant":"Miley Lainey","turnover":"507"}{"sales apprentice":"Deacon Wesley","turnover":"521"}{"area manager":"Arlette Kailey","turnover":"409"}
{"marketing manager":"Everly Jaliyah","turnover":"464"}
{"sales assistant":"Carolina Adalee","turnover":"156"}
{"service manager":"Baylee Rayna","turnover":"393"}{"commission agent":"Archie Jalen","turnover":"200"}
{"marketing manager":"Tristan Jagger","turnover":"534"}
{"area manager":"Miracle Katie","turnover":"195"}
{"area manager":"Koda Solomon","turnover":"566"}
{"commission agent":"Jimena Alexandra","turnover":"778"}{"sales assistant":"Bellamy Charley","turnover":"616"}
{"marketing manager":"Granger Otis","turnover":"484"}{"service manager":"Octavia Tessa","turnover":"444"}{"commission agent":"Van Santino","turnover":"543"}
{"area manager":"Charleigh Callie","turnover":"818"}{"sales apprentice":"Kenneth Dimitri","turnover":"844"}{"sales assistant":"Rex Franklin","turnover":"103"}
{"sales assistant":"Cohen Kason","turnover":"203"}
{"sales apprentice":"Addilynn Georgia","turnover":"74"}
{"sales assistant":"Jada Kaisley","turnover":"621"}{"sales assistant":"Adelaide Valery","turnover":"759"}{"area manager":"Kamari Angelica","turnover":"271"}
{"sales apprentice":"Rayna Rosa","turnover":"340"}
{"sales apprentice":"Dahlia Emmy","turnover":"103"}
{"commission agent":"Roselyn Aileen","turnover":"298"}
{"sales apprentice":"Quinn Lilly","turnover":"399"}
{"sales assistant":"Rory Francisco","turnover":"251"}
{"sales assistant":"Kyrie Ledger","turnover":"579"}{"service manager":"Mckenzie Laney","turnover":"654"}{"commission agent":"Margaret Maeve","turnover":"569"}{"service manager":"Aron Grayson","turnover":"619"}{"area manager":"Malaysia Lilliana","turnover":"657"}{"sales apprentice":"Jonathan Bodie","turnover":"631"}{"marketing manager":"Emilio Jase","turnover":"131"}
{"marketing manager":"Arya Alma","turnover":"189"}
{"marketing manager":"Dario Marcel","turnover":"827"}
{"marketing manager":"Emelia Emerson","turnover":"585"}
{"sales apprentice":"Malik Callahan","turnover":"293"}{"marketing manager":"Allison Madalyn","turnover":"833"}{"sales assistant":"Tyson Griffin","turnover":"276"}
{"sales assistant":"Sarai Davina","turnover":"548"}{"sales assistant":"Rosalee Kyleigh","turnover":"460"}{"commission agent":"Skyler Jonas","turnover":"609"}{"area manager":"Misael Remy","turnover":"348"}{"sales apprentice":"Kyro Jake","turnover":"603"}
{"service manager":"Michael Ira","turnover":"422"}{"service manager":"Dillon Keith","turnover":"260"}{"sales manager":"Mekhi Khari","turnover":"265"}{"area manager":"Helen Winnie","turnover":"312"}{"marketing manager":"Teresa Lyra","turnover":"395"}{"sales manager":"Gary Hunter","turnover":"763"}
{"area manager":"Brooks Cohen","turnover":"284"}{"sales manager":"Arian Kaiden","turnover":"396"}
{"area manager":"Rachel Jordan","turnover":"508"}
{"area manager":"Zayn Jerry","turnover":"734"}
{"area manager":"Janiyah Jaylah","turnover":"316"}
{"area manager":"Ryder Joelle","turnover":"254"}{"marketing manager":"Riley Vera","turnover":"544"}{"service manager":"Madden Mathew","turnover":"78"}{"marketing manager":"Cason Ethan","turnover":"386"}
{"marketing manager":"Novah Reyna","turnover":"146"}{"commission agent":"Juliana Indigo","turnover":"727"}{"service manager":"Valerie Trinity","turnover":"291"}
{"sales apprentice":"Malayah Sunny","turnover":"553"}
{"sales apprentice":"Oscar Jaiden","turnover":"82"}{"service manager":"Evander Bode","turnover":"488"}{"sales apprentice":"Prince Kamden","turnover":"402"}
{"service manager":"Octavia Presley","turnover":"62"}
{"sales assistant":"Jayla Millie","turnover":"653"}{"sales manager":"Eliam Finnley","turnover":"204"}
{"sales assistant":"Rayne Fatima","turnover":"499"}
{"sales apprentice":"Ophelia Janelle","turnover":"91"}{"area manager":"Nixon Zahir","turnover":"543"}
{"service manager":"Peyton Jimena","turnover":"846"}{"sales assistant":"Zayden Jaiden","turnover":"687"}
{"service manager":"Daleyza Myla","turnover":"539"}{"commission agent":"Catalina Halo","turnover":"534"}{"marketing manager":"Dulce Maxine","turnover":"600"}{"marketing manager":"Joyce Royalty","turnover":"458"}{"sales manager":"Cal Jeffrey","turnover":"791"}{"marketing manager":"Keira Aliza","turnover":"544"}{"service manager":"Tomas Elon","turnover":"819"}
{"sales manager":"Johnny Kyle","turnover":"410"}{"sales assistant":"Watson Wilson","turnover":"523"}
{"area manager":"Brendan Stetson","turnover":"733"}
{"marketing manager":"Everett Cesar","turnover":"337"}{"marketing manager":"Janiyah Keyla","turnover":"277"}{"marketing manager":"Zane Aryan","turnover":"742"}
{"sales manager":"Aitana Aliyah","turnover":"54"}
{"service manager":"Julissa Milan","turnover":"431"}{"marketing manager":"Jakai Spencer","turnover":"806"}{"area manager":"Blair Maleah","turnover":"275"}
{"area manager":"Annika Julie","turnover":"253"}
{"sales assistant":"Madeleine Elizabeth","turnover":"437"}{"sales manager":"Addilyn Evelynn","turnover":"682"}
{"sales assistant":"Audrey River","turnover":"509"}{"marketing manager":"Jessica Sutton","turnover":"122"}{"sales apprentice":"Kingsley Moshe","turnover":"815"}{"commission agent":"Judson Ira","turnover":"503"}
{"marketing manager":"Remi Lucia","turnover":"778"}
{"marketing manager":"Mitchell Adrian","turnover":"802"}
{"sales apprentice":"Charles Greyson","turnover":"60"}{"sales manager":"Tessa Nora","turnover":"212"}{"service manager":"Ruby Vanessa","turnover":"403"}
{"marketing manager":"Riggs Wes","turnover":"529"}{"sales manager":"Maddox Myles","turnover":"667"}
{"sales manager":"Royal Langston","turnover":"369"}{"marketing manager":"Mikayla Esme","turnover":"185"}
{"service manager":"Josephine Martha","turnover":"441"}{"commission agent":"Lilyana Emmy","turnover":"148"}
{"sales manager":"Aziel Dimitri","turnover":"77"}
{"commission agent":"Sarah Jessica","turnover":"136"}{"service manager":"Yara Lilah","turnover":"629"}{"sales apprentice":"Amora Remington","turnover":"692"}
{"marketing manager":"Jones Victor","turnover":"362"}
{"service manager":"Jianna Blair","turnover":"675"}
{"area manager":"Azrael Dante","turnover":"836"}{"service manager":"Juliet Nevaeh","turnover":"682"}
{"area manager":"Titan Matteo","turnover":"199"}{"sales manager":"Barrett Finn","turnover":"65"}{"marketing manager":"Braylen Dax","turnover":"210"}
{"sales apprentice":"Xiomara Lilliana","turnover":"55"}{"sales apprentice":"Mateo Simon","turnover":"386"}{"sales assistant":"Sarai Yareli","turnover":"99"}{"commission agent":"Gunnar Eddie","turnover":"106"}
{"commission agent":"Joel Pierce","turnover":"323"}
{"sales assistant":"Moshe Harvey","turnover":"71"}{"service manager":"Trenton Cory","turnover":"845"}{"sales apprentice":"Iris Celeste","turnover":"336"}
{"sales assistant":"Carl Luka","turnover":"831"}
{"commission agent":"Tomas Bridger","turnover":"331"}{"sales assistant":"Joshua Neo","turnover":"429"}{"sales apprentice":"Fabian Tristan","turnover":"74"}
{"sales assistant":"Martin Ulises","turnover":"72"}{"sales apprentice":"Jamal Cody","turnover":"398"}{"sales assistant":"Declan Beau","turnover":"679"}{"service manager":"Calliope Valerie","turnover":"202"}
{"service manager":"Eliana Remy","turnover":"455"}{"marketing manager":"Cataleya Peyton","turnover":"133"}{"sales apprentice":"Gracie Joyce","turnover":"211"}{"sales apprentice":"Victor Layne","turnover":"717"}{"sales manager":"Gian Malakai","turnover":"513"}
{"sales apprentice":"Rowan Jaxx","turnover":"754"}{"commission agent":"Maliyah Alejandra","turnover":"569"}
{"sales apprentice":"Atreus Cayson","turnover":"379"}
{"sales assistant":"Lee Mordechai","turnover":"173"}{"service manager":"Maryam Charlee","turnover":"615"}{"sales apprentice":"Leonardo Langston","turnover":"614"}
{"sales manager":"Leland Colten","turnover":"216"}{"commission agent":"Kade Manuel","turnover":"665"}{"sales assistant":"Judson Ander","turnover":"429"}
{"area manager":"Tanner Jacoby","turnover":"763"}{"service manager":"Eliza Marie","turnover":"471"}
{"marketing manager":"Zev Ronin","turnover":"691"}{"area manager":"Angelica Demi","turnover":"345"}{"sales manager":"Adriel Blaze","turnover":"278"}{"service manager":"Kyrie Judah","turnover":"606"}
{"sales assistant":"Landen Orion","turnover":"833"}{"marketing manager":"Khalid Anthony","turnover":"78"}
{"commission agent":"Dior Halo","turnover":"407"}{"marketing manager":"Miley Whitley","turnover":"756"}
{"area manager":"Callen Ray","turnover":"223"}{"commission agent":"Baylor Izaiah","turnover":"482"}{"service manager":"Imani Mary","turnover":"568"}
{"area manager":"Galilea Gracelyn","turnover":"626"}
{"sales assistant":"Leyla Delilah","turnover":"655"}{"service manager":"Esteban Julio","turnover":"175"}
{"service manager":"Jenna Noor","turnover":"394"}{"area manager":"Sincere Khai","turnover":"645"}
{"sales apprentice":"Natalie Jolene","turnover":"548"}{"sales assistant":"Journey Simone","turnover":"823"}
{"service manager":"Aries Jesiah","turnover":"419"}{"area manager":"Ulises Tobias","turnover":"568"}{"sales assistant":"Banks Erick","turnover":"358"}
{"marketing manager":"Lyanna Capri","turnover":"259"}
{"service manager":"Colter Axel","turnover":"616"}
{"marketing manager":"Callahan Antonio","turnover":"326"}{"sales manager":"Kali Summer","turnover":"249"}{"sales assistant":"Reed John","turnover":"662"}{"sales manager":"Duke Mohamed","turnover":"790"}
{"commission agent":"Kenna Avayah","turnover":"729"}
{"marketing manager":"Ayden Evan","turnover":"401"}
{"service manager":"Estella Addilynn","turnover":"584"}{"service manager":"Wrenley Winnie","turnover":"413"}
{"sales assistant":"Tatum Jackson","turnover":"817"}{"sales manager":"Patrick Zaid","turnover":"775"}
{"area manager":"Samson Avery","turnover":"429"}{"sales apprentice":"Hunter Meghan","turnover":"314"}
{"sales manager":"Alistair Ira","turnover":"660"}
{"commission agent":"Clyde Charles","turnover":"329"}{"area manager":"Harlem Amias","turnover":"573"}
{"sales apprentice":"Zakai Bjorn","turnover":"621"}{"sales assistant":"Daphne Kendall","turnover":"813"}
{"service manager":"Kylan David","turnover":"481"}
{"sales assistant":"Fernanda Kimora","turnover":"149"}{"sales apprentice":"Kailani Alyssa","turnover":"545"}{"marketing manager":"Edison Shane","turnover":"815"}{"service manager":"Samson Tadeo","turnover":"822"}
{"area manager":"Luka Ariel","turnover":"464"}{"marketing manager":"Paloma Nadia","turnover":"824"}
{"sales assistant":"Dominick Stefan","turnover":"740"}{"service manager":"Riggs Brock","turnover":"684"}
{"marketing manager":"Adele Roselyn","turnover":"286"}
{"sales apprentice":"Roselyn Kamryn","turnover":"808"}
{"sales assistant":"Larry Emir","turnover":"202"}
{"commission agent":"Brady Ford","turnover":"147"}
{"area manager":"Marianna Madisyn","turnover":"562"}
{"sales assistant":"Jose Anderson","turnover":"604"}
{"service manager":"Vanessa Hattie","turnover":"402"}
{"area manager":"Kinsley Kaylani","turnover":"510"}{"sales assistant":"Reuben Alan","turnover":"198"}{"sales apprentice":"Misael Collin","turnover":"255"}
{"commission agent":"Liliana Alessia","turnover":"433"}
{"sales apprentice":"Justice Loyal","turnover":"202"}{"area manager":"Amelie Cadence","turnover":"486"}{"sales manager":"Julien Darius","turnover":"791"}
{"sales assistant":"Reina Alaya","turnover":"631"}{"area manager":"Lexi Rose","turnover":"547"}
{"area manager":"Kaylee Kensley","turnover":"581"}{"sales apprentice":"Damian Lachlan","turnover":"708"}
{"commission agent":"Rohan Kabir","turnover":"175"}
{"sales manager":"Adler Brixton","turnover":"575"}{"marketing manager":"Peter Finnegan","turnover":"826"}{"area manager":"Alberto Samir","turnover":"712"}
{"sales manager":"Angelique Fatima","turnover":"140"}
{"sales assistant":"Berkley Phoenix","turnover":"92"}
{"sales assistant":"Preston Abdiel","turnover":"327"}{"sales manager":"Lola Catalina","turnover":"710"}{"area manager":"Khai Seven","turnover":"798"}
{"commission agent":"Trace Rene","turnover":"586"}
{"service manager":"Kali Nova","turnover":"784"}
{"sales apprentice":"Amaia Bella","turnover":"243"}{"sales manager":"Zayden Aarav","turnover":"369"}{"service manager":"Roman Alec","turnover":"801"}{"marketing manager":"Marina Mina","turnover":"565"}{"commission agent":"Blaine Gabriel","turnover":"781"}
{"area manager":"Whitley Ada","turnover":"825"}
{"commission agent":"Alayna Sylvie","turnover":"542"}
{"sales manager":"Guinevere Madeleine","turnover":"631"}{"sales apprentice":"Marianna Ayla","turnover":"257"}{"sales assistant":"Judah Cody","turnover":"738"}
{"marketing manager":"Elliott Dean","turnover":"743"}{"service manager":"Sylas Amias","turnover":"717"}
{"sales manager":"Ada Veronica","turnover":"660"}{"sales manager":"Kaylani Crystal","turnover":"785"}
{"area manager":"Messiah Vicente","turnover":"582"}{"sales apprentice":"Poppy Valerie","turnover":"154"}{"sales manager":"Jaylee Avery","turnover":"672"}{"area manager":"Marshall Denver","turnover":"296"}
{"sales apprentice":"Eli Austin","turnover":"445"}{"area manager":"Aliana Alora","turnover":"147"}{"commission agent":"Dangelo Jayson","turnover":"559"}
{"area manager":"Joy Kori","turnover":"58"}{"area manager":"Dakari Davion","turnover":"335"}
{"sales assistant":"Conner Coleson","turnover":"305"}
{"commission agent":"Kace Ellis","turnover":"108"}
{"sales apprentice":"Princess Ellie","turnover":"169"}
{"sales assistant":"Marlowe Mallory","turnover":"144"}{"sales manager":"Angelica Rebekah","turnover":"611"}{"marketing manager":"Marina Madelyn","turnover":"816"}
{"service manager":"Timothy Jair","turnover":"132"}{"area manager":"Maverick Enoch","turnover":"161"}{"sales manager":"Dorian Elliot","turnover":"54"}
{"service manager":"Clyde Sawyer","turnover":"330"}{"area manager":"Kartier Rafael","turnover":"414"}
{"sales apprentice":"Theodore Landen","turnover":"238"}{"commission agent":"Alexander Chandler","turnover":"704"}
{"sales manager":"Presley Ariyah","turnover":"737"}{"area manager":"Sebastian Daxton","turnover":"645"}
{"sales manager":"Forest Agustin","turnover":"807"}
{"marketing manager":"Angie Journi","turnover":"437"}
{"sales manager":"Nathaniel Noel","turnover":"301"}{"area manager":"Alora Hunter","turnover":"67"}
{"sales assistant":"Sebastian Aryan","turnover":"137"}
{"sales assistant":"Colt Carl","turnover":"714"}
{"commission agent":"Lennox Layne","turnover":"654"}
{"sales assistant":"Emerson Emilia","turnover":"241"}
{"sales manager":"Paige Kimora","turnover":"654"}{"commission agent":"Augustine Hector","turnover":"606"}
{"sales assistant":"Hunter Tatum","turnover":"632"}{"area manager":"Zaiden Landen","turnover":"392"}
{"commission agent":"Elisha Malachi","turnover":"306"}{"marketing manager":"Joe Abel","turnover":"271"}
{"commission agent":"Ares Odin","turnover":"770"}
{"sales apprentice":"Kenzie Chloe","turnover":"617"}
{"marketing manager":"Ruth Alexa","turnover":"171"}
{"marketing manager":"Yahir Ishaan","turnover":"280"}
{"sales assistant":"Kingston Lance","turnover":"833"}{"service manager":"Sophia Braelynn","turnover":"131"}
{"sales manager":"Yahir Wes","turnover":"326"}{"service manager":"Nathaniel Jakob","turnover":"795"}{"area manager":"Maeve Lauren","turnover":"811"}
{"sales manager":"Melvin Lewis","turnover":"174"}
{"service manager":"Zaylee Nora","turnover":"759"}{"area manager":"Royal Winter","turnover":"594"}
{"marketing manager":"Cataleya Lainey","turnover":"558"}
{"service manager":"Annie Lyanna","turnover":"357"}
{"marketing manager":"Carl Jorge","turnover":"134"}{"sales assistant":"Keyla Amy","turnover":"748"}{"commission agent":"Abraham Eduardo","turnover":"477"}{"marketing manager":"Ezra Aubriella","turnover":"468"}{"sales assistant":"Maeve Madelyn","turnover":"405"}{"sales assistant":"Bear Talon","turnover":"149"}
{"sales assistant":"Tanner Grady","turnover":"63"}
{"area manager":"Kyle Gideon","turnover":"521"}
{"service manager":"Malia Rory","turnover":"479"}{"area manager":"Ellie Rosalie","turnover":"484"}{"marketing manager":"Camden Harrison","turnover":"689"}
{"marketing manager":"Crosby Sutton","turnover":"658"}
{"area manager":"Jiraiya Kash","turnover":"532"}
{"commission agent":"Ignacio Jacoby","turnover":"663"}{"area manager":"Alberto Jonas","turnover":"648"}
{"marketing manager":"Hailey Peyton","turnover":"102"}{"service manager":"Jaylani Ximena","turnover":"170"}{"area manager":"Kasen Cruz","turnover":"697"}
{"service manager":"Alan Beau","turnover":"840"}
{"sales assistant":"Rylan Jaylani","turnover":"757"}{"sales assistant":"Trinity Kai","turnover":"847"}
{"sales assistant":"Cason Amos","turnover":"621"}
{"sales apprentice":"Jase Rocco","turnover":"313"}{"sales manager":"Caroline Amaya","turnover":"260"}
{"commission agent":"Weston Justice","turnover":"526"}
{"area manager":"Hanna Camille","turnover":"74"}
{"sales apprentice":"Tristan Keith","turnover":"848"}
{"sales assistant":"Nikolas Frederick","turnover":"82"}
{"sales apprentice":"Isabelle Mikaela","turnover":"594"}{"service manager":"Griffin Carlos","turnover":"123"}
{"area manager":"Sky Miracle","turnover":"604"}{"sales manager":"Mohammed Russell","turnover":"681"}
{"marketing manager":"Forrest Lawrence","turnover":"226"}{"sales assistant":"Bentley Oliver","turnover":"142"}{"marketing manager":"Zev Joel","turnover":"802"}
{"sales manager":"Armando Damir","turnover":"503"}{"sales apprentice":"Alyssa Aleena","turnover":"600"}
{"commission agent":"Victor Rey","turnover":"136"}
{"sales apprentice":"Hayes Aden","turnover":"667"}{"area manager":"Gwendolyn Melissa","turnover":"432"}
{"service manager":"Marceline Charleigh","turnover":"559"}
{"service manager":"Camilla Lainey","turnover":"520"}{"area manager":"Kaden Christian","turnover":"615"}{"commission agent":"Annabella Erin","turnover":"95"}
{"marketing manager":"Leilany Aria","turnover":"528"}{"commission agent":"Laila Charleigh","turnover":"144"}{"marketing manager":"Kellan Cassius","turnover":"94"}
{"sales apprentice":"Harvey Shiloh","turnover":"409"}{"marketing manager":"Christopher Angel","turnover":"274"}{"area manager":"Isabela Elliott","turnover":"570"}{"commission agent":"Mac Nicolas","turnover":"471"}{"commission agent":"Raelynn Faith","turnover":"387"}
{"sales assistant":"Maisie Alaiya","turnover":"126"}{"sales manager":"Lucca Asher","turnover":"597"}
{"commission agent":"Mariah Annabella","turnover":"173"}
{"sales apprentice":"Journey Iyla","turnover":"186"}
{"sales assistant":"Palmer Lewis","turnover":"832"}{"sales apprentice":"August Azaria","turnover":"242"}
{"sales manager":"Nancy Novah","turnover":"477"}
{"service manager":"Raul Bentley","turnover":"811"}
{"service manager":"Zola Jacqueline","turnover":"479"}{"commission agent":"Parker Emely","turnover":"561"}{"sales assistant":"Colter Mylo","turnover":"344"}{"sales manager":"Gideon Arthur","turnover":"466"}{"sales manager":"Lia Florence","turnover":"452"}
{"sales assistant":"Gideon Yisroel","turnover":"130"}{"commission agent":"Goldie Anaya","turnover":"314"}
{"service manager":"Brinley Penny","turnover":"354"}{"sales manager":"Violet Carter","turnover":"454"}
{"marketing manager":"Aaliyah Liana","turnover":"169"}{"marketing manager":"Kareem Khalid","turnover":"241"}
{"sales manager":"Lauren Ellison","turnover":"732"}
{"area manager":"Nala Leilany","turnover":"164"}
{"sales manager":"Estella Clara","turnover":"719"}{"commission agent":"Marlee Hadassah","turnover":"484"}
{"marketing manager":"Addison Mylah","turnover":"807"}{"service manager":"Hanna Lillie","turnover":"355"}{"commission agent":"Memphis Malachi","turnover":"669"}
{"sales manager":"Araceli Nellie","turnover":"496"}{"sales manager":"Cristian Sawyer","turnover":"819"}]}}

 

[emexes]

If the underlying JSON "records" that correspond to CSV lines, aren't nested, then the parsing becomes a crapload easier.

I used plain old boring regex , instead of getting up to speed with the trees and maps of a full-blown JSON parser.

www.regex101.com might explain the regex search pattern better than I did.

Sub HandleSimpleJsonObject(Json As String)
 
    Dim sb As StringBuilder
    sb.Initialize
 
    Dim Pattern As String =    _
        "[\{,]"              & _    'look for leading { or , before each field
        "\s*"                & _    'then maybe space
        """"                 & _    'then opening quote for field name
        "([^""]*)"           & _    'then field name = everything other than quotes
        """"                 & _    'then closing quote for field name
        "\s*\:\s*"           & _    'then (maybe space) colon (maybe more space)
        """"                 & _    'then opening quote for field value
        "([^""]*)"           & _    'then field value = everything other than quotes
        """"                        'then closing quote for field value

    Dim NumFields As Int = 0
 
    Dim m As Matcher = Regex.Matcher(Pattern, Json)
    Do While m.Find
        If NumFields > 0 Then
            sb.Append(", ")    'I like spaces after CSV commas, but not everybody does
        End If
   
        sb.Append("""" & m.Group(2) & """")    '.Group(1) is field name, .Group(2) is field value
        NumFields = NumFields + 1
    Loop
 
    Dim CsvLine As String = sb.ToString
    Log(CsvLine)
 
End Sub

Waiting for debugger to connect...
Program started.
"Noelle April", "156"
"Elon Julius", "665"
"Landen Boden", "585"
"Yisroel Armando", "826"
...
"Hanna Lillie", "355"
"Memphis Malachi", "669"
"Araceli Nellie", "496"
"Cristian Sawyer", "819"
Read 1234 objects in 429 ms
Program terminated (StartMessageLoop was not called).

 

[Yafuhenk]

@emexes
I will have a close look at your post later and I am sure I will use it.
I solved it by removing the characters until "[" appears. but I think that is not the correct way. (Here you see the difference between an experienced coder and a beginner).

Sub HandleJsonFile(JsonDir As String, JsonFile As String) As JsonImportInfo
    Dim StartTime As Long = DateTime.now
    Dim StartPos As Int = 0
    Dim NumLoops As Int = 0
    Dim tr As TextReader
    tr.Initialize(File.OpenInput(JsonDir, JsonFile))
    Dim buffer(4096) As Char
    Dim objectsb As StringBuilder
    Dim NumObjects As Int = 0
    Dim ObjectLevel As Int = 0
    Do While True    'grr...
        Dim NumCharsRead As Int = tr.Read(buffer, 0, buffer.Length)
        If NumCharsRead < 0 Then
            Exit 'do
        End If
        If NumLoops = 0 Then
            For I = 0 To NumCharsRead - 1
                If buffer(I) = "[" Then
                    StartPos = I
                    Exit
                End If
            Next
        End If
        If NumLoops <> 0 Then
            StartPos = 0
        End If
        For I = StartPos To NumCharsRead - 1    'buffer might not have been entirely filled
            Dim ch As Char = buffer(I)
            If ObjectLevel = 0 Then    'not yet collecting object into string
                If ch = "{" Then
                    ObjectLevel = 1    'or ObjectLevel = ObjectLevel + 1 if you are a glutton for punishment
                    objectsb.Initialize
                    objectsb.Append(ch)    'it'd be nice to have matching confirmatory { and } at outer ends of object string
                End If
            Else    'currently collecting object into string
                objectsb.Append(ch)
                Select ch
                    Case "{":    'object inside object
                        ObjectLevel = ObjectLevel + 1
                    Case "}":
                        ObjectLevel = ObjectLevel - 1    'back out a level
                        If ObjectLevel = 0 Then    'reached end of outer object that we were collecting into a string
                            NumObjects = NumObjects + 1
                            Dim Jp As JSONParser
                            Dim m As Map
                            Jp.Initialize(objectsb.ToString) 'Read the text from a file.
                            m = Jp.NextObject
                            For Each k As String In m.Keys
                                'Start reading objects here
                                'Log(m.Get("YCUST_SAL__YYRB1_T"))
                            Next
                        End If
                End Select
            End If
        Next
        NumLoops = NumLoops + 1
    Loop
    tr.Close
    Dim Endtime As Long = DateTime.now
    Dim JInfo As JsonImportInfo
    JInfo.Initialize
    JInfo.NumObjects = NumObjects
    JInfo.NumMSec = (Endtime - StartTime)
    Return JInfo
End Sub

Waiting for debugger to connect...
File sales_102021_092022.json, contains 64329 objects and was processed in 6288 ms.

 

[emexes]

I solved it by removing the characters until "[" appears. but I think that is not the correct way. (Here you see the difference between an experienced coder and a beginner

1/ if it works, it's correct (ok, correct enough) (ok, ok, usually correct enough)

2/ experience sometimes leads programmers down long-and-complicated "obvious" path, when unbiased minds can see something simpler

eg, instead of all that regex kerfuffle, you probably noticed that the JSON is already half-way to being CSV, and so might do:

Sub HandleEvenSimplerJsonObject(Json As String)
 
    If Json.StartsWith("{") And Json.EndsWith("}") Then    'to be sure, to be sure
        If Json.Contains(""":""") Then    'if contains quote-colon-quote indicating JSON is formatted in almost-CSV style (if one field is, then that bodes well for the other fields too)
            Dim CsvLine As String = Json.SubString2(1, Json.Length - 1).Replace(""":""", """,""")
            Log(CsvLine)
            Return
        End If
    End If

    Log("Too empty or too complicated for me:")
    Log(Json)
     
End Sub

Waiting for debugger to connect...
Program started.
"service manager","Noelle April","turnover","156"
"commission agent","Elon Julius","turnover","665"
"sales assistant","Landen Boden","turnover","585"
"sales manager","Yisroel Armando","turnover","826"
...
"service manager","Hanna Lillie","turnover","355"
"commission agent","Memphis Malachi","turnover","669"
"sales manager","Araceli Nellie","turnover","496"
"sales manager","Cristian Sawyer","turnover","819"
Read 1234 objects in 782 ms
Program terminated (StartMessageLoop was not called).

and if you don't like the "turnover column" then:

Sub HandleEvenSimplerJsonObject(Json As String)
 
    If Json.StartsWith("{") And Json.EndsWith("}") Then    'to be sure, to be sure
        If Json.Contains(""":""") Then    'if contains quote-colon-quote indicating JSON is formatted in almost-CSV style (if one field is, then that bodes well for the other fields too)
            Dim CsvLine As String = Json.SubString2(1, Json.Length - 1).Replace(""":""", """,""")
         
            CsvLine = CsvLine.Replace(",""turnover"",", ",")    'remove all-the-same turnover column
         
            Log(CsvLine)
            Return
        End If
    End If

    Log("Too empty or too complicated for me:")
    Log(Json)

End Sub

Waiting for debugger to connect...
Program started.
"service manager","Noelle April","156"
"commission agent","Elon Julius","665"
"sales assistant","Landen Boden","585"
"sales manager","Yisroel Armando","826"
...
"service manager","Hanna Lillie","355"
"commission agent","Memphis Malachi","669"
"sales manager","Araceli Nellie","496"
"sales manager","Cristian Sawyer","819"
Read 1234 objects in 774 ms
Program terminated (StartMessageLoop was not called).

 

[Yafuhenk]

@emexes, if you are interested, please find a json string as I get it from SAP. (I modified data)

{"d":{"results":[{"__metadata":{"id":"http://main.xxxx.org/sap/opu/odata/sap/xxx_xxx_ODATA_SRV/xxx_xxx_ODATAResults('V2.124.122.0_1.2.6.6.1.2.1.1.1.2.1.1%3A6.6.8.8.3.2.1.2.2.4.3.10.4.10.3.10.3.2.4.7.3.0.3.0.8.3.2.3.X.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1_IBT1120221120220000000000202211V92023000117430001098101X991020200000030700SB7400070320180007032018BBOV040067708900677089DE')","uri":"http://main.xxxx.org/sap/opu/odata/sap/xxx_xxx_ODATA_SRV/xxx_xxx_ODATAResults('V2.124.122.0_1.2.6.6.1.2.1.1.1.2.1.1%3A6.6.8.8.3.2.1.2.2.4.3.10.4.10.3.10.3.2.4.7.3.0.3.0.8.3.2.3.X.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1_IBT1120221120220000000000202211V92023000117430001098101X991020200000030700SB7400070320180007032018BBOV040067708900677089DE')","type":"xxx_xxx_ODATA_SRV.xxx_xxx_ODATAResult"},"ID":"V2.124.122.0_1.2.6.6.1.2.1.1.1.2.1.1:6.6.8.8.3.2.1.2.2.4.3.10.4.10.3.10.3.2.4.7.3.0.3.0.8.3.2.3.X.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1_IBT1120221120220000000000202211V92023000117430001098101X991020200000030700SB7400070320180007032018BBOV040067708900677089DE","TotaledProperties":"","C1CALMONTH":"202211","C1CALMONTH_T":"11.2022","C1FISCYEAR":"V9/2023","YCUST_SAL__YYRB1":"00000000","YCUST_SAL__YYRB1_T":"Laura Jones","YCUST_SAL__YYRB2":"11111111","YCUST_SAL__YYRB2_T":"Peter Hanks","YCUST_SAL__YYRB3":"","YCUST_SAL__YYRB3_T":"","C1G_CWWSBU":"01","C1G_CWWSBU_T":"Automotive","C1CUSTOMER__0G_CWWIKZ":"X","C1CUSTOMER__0G_CWWIKZ_T":"External Sales","C1DISTR_CHAN":"99","C1DISTR_CHAN_T":"Extern Sales","C1DIVISION":"10","C1DIVISION_T":"ARC","C1SALESORG":"2222","C1SALESORG_T":"Car SA","C1PLANT":"","C1PLANT_T":"Not assigned","C1PROFIT_CTR__Y_PC_AGGR":"33333","C1PROFIT_CTR__Y_PC_AGGR_T":"Components","C1PROFIT_CTR__Y_SBF":"4444","C1PROFIT_CTR__Y_SBF_T":"Optical Solutions","C1CUSTOMER__Y_KNZRN":"5555555","C1CUSTOMER__Y_KNZRN_T":"Optics GmbH","C1CUST_SALES__0CUST_GRP2":"","C1CUST_SALES__0CUST_GRP2_T":"Not assigned","C1CUSTOMER":"5555555","C1CUSTOMER_T":"Optics GmbH","C1DLV_BLOCK":"","C1DLV_BLOCK_T":"Not assigned","YCUST_SAL__YYCUSTSEG":"BB","YCUST_SAL__YYCUTSEG_T":"Basic","YPT_APPZU__YPT_APPL":"OV66","YPT_APPZU__YPT_APPL_T":"Partner","C1G_CWWAGR":"00777","C1G_CWWAGR_T":"Lightguides","C1MATERIAL__0BASIC_MATL":"","C1MATERIAL":"","C1MATERIAL_T":"Not assigned","C1MATERIAL__0BBP_PROD":"","C1ORD_REASON":"","C1ORD_REASON_T":"Not assigned","C1PROD_HIER":"08900677","C1PROD_HIER_T":"OC coated light guides","YYYPRGRP":"89","YYYPRGRP_T":"OC coated light guides","C1G_CWWLAG":"DE","C1G_CWWLAG_T":"Germany","C1G_CWWLWE":"","C1G_CWWLWE_T":"Not assigned","YC_INCOMORDER_NETSALES":"0","YC_INCOMORDER_NETSALES_F":"","YC_INCOMORDER_NETSALES_E":"","C10733DALXCD8PAUPE76VNGEFO":"0","C10733DALXCD8PAUPE76VNGEFO_F":"","C10733DALXCD8PAUPE76VNGKR8":"0","C10733DALXCD8PAUPE76VNGKR8_F":"","C10733DALXCD8PAUPE76VNGR2S":"8888.88","C10733DALXCD8PAUPE76VNGR2S_F":"8.888,88 EUR","C10733DALXCD8PAUPE76VNGR2S_E":"EUR","YSTDMAR_2":"0","YSTDMAR_2_F":"","YSTDMAR_2_E":"","C10733DALXCD8PAUPE76VNH3PW":"0","C10733DALXCD8PAUPE76VNH3PW_F":"","C10733DALXCD8PAUPE76VNH3PW_E":"","C10733DALXCD8PAUPE76VNHA1G":"0","C1073DALXCD8PAUPE76VNHA1G_F":"","C10733DALXCD8PAUPE76VNHA1G_E":"","C10733DALXCD8PAUPE76VNHGD0":"0","C10733DALXCD8PAUPE76VNHGD0_F":"","C10733DALXCD8PAUPE76VNHGD0_E":"PC","C10733DALXCD8PAUPE76VNHMOK":"0","C10733DALXCD8PAUPE76VNHMOK_F":"","C10733DALXCD8PAUPE76VNHMOK_E":"","C10733DALXCD8X0MFZ4KUKPWFB":"0","C10733DALXCD8X0MFZ4KUKPWFB_F":"","C10733DALXCD8X0MFZ4KUKPWFB_E":"","Parameters":{"__deferred":{"uri":"http://main.xxxx.org/sap/opu/odata/sap/xxx_xxx_ODATA_SRV/xxx_xxx_ODATAResults('V2.124.122.0_1.2.6.6.1.2.1.1.1.2.1.1%3A6.6.8.8.3.2.1.2.2.4.3.10.4.10.3.10.3.2.4.7.3.0.3.0.8.3.2.3.X.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1_IBT1120221120220000000000202211V92023000117430001098101X991020200000030700SB7400070320180007032018BBOV040067708900677089DE')/Parameters"}}}]}}

 

[emexes]

@emexes, if you are interested,

Does a bear defecate in the woods?

please find a json string as I get it from SAP. (I modified data)

{"d":{"results":[{"__metadata":{"id":"http://main.xxxx.org/sap/opu/odata/sap/xxx_xxx_ODATA_SRV/xxx_xxx_ODATAResults('V2.124.122.0_1.2.6.6...

Part of me says that whoever designed that data schema oughta be stoned, but on the other hand, it is probably the result of evolving over decades to handle stuff never envisioned at the beginning, so... let the one without sin amongst us, cast the first stone.

My thought now is that it still looks like a flatish object, but probably a very "wide" one ie a single object containing all the tagged values, that potentially is too large to load into a String to feed the B4X built-in regex.

But... if all the tagged values are in the format "tag":"value" (quote-tag-quote-colon-quote-value-quote) and there are no escaped-quotes-inside-quotes, then it's easy enough to grab those tagged values directly from the incoming file, by gathering characters together by groups-of-4-quotes.

Sub HandleJsonFile4Quotes(JsonDir As String, JsonFile As String) As Int

    Dim tr As TextReader
    tr.Initialize(File.OpenInput(JsonDir, JsonFile))

    Dim buffer(4096) As Char
 
    Dim objectsb As StringBuilder
    Dim NumObjects As Int = 0

    Dim NumQuotes As Int = 0
    Do While True    'grr...
        Dim NumCharsRead As Int = tr.Read(buffer, 0, buffer.Length)
        If NumCharsRead < 0 Then
            Exit 'do
        End If

        For I = 0 To NumCharsRead - 1    'buffer might not have been entirely filled
            Dim Ch As Char = buffer(I)
         
            Select Case NumQuotes
                Case 0:
                    If Ch = """" Then    'ignoring whitespace, separating commas, closing structure tags } and ]
                        objectsb.Initialize
                        objectsb.Append(Ch)
                        NumQuotes = 1
                    End If
                 
                Case 1, 3:
                    objectsb.Append(Ch)
                    If Ch = """" Then
                        NumQuotes = NumQuotes + 1
                        If NumQuotes = 4 Then
                            NumObjects = NumObjects + 1
                            HandleJsonTagValue(objectsb.ToString)
                            NumQuotes = 0
                        End If
                    End If
                 
                Case 2:
                    Select Case Ch
                        Case ":":
                            objectsb.Append(Ch)
                         
                        Case " ", Chr(9), Chr(10), Chr(13):
                            'skip whitespace
                         
                        Case """":
                            objectsb.Append(Ch)
                            NumQuotes = NumQuotes + 1
                         
                        case Else:
                            objectsb.Append(Ch)
                         
                            if "{[".Contains(Ch) Then    'if Ch is { or [
                                Log("Skipping structure tag " & objectsb.ToString)
                            Else
                                Log("Wtf is " & objectsb.ToString & " ???")
                            end If
                         
                            NumQuotes = 0
                         
                    End Select
                 
            End Select
        Next
    Loop

    tr.Close

    Return NumObjects
 
End Sub

Sub HandleJsonTagValue(S As String)
 
    If S.Length < 5 Then
        Log("Too short: " & S)
    else if S.StartsWith("""") = False Or S.EndsWith("""") = False Then
        Log("Missing quotes: " & S)
    Else
        Dim P As Int = S.IndexOf(""":""")
        If P < 0 Then
            Log("Missing colon: " & S)
        Else
            Dim Tag As String = S.SubString2(1, P)
            Dim Value As String = S.SubString2(P + 3, S.Length - 1)
         
            If Value.Length > 0 Then
                Log("Value of " & Tag & " is " & Value)
            Else
                Log("Value " & Tag & " is empty")
            End If
        End If
    End If
 
End Sub

Waiting for debugger to connect...
Program started.
Skipping structure tag "d":{
Skipping structure tag "results":[
Skipping structure tag "__metadata":{
Value of id is http://main.xxxx.org/sap/opu/odata/sap/xxx_xxx_ODATA_SRV/xxx_xxx_ODATAResults('V2.124.122.0_1.2.6.6.1.2.1.1.1.2.1.1%3A6.6.8.8.3.2.1.2.2.4.3.10.4.10.3.10.3.2.4.7.3.0.3.0.8.3.2.3.X.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1_IBT1120221120220000000000202211V92023000117430001098101X991020200000030700SB7400070320180007032018BBOV040067708900677089DE')
Value of uri is http://main.xxxx.org/sap/opu/odata/sap/xxx_xxx_ODATA_SRV/xxx_xxx_ODATAResults('V2.124.122.0_1.2.6.6.1.2.1.1.1.2.1.1%3A6.6.8.8.3.2.1.2.2.4.3.10.4.10.3.10.3.2.4.7.3.0.3.0.8.3.2.3.X.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1_IBT1120221120220000000000202211V92023000117430001098101X991020200000030700SB7400070320180007032018BBOV040067708900677089DE')
Value of type is xxx_xxx_ODATA_SRV.xxx_xxx_ODATAResult
Value of ID is V2.124.122.0_1.2.6.6.1.2.1.1.1.2.1.1:6.6.8.8.3.2.1.2.2.4.3.10.4.10.3.10.3.2.4.7.3.0.3.0.8.3.2.3.X.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1_IBT1120221120220000000000202211V92023000117430001098101X991020200000030700SB7400070320180007032018BBOV040067708900677089DE
Value TotaledProperties is empty
Value of C1CALMONTH is 202211
Value of C1CALMONTH_T is 11.2022
Value of C1FISCYEAR is V9/2023
Value of YCUST_SAL__YYRB1 is 00000000
Value of YCUST_SAL__YYRB1_T is Laura Jones
Value of YCUST_SAL__YYRB2 is 11111111
Value of YCUST_SAL__YYRB2_T is Peter Hanks
Value YCUST_SAL__YYRB3 is empty
Value YCUST_SAL__YYRB3_T is empty
Value of C1G_CWWSBU is 01
Value of C1G_CWWSBU_T is Automotive
...
Value C10733DALXCD8PAUPE76VNHMOK_E is empty
Value of C10733DALXCD8X0MFZ4KUKPWFB is 0
Value C10733DALXCD8X0MFZ4KUKPWFB_F is empty
Value C10733DALXCD8X0MFZ4KUKPWFB_E is empty
Skipping structure tag "Parameters":{
Skipping structure tag "__deferred":{
Value of uri is http://main.xxxx.org/sap/opu/odata/sap/xxx_xxx_ODATA_SRV/xxx_xxx_ODATAResults('V2.124.122.0_1.2.6.6.1.2.1.1.1.2.1.1%3A6.6.8.8.3.2.1.2.2.4.3.10.4.10.3.10.3.2.4.7.3.0.3.0.8.3.2.3.X.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1_IBT1120221120220000000000202211V92023000117430001098101X991020200000030700SB7400070320180007032018BBOV040067708900677089DE')/Parameters
Read 87 objects in 354 ms
Program terminated (StartMessageLoop was not called).

 

[Yafuhenk]

Marked as 'SOLVED"