How to deserialize BIG JSON data into DataGridView

Question

I´d like to deserialize a big JSON File (~600MB) into DataGridView without facing "maxJsonLength" Error.

My Code is working fine for small JSON files. But for bigger JSON files I get the "maxJsonLength" Error. Is there a workaround or an easy way to solve this problem since I am not the most experienced coder?

Private Sub BtnOpenFile_Click(sender As Object, e As EventArgs) Handles BtnOpenFileOld.Click
        OpenFileDialog1.InitialDirectory = "C:\"

        If OpenFileDialog1.ShowDialog = DialogResult.Cancel Then
        End If

        Dim JSonFilePath As String = File.ReadAllText(OpenFileDialog1.FileName)
        LblFilePath.Text = OpenFileDialog1.FileName

        DataGridView1.Rows.Clear()
        DataGridView1.Refresh()

        Dim dict As Object = New JavaScriptSerializer().Deserialize(Of List(Of Object))(JSonFilePath)

        For Each item As Object In dict
            DataGridView1.Rows.Add(item("EMail").ToString, item("Timestamp").ToString, item("Number").ToString)
        Next

    End Sub

My JSON File looks like

[
  {
    "EMail": "one@mail.com",
    "Timestamp": "2019-05-25T21:24:06.799381+02:00",
    "Number": 206074,
    "randomtrash1": "notneeded",
    "randomtrash2": "notneeded",
    "randomtrash3": "notneeded",
    "randomtrash4": "notneeded",
    "randomtrash5": "notneeded",
    "randomtrash6": "notneeded",
    "randomtrash7": "notneeded",
    "randomtrash8": "notneeded",
    "randomtrash9": "notneeded"
  },
  {
    "EMail": "two@mail.com",
    "Timestamp": "2019-05-25T21:24:06.8273826+02:00",
    "Number": 7397,
    "randomtrash1": "notneeded",
    "randomtrash2": "notneeded",
    "randomtrash3": "notneeded",
    "randomtrash4": "notneeded",
    "randomtrash5": "notneeded",
    "randomtrash6": "notneeded",
    "randomtrash7": "notneeded",
    "randomtrash8": "notneeded",
    "randomtrash9": "notneeded",
    "randomtrash10": "notneeded",
    "randomtrash11": "notneeded",
    "randomtrash12": "notneeded",
    "randomtrash13": "notneeded",
    "randomtrash14": "notneeded",
    "randomtrash15": "notneeded",
    "randomtrash16": "notneeded",
    "randomtrash17": "notneeded",
    "randomtrash18": "notneeded",
    "randomtrash19": "notneeded",
    "randomtrash20": "notneeded",
    "randomtrash21": "notneeded"
  }
]

Why are you using `JavaScriptSerializer`? Why not [tag:json.net] or even `DataContractJsonSerializer`? — dbc, May 26 '19 at 17:26
If you are certain you want to use `JavaScriptSerializer` then this is a duplicate of [The length of the string exceeds the value set on the maxJsonLength property](https://stackoverflow.com/a/15647158), however there are better solutions that will allow you to stream directly from your file without pre-loading into a 600-1200 MB string. — dbc, May 26 '19 at 17:29
because I don`t know how to work with json.net all examples I found about json.net have been in c#. So "JavaScriptSerializer" seemed for me the easiest way and it worked pretty good till I reached the maxJsonLength — fwalser, May 26 '19 at 17:31
`all examples I found about json.net have been in c#` FYI you can always convert those examples into VB.NET, at [this web site](http://converter.telerik.com/). — InteXX, May 26 '19 at 17:54

dbc · Accepted Answer · 2019-05-27T02:07:51.297

A minimal fix to avoid the maxJsonLength error is to set JavaScriptSerializer.MaxJsonLength = int.MaxValue as shown in this answer to The length of the string exceeds the value set on the maxJsonLength property by Taha Rehman Siddiqui:

Dim serializer As JavaScriptSerializer = New JavaScriptSerializer()
serializer.MaxJsonLength = Int32.MaxValue
Dim dict As Object = serializer.Deserialize(Of List(Of Object))(JSonFilePath)

However, you are currently loading a 600MB file into a 600-1,200MB string which is approaching the .Net maximum string length. If you file gets somewhat larger, you will start to get out-of-memory exceptions. A better approach would be to deserialize directly from the file using a streaming solution, however JavaScriptSerializer does not support streaming. Thus I'd recommend switching to json.net, which does.

First, install Json.NET as shown here. Next, since you don't seem to want to define an explicit data model for your JSON, create the following static method:

Public Module JsonExtensions
    Public Function LoadAnonymousType(Of T)(ByVal path as String, ByVal anonymousTypeObject as T, Optional ByVal settings as JsonSerializerSettings = Nothing) as T
        Using streamReader As TextReader = File.OpenText(path)
            Dim serializer = JsonSerializer.CreateDefault(settings)
            return CType(serializer.Deserialize(streamreader, GetType(T)), T)
        End Using
    End Function
End Module

What this does is to deserialize the contents of the file whose location is specified by the path argument into a data model of the type specified by the anonymousTypeObject argument.

Now you will be able to deserialize your JSON directly from the file to an array of of anonymous type objects as follows:

Dim array = JsonExtensions.LoadAnonymousType( _
    FileName, _
    { New With {.Email = CType(Nothing, String), .Timestamp = CType(Nothing, String), .Number = CType(Nothing, Long) }} _
)
For Each item In array
    ' DataGridView1.Rows.Add(item("EMail").ToString, item("Timestamp").ToString, item("Number").ToString)
    Console.WriteLine("Email = {0}, Timestamp = {1}, Number = {2}", item.Email, item.Timestamp, item.Number)
Next

Alternatively, you could create an explicit data model as follows:

Public Class RootObject
    Public Property Email As String
    Public Property Timestamp As String
    Public Property Number As Long
End Class

And deserialize as shown in Deserialize JSON from a file:

Dim list As List(Of RootObject) = Nothing
Using streamReader As TextReader = File.OpenText(FileName)
    list = CType(JsonSerializer.CreateDefault().Deserialize(streamReader, GetType(List(Of RootObject))), List(Of RootObject))
End Using
For Each item In list
    ' DataGridView1.Rows.Add(item("EMail").ToString, item("Timestamp").ToString, item("Number").ToString)
    Console.WriteLine("Email = {0}, Timestamp = {1}, Number = {2}", item.Email, item.Timestamp, item.Number)
Next

Demo fiddle here.

score 0 · Answer 2 · answered May 26 '19 at 18:42

Imports Newtonsoft.Json

Public Class Form1

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    Dim str As String = _
            "[" + _
            "  {" + _
            "    ""EMail"": ""one@mail.com""," + _
            "    ""Timestamp"": ""2019-05-25T21:24:06.799381+02:00""," + _
            "    ""Number"": 206074," + _
            "  }," + _
            "  {" + _
            "    ""EMail"": ""two@mail.com""," + _
            "    ""Timestamp"": ""2019-05-25T21:24:06.8273826+02:00""," + _
            "    ""Number"": 7397," + _
            "  }," + _
            "]"

    Try
        Dim list As List(Of jsnn) = JsonConvert.DeserializeObject(Of List(Of jsnn))(str)

        For Each item As jsnn In list
            Console.WriteLine(item.EMail & ": " & item.Timestamp & ": " & item.Number)
        Next

    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

End Sub

End Class

Public Class jsnn
    Public Property EMail As String
    Public Property Timestamp As DateTime
    Public Property Number As Integer
End Class

here's a screenshot

How to deserialize BIG JSON data into DataGridView

2 Answers2