Import json data into Excel

Question

I have a text file in json format and want to read it into Excel. A very simplified example of the json file has the following structure:

{ [
  { 'a': 10, 'b': 20 },
  { 'a': 20, 'b': 22 },
  { 'a': 11, 'b': 24 }
] }

I want to convert it to Excel in which each record becomes a row in excel with the selected parameters as the column headers.

Solution 1

You can do this in VBA with the vba-json library. Here's an example of some code I copied:

Sub TestJsonDecode() 'This works, uses vba-json library 
    Dim lib As New JSONLib 'Instantiate JSON class object 
    Dim jsonParsedObj As Object 'Not needed 

    jsonString = "{'key1':'val1','key2':'val2'}" 
    Set jsonParsedObj = lib.parse(CStr(jsonString)) 

    For Each keyName In jsonParsedObj.keys 
        MsgBox "Keyname=" & keyName & "//Value=" & jsonParsedObj(keyName) 
    Next 

    Set jsonParsedObj = Nothing 
    Set lib = Nothing 
End Sub 

Sub TestJsonEncode() 'This works, uses vba-json library 
    Dim lib As New JSONLib 'Instantiate JSON class object 
    Set arr = CreateObject("Scripting.Dictionary") 

    arr("key1") = "val1" 
    arr("key2") = "val2" 

    MsgBox lib.toString(arr) 
End Sub 

Solution 2

1. Paste the JSON into json-csv.com.

2. Download the resultant CSV file.

3. Open the CSV file up in Excel.

Solution 3

Here are some example excel downloads with json excel connversion capabilities. You can do it straight from within excel.

http://ramblings.mcpher.com/Home/excelquirks/json

Comments

[Admin]

Pardon my ignorance; which of the values above are the "records" and which are the "selected parameters"?

[Admin]

A record would be { 'a': 10, 'b': 20 } and the parameters would be 'a' and 'b'.

[Admin]

This may get a better response on stackoverflow.com

[Peter Turner]

Not a great answer any more without google code being around to download this