Parse JSON with Excel VBA

Ferdi24 · Jun 14, 2012

Hi Experts,

I'm trying to parse a JSON string in Excel VBA into an array so that I can write it in table-format into a sheet.

Example: I have this JSON string in cell A1 "rows":[["20120604", "ABC", "89"],["20120604", "BCD", "120"],["20120604", "CDE","239"]]

The final result in my sheet should look like this:

20120604 | ABC | 89
20120604 | BCD | 20
20120604 | CDE | 239

The format of the content inbetween the [ ] could be anything.

Example 1:
"rows":[["20120604","122"],["20120604","239"],["20120604","150"]]
Example2:
"rows":[["ABC","34"],["BCD","111"],["CDE","459"]]
Example3:
"rows":[["20120604", "ABC", "89"],["20120604", "BCD", "120"],["20120604", "CDE","239"]]
Example4:
"rows":[["XYZ", "ABC", "89","15"],["WXY", "BCD", "120","11"],["VWX", "CDE","239","19"]]

Does anyone have any ideas and would be willing to share his/her wisdom ith me?
Thanks a lot for reading

billgeo · Jul 30, 2014

For what to replace the "SOMETHING" with, below is a sample JSON file I was sent that I'll eventually be processing.
I'm going to guess that "EventResults" is what is placed there. Pure guess tho.

Thanks for your assistance with this!

Code:

{
  "database": {
    "-name": "r2sports",
    "table_structure": [      
      {
        "-name": "finalPlaces",
        "field": [
          {
            "-field": "TID",
            "-TYPE": "int",
            "-null": "YES",
            "-default": "null"
          },
          {
            "-field": "divID",
            "-TYPE": "int",
            "-null": "YES",
            "-default": "null"
          },        
          {
            "-field": "combinedID",
            "-TYPE": "int",
            "-null": "YES",
            "-default": "null"
          },
          {
            "-field": "drawType",
            "-TYPE": "text",
            "-null": "YES",
            "-default": "null"
          },    
          {
            "-field": "1stID",
            "-TYPE": "int",
            "-null": "YES",
            "-default": "null"
          },
          {
            "-field": "2ndID",
            "-TYPE": "int",
            "-null": "YES",
            "-default": "null"
          },
          {
            "-field": "3rdID",
            "-TYPE": "text",
            "-null": "NO",
            "-key": "PRI",
            "-default": "null"
          },
          {
            "-field": "4thID",
            "-TYPE": "int",
            "-null": "YES",
            "-default": "null"
          },
          {
            "-field": "5thID",
            "-TYPE": "int",
            "-null": "YES",
            "-default": "null"
          },
          {
            "-field": "6thID",
            "-TYPE": "int",
            "-null": "YES",
            "-default": "null"
          },
          {
            "-field": "7thID",
            "-TYPE": "int",
            "-null": "YES",
            "-default": "null"
          },
          {
            "-field": "8thID",
            "-TYPE": "int",
            "-null": "YES",
            "-default": "null"
          },
          {
            "-field": "9thID",
            "-TYPE": "int",
            "-null": "YES",
            "-default": "null"
          },
          {
            "-field": "10thID",
            "-TYPE": "int",
            "-null": "YES",
            "-default": "null"
          },
          {
            "-field": "11thID",
            "-TYPE": "int",
            "-null": "YES",
            "-default": "null"
          },
          {
            "-field": "12thID",
            "-TYPE": "int",
            "-null": "YES",
            "-default": "null"
          },
          {
            "-field": "13thID",
            "-TYPE": "int",
            "-null": "YES",
            "-default": "null"
          },
          {
            "-field": "14thID",
            "-TYPE": "int",
            "-null": "YES",
            "-default": "null"
          },
          {
            "-field": "15thID",
            "-TYPE": "int",
            "-null": "YES",
            "-default": "null"
          },                                            
          {
            "-field": "16thID",
            "-TYPE": "int",
            "-null": "YES",
            "-default": "null"
          }
        ]
      }
    ],
  "table_data": [
  {
"-name": "finalPlaces",
"row": [
  {
  "field": [
  {
    "-name": "TID",
    "#int": "12556"
  },
  {
    "-name": "divID",
    "#int": ""
  },
  {
    "-name": "combinedID",
    "#int": "87827"
  },
  {
    "-name": "drawType",
    "#test": "Pool Play Group"
  },
  {
    "-name": "1stID",
    "#int": "0"
  },
  {
    "-name": "2ndID",
    "#int": "0"
  },
  {
    "-name": "3rdID",
    "#int": "0"
  },
  {
    "-name": "4thID",
    "#int": "0"
  },
  {
    "-name": "5thID",
    "#int": ""
  },
  {
    "-name": "6thID",
    "#int": ""
  },
  {
    "-name": "7thID",
    "#int": ""
  },
  {
    "-name": "8thID",
    "#int": ""
  },
  {
    "-name": "9thID",
    "#int": ""
  },
  {
    "-name": "10thID",
    "#int": ""
  },
  {
    "-name": "11thID",
    "#int": ""
  },
  {
    "-name": "12thID",
    "#int": ""
  },
  {
    "-name": "13thID",
    "#int": ""
  },
  {
    "-name": "14thID",
    "#int": ""
  },
  {
    "-name": "15thID",
    "#int": ""
  },
  {
    "-name": "16thID",
    "#int": ""
  }
]
}
]
}
,
{
"-name": "finalPlaces",
"row": [
  {
  "field": [
  {
    "-name": "TID",
    "#int": "12556"
  },
  {
    "-name": "divID",
    "#int": ""
  },
  {
    "-name": "combinedID",
    "#int": "88011"
  },
  {
    "-name": "drawType",
    "#test": "Pool Play Group"
  },
  {
    "-name": "1stID",
    "#int": "0"
  },
  {
    "-name": "2ndID",
    "#int": "0"
  },
  {
    "-name": "3rdID",
    "#int": "0"
  },
  {
    "-name": "4thID",
    "#int": "0"
  },
  {
    "-name": "5thID",
    "#int": ""
  },
  {
    "-name": "6thID",
    "#int": ""
  },
  {
    "-name": "7thID",
    "#int": ""
  },
  {
    "-name": "8thID",
    "#int": ""
  },
  {
    "-name": "9thID",
    "#int": ""
  },
  {
    "-name": "10thID",
    "#int": ""
  },
  {
    "-name": "11thID",
    "#int": ""
  },
  {
    "-name": "12thID",
    "#int": ""
  },
  {
    "-name": "13thID",
    "#int": ""
  },
  {
    "-name": "14thID",
    "#int": ""
  },
  {
    "-name": "15thID",
    "#int": ""
  },
  {
    "-name": "16thID",
    "#int": ""
  }
]
}
]
}
,
{
"-name": "finalPlaces",
"row": [
  {
  "field": [
  {
    "-name": "TID",
    "#int": "12556"
  },
  {
    "-name": "divID",
    "#int": ""
  },
  {
    "-name": "combinedID",
    "#int": "88067"
  },
  {
    "-name": "drawType",
    "#test": "Pool Play Group"
  },
  {
    "-name": "1stID",
    "#int": ""
  },
  {
    "-name": "2ndID",
    "#int": ""
  },
  {
    "-name": "3rdID",
    "#int": ""
  },
  {
    "-name": "4thID",
    "#int": ""
  },
  {
    "-name": "5thID",
    "#int": ""
  },
  {
    "-name": "6thID",
    "#int": ""
  },
  {
    "-name": "7thID",
    "#int": ""
  },
  {
    "-name": "8thID",
    "#int": ""
  },
  {
    "-name": "9thID",
    "#int": ""
  },
  {
    "-name": "10thID",
    "#int": ""
  },
  {
    "-name": "11thID",
    "#int": ""
  },
  {
    "-name": "12thID",
    "#int": ""
  },
  {
    "-name": "13thID",
    "#int": ""
  },
  {
    "-name": "14thID",
    "#int": ""
  },
  {
    "-name": "15thID",
    "#int": ""
  },
  {
    "-name": "16thID",
    "#int": ""
  }
]
}
]
}
,
{
"-name": "finalPlaces",
"row": [
  {
  "field": [
  {
    "-name": "TID",
    "#int": "12556"
  },
  {
    "-name": "divID",
    "#int": ""
  },
  {
    "-name": "combinedID",
    "#int": "88068"
  },
  {
    "-name": "drawType",
    "#test": "Pool Play Group"
  },
  {
    "-name": "1stID",
    "#int": ""
  },
  {
    "-name": "2ndID",
    "#int": ""
  },
  {
    "-name": "3rdID",
    "#int": ""
  },
  {
    "-name": "4thID",
    "#int": ""
  },
  {
    "-name": "5thID",
    "#int": ""
  },
  {
    "-name": "6thID",
    "#int": ""
  },
  {
    "-name": "7thID",
    "#int": ""
  },
  {
    "-name": "8thID",
    "#int": ""
  },
  {
    "-name": "9thID",
    "#int": ""
  },
  {
    "-name": "10thID",
    "#int": ""
  },
  {
    "-name": "11thID",
    "#int": ""
  },
  {
    "-name": "12thID",
    "#int": ""
  },
  {
    "-name": "13thID",
    "#int": ""
  },
  {
    "-name": "14thID",
    "#int": ""
  },
  {
    "-name": "15thID",
    "#int": ""
  },
  {
    "-name": "16thID",
    "#int": ""
  }
]
}
]
}
,
{
"-name": "finalPlaces",
"row": [
  {
  "field": [
  {
    "-name": "TID",
    "#int": "12556"
  },
  {
    "-name": "divID",
    "#int": ""
  },
  {
    "-name": "combinedID",
    "#int": "88069"
  },
  {
    "-name": "drawType",
    "#test": "Pool Play Group"
  },
  {
    "-name": "1stID",
    "#int": ""
  },
  {
    "-name": "2ndID",
    "#int": ""
  },
  {
    "-name": "3rdID",
    "#int": ""
  },
  {
    "-name": "4thID",
    "#int": ""
  },
  {
    "-name": "5thID",
    "#int": ""
  },
  {
    "-name": "6thID",
    "#int": ""
  },
  {
    "-name": "7thID",
    "#int": ""
  },
  {
    "-name": "8thID",
    "#int": ""
  },
  {
    "-name": "9thID",
    "#int": ""
  },
  {
    "-name": "10thID",
    "#int": ""
  },
  {
    "-name": "11thID",
    "#int": ""
  },
  {
    "-name": "12thID",
    "#int": ""
  },
  {
    "-name": "13thID",
    "#int": ""
  },
  {
    "-name": "14thID",
    "#int": ""
  },
  {
    "-name": "15thID",
    "#int": ""
  },
  {
    "-name": "16thID",
    "#int": ""
  }
]
}
]
}
,
{
"-name": "finalPlaces",
"row": [
  {
  "field": [
  {
    "-name": "TID",
    "#int": "12556"
  },
  {
    "-name": "divID",
    "#int": ""
  },
  {
    "-name": "combinedID",
    "#int": "88844"
  },
  {
    "-name": "drawType",
    "#test": "Pool Play Group"
  },
  {
    "-name": "1stID",
    "#int": "75"
  },
  {
    "-name": "2ndID",
    "#int": "190"
  },
  {
    "-name": "3rdID",
    "#int": "112"
  },
  {
    "-name": "4thID",
    "#int": "105"
  },
  {
    "-name": "5thID",
    "#int": ""
  },
  {
    "-name": "6thID",
    "#int": ""
  },
  {
    "-name": "7thID",
    "#int": ""
  },
  {
    "-name": "8thID",
    "#int": ""
  },
  {
    "-name": "9thID",
    "#int": ""
  },
  {
    "-name": "10thID",
    "#int": ""
  },
  {
    "-name": "11thID",
    "#int": ""
  },
  {
    "-name": "12thID",
    "#int": ""
  },
  {
    "-name": "13thID",
    "#int": ""
  },
  {
    "-name": "14thID",
    "#int": ""
  },
  {
    "-name": "15thID",
    "#int": ""
  },
  {
    "-name": "16thID",
    "#int": ""
  }
]
}
]
}
,
{
"-name": "finalPlaces",
"row": [
  {
  "field": [
  {
    "-name": "TID",
    "#int": "12556"
  },
  {
    "-name": "divID",
    "#int": ""
  },
  {
    "-name": "combinedID",
    "#int": "88845"
  },
  {
    "-name": "drawType",
    "#test": "Pool Play Group"
  },
  {
    "-name": "1stID",
    "#int": "189"
  },
  {
    "-name": "2ndID",
    "#int": "111"
  },
  {
    "-name": "3rdID",
    "#int": "76"
  },
  {
    "-name": "4thID",
    "#int": "106"
  },
  {
    "-name": "5thID",
    "#int": ""
  },
  {
    "-name": "6thID",
    "#int": ""
  },
  {
    "-name": "7thID",
    "#int": ""
  },
  {
    "-name": "8thID",
    "#int": ""
  },
  {
    "-name": "9thID",
    "#int": ""
  },
  {
    "-name": "10thID",
    "#int": ""
  },
  {
    "-name": "11thID",
    "#int": ""
  },
  {
    "-name": "12thID",
    "#int": ""
  },
  {
    "-name": "13thID",
    "#int": ""
  },
  {
    "-name": "14thID",
    "#int": ""
  },
  {
    "-name": "15thID",
    "#int": ""
  },
  {
    "-name": "16thID",
    "#int": ""
  }
]
}
]
}
,
{
"-name": "finalPlaces",
"row": [
  {
  "field": [
  {
    "-name": "TID",
    "#int": "12556"
  },
  {
    "-name": "divID",
    "#int": ""
  },
  {
    "-name": "combinedID",
    "#int": "88846"
  },
  {
    "-name": "drawType",
    "#test": "Pool Play Group"
  },
  {
    "-name": "1stID",
    "#int": "133"
  },
  {
    "-name": "2ndID",
    "#int": "134"
  },
  {
    "-name": "3rdID",
    "#int": "155"
  },
  {
    "-name": "4thID",
    "#int": "156"
  },
  {
    "-name": "5thID",
    "#int": ""
  },
  {
    "-name": "6thID",
    "#int": ""
  },
  {
    "-name": "7thID",
    "#int": ""
  },
  {
    "-name": "8thID",
    "#int": ""
  },
  {
    "-name": "9thID",
    "#int": ""
  },
  {
    "-name": "10thID",
    "#int": ""
  },
  {
    "-name": "11thID",
    "#int": ""
  },
  {
    "-name": "12thID",
    "#int": ""
  },
  {
    "-name": "13thID",
    "#int": ""
  },
  {
    "-name": "14thID",
    "#int": ""
  },
  {
    "-name": "15thID",
    "#int": ""
  },
  {
    "-name": "16thID",
    "#int": ""
  }
]
}
]
}

    ]
  }
}

billgeo · Jul 30, 2014

Also, regarding the below;
What is the RestLibrary? That is, what should "Load JSON File" be replaces with?
I'm getting this error: "Load JSON File is not a known library entry".

' Pass the JSON String "testString" to be processed and populate Workbook Sheet "Sheet1",
using the RestLibrary called "Load JSON File"
'
restQuery("Sheet1", "Load JSON File", , , , , , , , , True, testString).tearDown

Bill

billgeo · Jul 30, 2014

OK, I've resolved the above "RestLibrary" issue.

ronle · Feb 7, 2015

For what's it worth and if people are still looking for a simplified way to digest Json into tabular view, I have created a <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(87, 65, 35);">VBA</acronym> script (with the help of other code pieces online).
This script creates a treeview representation of the Json file allowing to select the keywords upon which a new line would be created in the table.
Excel macro is hosted here - https://github.com/ronle/JsonToExcel

Mr_Ekshin · Feb 12, 2015

Here's the easiest solution of them all.

I was frustrated trying to find a JSON>XL solution. Parsers that COULD find, either didn't work, or I couldn't understand how to make them work.

OR they wanted me to update my excel (I don't want to put out files others can't easily run), or include extra references, or whatnot.

So I wrote my own. It's simple. You don't download anything. You don't include extra references. You just make it run. It's primative, ugly, disgusting code...

AND IT WORKS. It solved my problem, and parses all examples in this thread except the last (because my parser is not designed to handle carriage returns).

It handles nested JSON. It handles my project file which was 7.8k in size so it should work fine for everything else.

There are variables you can set to format the output a little.

Simply have your JSON file in a cell, edit the script to know where that cell is, and what cell to output to at the beginning, and run it. NOTE: have this all happen on a new sheet. After your data is splattered on a sheet, you can then use excel to do what you want with it.

And if you want to make the code TINY, just get rid of the remark statements (though you may miss out on seeing my frustrated mind at work).

... and if someone wants to rewrite this using better programming technique, have at. Here are some things to to keep in mind.
1. Make it slicker & faster, not bigger & slower.
2. Redundant code takes up more memory and makes structured programmers wince over their quiche, but it's FASTER. This is tiny enough to not require converting to FORTH architecture multiple subs.

Code:

Option Explicit

Sub n2XlJSONParser()
' This will handle nested JSON.
' This will work with Excel 2003(sp3) without extra references or inporting any extra sh*t.
' This will propagate data horizontally, _
' _ and then propagate new nest data on new row.
' You can set the input cell, output starting position, _
' _ and limit number of rows before a new output line.
' There are numerous MsgBox's that you can uncomment to single-step through the process.

Dim Src As String       ' reference Source as string value
Dim ws As Worksheet     ' Target worksheet for extracted data
Dim TLen As Integer     ' Total length of of input cell data
Dim columnsCR As Integer ' limit number of data pairs before forced Carriage Return
Dim cCR As Integer      ' Runtime modified carriage return value for relative reference from start.
Dim lenLim As Integer   ' Data lenth limit to cause a new row.  0 = none
Dim rStart As Integer   ' Starting output row
Dim cStart As Integer   ' Starting output column
Dim rOut As Integer     ' Dest Row for output
Dim cOut As Integer     ' Dest Col for output
Dim nDex As Integer     ' Current position in Source string
Dim nDex2 As Integer    ' further index to find end quote
Dim nDexChr As String   ' The character at nDex
Dim n2This As String    ' value within from nDex -> nDex2 range
Dim records As Integer  ' Total number of records to report parsed.

' Propagation rules:
' A brace "{" indents the table 1 column.
' An end brace "}" un-indents the table (is that a word?).
' Quotes are partial delimiters to find a string value to extract by looking ahead to the next quote.
' colons and commas are minor delimiters; a comma looks back to the previous colon.
' "}," is a major delimiter, and causes the extraction to start a new row.

' First assign the source
    Set ws = ActiveWorkbook.Worksheets(1)
    Src = ws.Range("A4").Value  ' Cell that contains JSON data to parse.
    TLen = Len(Src)
    records = 0
' MsgBox "Total Length = " & TLen
    
' Initialize cell output behavior.  Adjust these to suit.
    rStart = 6          ' Start output in row 6
    cStart = 1          ' Start output in "A" column
    columnsCR = 0       ' Number of columns, after which, force the start of a new row. _
                        ' _ A ZERO value turns this off. (even numbers work best)
    lenLim = 0          ' If string longer than this, start new row.  A ZERO value turns this off.
                        '...if you use lenLim, variable record lengths can break table structure.

' Set up assorted variables
    rOut = rStart
    cOut = cStart
    Sheet1.Cells(rStart, cStart).Activate ' This is the starting output cell.
    cStart = cStart - 1 ' Pay no attention to the man behind the curtain!
    
' Initialize nDex
' The nDex crawls through the Source, leaving nDex2 behind.  When it finds a delimiter, it grabs _
' what is in between the two NDex counters.  Then it brings up nDex2 and continues.  This is like _
' an inch-worm process through the source data.  It's smart enough to know to look for the next _
' quote after finding a quote.  This way it won't have to check EVERY character of data.
' A future version will increase speed dramatically by looking ahead for next delimiter.
    nDex = 1
    nDex2 = nDex
    
    ' MAIN LOOP (Rockin' the Do-loops old skool.)
    Do While nDex <= TLen
        nDexChr = Mid(Src, nDex, 1) ' Get the character sitting at the nDex location
' MsgBox "nDexChr = " & nDexChr
    
        ' Find what's at the nDex location
        If nDexChr = """" Then  ' Is it a quote?
            nDex2 = nDex        ' Bring up the tail
' MsgBox "First quote found at " & nDex
            nDex = InStr(nDex2 + 1, Src, """")  ' Find the next quote
' MsgBox "Next quote found at " & nDex
            n2This = Mid(Src, nDex2 + 1, (nDex - nDex2) - 1)    'Get what is in the quotes
' MsgBox "n2This = " & n2This
            ws.Cells(rOut, cOut) = n2This ' Dump the first found data here and now.
            records = records + 1
            nDex = nDex + 1 ' increment the inchworm
            nDex2 = nDex    ' ...and bring up the tail
            cOut = cOut + 1
                            
        ElseIf nDexChr = "," Then ' Is it a comma?
            If nDex2 <> nDex Then ' Should we look back?
                n2This = Mid(Src, nDex2 + 1, (nDex - nDex2) - 1)    ' Get what's between the head and tail.
' MsgBox "Comma found." & vbNewLine & "Looking back to colon finds """ & n2This & """"
                ws.Cells(rOut, cOut) = n2This   ' Dump data into cell
                records = records + 1
                cOut = cOut + 1 ' next output cell in a new column
            End If
            nDex = nDex + 1
            nDex2 = nDex
            ' encountered comma now see if we should start new line.
            If (cOut >= cCR And columnsCR > 0) Or (Len(n2This) > lenLim And lenLim <> 0) Then
                rOut = rOut + 1
                cOut = cStart
                cCR = cStart + columnsCR
            End If
                       
        ElseIf nDexChr = "}" Then
            cStart = cStart - 1
            cCR = cStart + columnsCR
            If nDex <> nDex2 Then   ' didn't start here so look back.
                n2This = Mid(Src, nDex2 + 1, (nDex - nDex2) - 1)
' MsgBox "End brace found." & vbNewLine & vbNewLine & _
' "Looking back to colon finds """ & n2This & """"
                ws.Cells(rOut, cOut) = n2This
                records = records + 1
            End If
            nDex = nDex + 1
            nDex2 = nDex
           
' MsgBox "Checking for trailing comma: " & vbNewLine & vbNewLine & _
' "nDex = " & nDex & vbNewLine & "Pos of next comma = " & InStr(nDex, Src, ",")
            If InStr(nDex, Src, ",") = nDex Then    ' is this character a comma?
' MsgBox "End brace followed by comma found." & _
' vbNewLine & vbNewLine & "Starting new row."
                rOut = rOut + 1
                cOut = cStart
            Else:
                cOut = cOut + 1
            End If
            nDex = nDex + 1
            
        ElseIf nDexChr = "{" Then
            cStart = cStart + 1
            If cOut < cStart Then
                cOut = cStart
            End If
            cCR = cStart + columnsCR
            nDex = nDex + 1
' MsgBox "cStart = " & cStart & vbNewLine & "cOut = " & cOut
        
        Else:
            nDex = nDex + 1
        End If
                
        Loop
        MsgBox records & " total records were parsed."
    
End Sub

mariusdr · Jun 12, 2015

Mr_Ekshin said:

Here's the easiest solution of them all.

I was frustrated trying to find a JSON>XL solution. Parsers that COULD find, either didn't work, or I couldn't understand how to make them work.

OR they wanted me to update my excel (I don't want to put out files others can't easily run), or include extra references, or whatnot.

So I wrote my own. It's simple. You don't download anything. You don't include extra references. You just make it run. It's primative, ugly, disgusting code...

AND IT WORKS. It solved my problem, and parses all examples in this thread except the last (because my parser is not designed to handle carriage returns).

It handles nested JSON. It handles my project file which was 7.8k in size so it should work fine for everything else.

There are variables you can set to format the output a little.

Simply have your JSON file in a cell, edit the script to know where that cell is, and what cell to output to at the beginning, and run it. NOTE: have this all happen on a new sheet. After your data is splattered on a sheet, you can then use excel to do what you want with it.

And if you want to make the code TINY, just get rid of the remark statements (though you may miss out on seeing my frustrated mind at work).

... and if someone wants to rewrite this using better programming technique, have at. Here are some things to to keep in mind.
1. Make it slicker & faster, not bigger & slower.
2. Redundant code takes up more memory and makes structured programmers wince over their quiche, but it's FASTER. This is tiny enough to not require converting to FORTH architecture multiple subs.

Code:

Option Explicit

Sub n2XlJSONParser()
' This will handle nested JSON.
' This will work with Excel 2003(sp3) without extra references or inporting any extra sh*t.
' This will propagate data horizontally, _
' _ and then propagate new nest data on new row.
' You can set the input cell, output starting position, _
' _ and limit number of rows before a new output line.
' There are numerous MsgBox's that you can uncomment to single-step through the process.

Dim Src As String       ' reference Source as string value
Dim ws As Worksheet     ' Target worksheet for extracted data
Dim TLen As Integer     ' Total length of of input cell data
Dim columnsCR As Integer ' limit number of data pairs before forced Carriage Return
Dim cCR As Integer      ' Runtime modified carriage return value for relative reference from start.
Dim lenLim As Integer   ' Data lenth limit to cause a new row.  0 = none
Dim rStart As Integer   ' Starting output row
Dim cStart As Integer   ' Starting output column
Dim rOut As Integer     ' Dest Row for output
Dim cOut As Integer     ' Dest Col for output
Dim nDex As Integer     ' Current position in Source string
Dim nDex2 As Integer    ' further index to find end quote
Dim nDexChr As String   ' The character at nDex
Dim n2This As String    ' value within from nDex -> nDex2 range
Dim records As Integer  ' Total number of records to report parsed.

' Propagation rules:
' A brace "{" indents the table 1 column.
' An end brace "}" un-indents the table (is that a word?).
' Quotes are partial delimiters to find a string value to extract by looking ahead to the next quote.
' colons and commas are minor delimiters; a comma looks back to the previous colon.
' "}," is a major delimiter, and causes the extraction to start a new row.

' First assign the source
    Set ws = ActiveWorkbook.Worksheets(1)
    Src = ws.Range("A4").Value  ' Cell that contains JSON data to parse.
    TLen = Len(Src)
    records = 0
' MsgBox "Total Length = " & TLen
    
' Initialize cell output behavior.  Adjust these to suit.
    rStart = 6          ' Start output in row 6
    cStart = 1          ' Start output in "A" column
    columnsCR = 0       ' Number of columns, after which, force the start of a new row. _
                        ' _ A ZERO value turns this off. (even numbers work best)
    lenLim = 0          ' If string longer than this, start new row.  A ZERO value turns this off.
                        '...if you use lenLim, variable record lengths can break table structure.

' Set up assorted variables
    rOut = rStart
    cOut = cStart
    Sheet1.Cells(rStart, cStart).Activate ' This is the starting output cell.
    cStart = cStart - 1 ' Pay no attention to the man behind the curtain!
    
' Initialize nDex
' The nDex crawls through the Source, leaving nDex2 behind.  When it finds a delimiter, it grabs _
' what is in between the two NDex counters.  Then it brings up nDex2 and continues.  This is like _
' an inch-worm process through the source data.  It's smart enough to know to look for the next _
' quote after finding a quote.  This way it won't have to check EVERY character of data.
' A future version will increase speed dramatically by looking ahead for next delimiter.
    nDex = 1
    nDex2 = nDex
    
    ' MAIN LOOP (Rockin' the Do-loops old skool.)
    Do While nDex <= TLen
        nDexChr = Mid(Src, nDex, 1) ' Get the character sitting at the nDex location
' MsgBox "nDexChr = " & nDexChr
    
        ' Find what's at the nDex location
        If nDexChr = """" Then  ' Is it a quote?
            nDex2 = nDex        ' Bring up the tail
' MsgBox "First quote found at " & nDex
            nDex = InStr(nDex2 + 1, Src, """")  ' Find the next quote
' MsgBox "Next quote found at " & nDex
            n2This = Mid(Src, nDex2 + 1, (nDex - nDex2) - 1)    'Get what is in the quotes
' MsgBox "n2This = " & n2This
            ws.Cells(rOut, cOut) = n2This ' Dump the first found data here and now.
            records = records + 1
            nDex = nDex + 1 ' increment the inchworm
            nDex2 = nDex    ' ...and bring up the tail
            cOut = cOut + 1
                            
        ElseIf nDexChr = "," Then ' Is it a comma?
            If nDex2 <> nDex Then ' Should we look back?
                n2This = Mid(Src, nDex2 + 1, (nDex - nDex2) - 1)    ' Get what's between the head and tail.
' MsgBox "Comma found." & vbNewLine & "Looking back to colon finds """ & n2This & """"
                ws.Cells(rOut, cOut) = n2This   ' Dump data into cell
                records = records + 1
                cOut = cOut + 1 ' next output cell in a new column
            End If
            nDex = nDex + 1
            nDex2 = nDex
            ' encountered comma now see if we should start new line.
            If (cOut >= cCR And columnsCR > 0) Or (Len(n2This) > lenLim And lenLim <> 0) Then
                rOut = rOut + 1
                cOut = cStart
                cCR = cStart + columnsCR
            End If
                       
        ElseIf nDexChr = "}" Then
            cStart = cStart - 1
            cCR = cStart + columnsCR
            If nDex <> nDex2 Then   ' didn't start here so look back.
                n2This = Mid(Src, nDex2 + 1, (nDex - nDex2) - 1)
' MsgBox "End brace found." & vbNewLine & vbNewLine & _
' "Looking back to colon finds """ & n2This & """"
                ws.Cells(rOut, cOut) = n2This
                records = records + 1
            End If
            nDex = nDex + 1
            nDex2 = nDex
           
' MsgBox "Checking for trailing comma: " & vbNewLine & vbNewLine & _
' "nDex = " & nDex & vbNewLine & "Pos of next comma = " & InStr(nDex, Src, ",")
            If InStr(nDex, Src, ",") = nDex Then    ' is this character a comma?
' MsgBox "End brace followed by comma found." & _
' vbNewLine & vbNewLine & "Starting new row."
                rOut = rOut + 1
                cOut = cStart
            Else:
                cOut = cOut + 1
            End If
            nDex = nDex + 1
            
        ElseIf nDexChr = "{" Then
            cStart = cStart + 1
            If cOut < cStart Then
                cOut = cStart
            End If
            cCR = cStart + columnsCR
            nDex = nDex + 1
' MsgBox "cStart = " & cStart & vbNewLine & "cOut = " & cOut
        
        Else:
            nDex = nDex + 1
        End If
                
        Loop
        MsgBox records & " total records were parsed."
    
End Sub

Thanks a lot! Great start for what I need to do!

Bob Sullivan · Sep 11, 2015

So I have a large .json file. How can I read the file, parse it and insert into a excel sheet?

vbollepalli · Apr 4, 2016

I have downloaded the ZIP file , But not sure how to use this.

Please guide me how to use it.

Thanks,
Naresh

Ferdi24 · Apr 4, 2016

Hi vbollepalli,
I have since switched to a different approach. Here's how it works for me now:

- Download and unzip the JsonConverter.bas file from https://github.com/VBA-tools/VBA-JSON
- In Excel open the VBA Editor (Alt+F11)
- Click 'File > Import file' and select the JsonConverter.bas
- Now go to 'Tools > References' and activate "Microsoft XML..." and "Microsoft Scripting Runtime"
That 's it really. Now you can write some code and use it like this:

Code:

Sub getGender()
Dim httpRequest As New MSXML2.XMLHTTP60
Dim jsonText As String, firstName As String
Dim jsonObject As Object


firstName = Application.InputBox(Prompt:="What is your name?", Default:=Environ("username"))


With httpRequest
    .Open "GET", "https://gender-api.com/get?name=" & firstName
    .send ("")
    jsonText = .responseText
End With


Set jsonobj = [URL="https://github.com/VBA-tools/VBA-JSON/blob/master/JsonConverter.bas"]JsonConverte[/URL]r.ParseJson(jsonText)


MsgBox "I am " & jsonobj("accuracy") & "% sure that you, " & _
StrConv(jsonobj("name"), vbProperCase) & ", are " & jsonobj("gender") & "."


End Sub

Hope that helps.

vbollepalli · Apr 5, 2016

Hi Fordie,

Thanks for your quick response.

My requirement is to convert JSON file into Excel file in a proper format.

Do you have any code available , Please share.

Parse JSON with Excel VBA

Ferdi24

Board Regular

billgeo

New Member

Excel Facts

billgeo

New Member

billgeo

New Member

ronle

New Member

Mr_Ekshin

New Member

mariusdr

New Member

Bob Sullivan

New Member

vbollepalli

New Member

Ferdi24

Board Regular

vbollepalli

New Member

Similar threads

Forum statistics

Share this page

Parse JSON with Excel VBA

Board Regular

New Member

Excel Facts

New Member

New Member

New Member

New Member

New Member

New Member

New Member

Board Regular

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock