Parsing JSON Strings
JSON string is a common return format from REST web services. However, working with JSON is difficult in Excel as Excel does not provide any function to parse JSON data. AlchemyJ provides an extended function, ajJSONToTable, which can turn data in a JSON string into a range of cells in Excel.
Here is an example of converting a customer list from a JSON string to an range of cells.
- Below is the JSON string we will use in this example.
{"customers":[{"Name":"CHAN TAI MAN","Date of Birth":28127,"Gender":"M","Customer Level":"5","Telephone":"22205555"},{"Name":"JERRY SZE","Date of Birth":32239,"Gender":"M","Customer Level":"2","Telephone":"22203333"},{"Name":"MERRY SO","Date of Birth":32205,"Gender":"F","Customer Level":"4","Telephone":"22201111"},{"Name":"MICKY MAI","Date of Birth":32271,"Gender":"F","Customer Level":"3","Telephone":"22206666"},{"Name":"TOM HUI","Date of Birth":28827,"Gender":"M","Customer Level":"1","Telephone":"22204444"}]}
Define the columns you wish to get from the JSON string. Please note that the column name should exactly match the JSON node name (case sensitive). In this example, we will extract Name, Date of Birth, Gender, Customer Level and Telephone from the JSON string. They are listed in Cell A4 to E4.
There are four parameters for ajJSONToTable and the first two parameters are mandatory. The first parameter is a list of nodes to be extracted. Therefore, we will input A4 to E5. The second parameter is the JSON string value. Therefore, we put A1 so that it points to the JSON string stored in cell A1. You can refer to ajJSONToTable for the details of this function.
Select cells A5 to E8 and input the formula =ajJSONToTable(A4:E4,A1) and press CTRL+ SHIFT+ ENTER. This will make the formula become an Array Formula so that the data to be retrieved can be placed into the selected cell range.
The values will be extracted into the selected range as shown below.
