JSON Parser – Convert JSON To CSV – Excel VBA Macro

Parse JSON – Converter in Excel

This name often reminds me of the film Jason Bourne. But this JSON is a file format standard.

In this page, You can get JSON converter with Excel VBA Code & sample JSON file. (In case, If you are looking for Python JSON Parser click this page which has a easy Python code )

Using this code, We will read data in JSON & parse the details to Excel , Txt or CSV file.

Parser – JSON To Excel – VBA Macro Code

Try this right away by creating a new Excel workbook. Save it in .xlsm format, press Alt + F11 to view the VB editor for Excel. Then follow these important preliminary steps.

Important steps before putting this JSON Parser to work:

1. Go to this Page & download the JsonConverter.bas
2. Include this .bas module in your VBA project.
3. In VB editor, goto Tool->References & enable “Microsoft Scripting Runtime” checkbox.

Now, copy paste this code to your VBA Project into a new module.

Sub Convert_JSON_To_CSV()
    Dim JSON As Object, jsonNode, jsonList, menuList, menuItem
    Dim jsonStr As String
    
    'Open JSON File
    fname = "D:\JSON2.txt"
    IFile = FreeFile
    Open fname For Input As #IFile
        jsonStr = Input$(LOF(IFile), IFile)
    Close IFile
    
    'Parse JSON to Dictionary
    Set JSON = ParseJson(jsonStr)
    
    'Read JSON Data from Dictionary
    Set jsonList = JSON("menu")
    'MsgBox jsonList("id")
    
    'To Write to CSV
    fname = "D:\JSON2CSV.CSV"
    IFile = FreeFile
    Open fname For Output As #IFile
        
    'Loop thru Each Node  in JSON
    Dim csvStr As String, csvStrHDR As String
    csvStrHDR = ""
    For Each jsonNode In jsonList.Keys
        If csvStrHDR = "" Then
            csvStrHDR = jsonNode
        Else
            csvStrHDR = csvStrHDR & "," & jsonNode
        End If
    Next
    Print #IFile, csvStrHDR
    
    csvStr = ""
    csvStrHDR = ""
    For Each jsonNode In jsonList.Items
        If VBA.TypeName(jsonNode) = "String" Then
            'MsgBox jsonNode
            If csvStrHDR = "" Then
                csvStrHDR = jsonNode
            Else
                csvStrHDR = csvStrHDR & "," & jsonNode
            End If
        Else
            If VBA.InStr(1, VBA.TypeName(jsonNode), "Dictionary", vbTextCompare) > 0 Then
                Set menuList = jsonList("popup")("menuitem")
                For Each menuItem In menuList
                    csvStr = csvStrHDR & "," & menuItem("value") & "," & menuItem("onclick")
                    Print #IFile, csvStr
                Next
            End If
        End If
    Next
    
    'Process Completed
    Close #IFile
    MsgBox "Process Completed"
End Sub

We are going to use the ParseJson module that is present in the above .Bas file. The parsed data will be moved to a dictionary data type variable.

To enable this dictionary data type option, we need the Microsoft Scripting Runtime option added in references.

This JsonConverter.bas is a miracle module that works like a charm and free to use for personal use. Please read the license details & sample code available in that page to know more about it.

But, if you are looking to parse any simple JSON file, then this code will work just fine.

JSON Sample file

{"menu": {
  "id": "officetricks",
  "value": "Blog",
  "popup": {
    "menuitem": [
      {"value": "New", "onclick": "jsontotext"},
      {"value": "Open", "onclick": "jsontocsv"},
      {"value": "Close", "onclick": "jsontoxls"}
    ]
  }
}}

If you don’t have a JSON file, use this sample file for testing the above code.

If you are very much new to this file format, get few technical aspects of it from this page. This file formatting is gaining more popular. In websites, in older days xml file was more widely used. But nowadays JSON is getting used more than any CSV, XML file formats.

Rather than using Excel to parse a JSON file, it is widely used in programming languages like PHP & Python. Because these are the programming languages that widely support Web technologies.

This code can handle nested objects with JSON as well.