search

VBA-tools / VBA-JSON

JSON conversion and parsing for VBA
MIT License
1.76k stars 568 forks source link

Out of String Space : ConvertToJson Function #203

Open PratikVasagadekar opened 3 years ago

PratikVasagadekar commented 3 years ago

I am trying to Build a JSON file around my VBA Project, but I am getting the Out of String Space error when trying to convert my strings to JSON ConvertToJson(JSONCollItems, Whitespace:=3)

It does appear when I am trying to ConvertToJson on huge Multiline strings.

After debugging, the error is stuck at 

Private Function json_BufferToString(ByRef json_Buffer As String, ByVal json_BufferPosition As Long) As String
    If json_BufferPosition > 0 Then
        json_BufferToString = VBA.Left$(json_Buffer, json_BufferPosition) 
    End If
End Function
Value of json_BufferPosition = 10770005
Value of json_Buffer = Out of Memory 
Value of json_BufferLength = 12386304

Could you provide any suggestion to getting around this?

houghtonap commented 3 years ago

Honestly, VBA-JSON is probably not the correct tool to be using if you have JSON that is 10MB in size.

  1. You have a 12MB buffer of which you want 10MB of data. Because of how VBA.Left works that means your in-memory working size for just those two strings is 22MB!
  2. When VBA-JSON serializes that 10MB JSON, returned by VBA.Left, it will create numerous objects and those objects will more than likely increase the in-memory working size by double, if not triple, on top of the 22MB you already are using by those two string.
  3. You have pushed VBA-JSON and the VBA environment beyond their limitations to have any hope of a performant solution, even if you could workaround the issue. The VBA environment just cannot hold all that data in-memory along with the Office application you are using to host VBA-JSON.

Since you didn't provide much information about what you are trying to accomplish, it is suggest you reconsider what you are trying to accomplish and/or use a different tool for your purpose. MySQL or another database that handles JSON data might be a better fit since you can slice the JSON up and dump it into tables, where you could import the table(s) into Excel for possible further analysis.