Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
2  
Pardon my ignorance; which of the values above are the "records" and which are the "selected parameters"? –  jrc03c Jul 8 '10 at 20:42
    
A record would be { 'a': 10, 'b': 20 } and the parameters would be 'a' and 'b'. –  Ricardo Marimón Jul 8 '10 at 21:15
2  
This may get a better response on stackoverflow.com –  Daisetsu Jul 8 '10 at 23:33

3 Answers 3

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 
share|improve this answer

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

share|improve this answer
  1. Paste the JSON into json-csv.com.

  2. Download the resultant CSV file.

  3. Open the CSV file up in Excel.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.