I am really tired of searching a lot on google about, generating JSON records from excel. I found a way to generate JSON format records in excel without VBA programming.Simple technique to convert Excel to JSON format
I am a constant explorer of various new plugins of WordPress. In this process, I came across a plugin that export and imports the product list in JSON format. I thought it easy to handle JSON however it’s not easy. There are many online tools that convert JSON to excel but there are not many sources available converting excel to JSON when there are arrays inside an object or object inside an object
What is JSON
JSON is a lightweight and compact file format used by programmers to pass data between the server and the client. (I mean the hosting platform to the browser), along with that, it has got other advantages due to key-value structure which is generally termed as a dictionary that resembles the syntax of the object in programming.
I found 2 cases of JSON files from a non-programmer’s point of view, one that has only one object and the other having objects inside the object.
Example of JSON format with simple object repeating multiple times.
[
{
"Name":"Ram",
"Age":"15",
"Student Id":"1001"
},
{
"Name":"Raghav",
"Age":"16",
"Student Id":"1002"
},
{
"Name":"Rajaram",
"Age":"18",
"Student Id":"1003"
}
]
It is clear from the record that it is a simple list of Name, Age and Student Id.
[
{"Name":"Ram","Age":"15","Student Id":"1001"}, // JSON Object
{"Name":"Raghav","Age":"16","Student Id":"1002"}, // JSON Object
{"Name":"Rajaram","Age":"18","Student Id":"1003"} // JSON Object
]
Example of JSON format with the object inside the object and repeating multiple times
[
{
"Name":"Ram",
"Age":"15",
"Student Id":"1001",
"Sports":{
"Cricket":"true",
"Football":"false",
"Hockey":"true"
}
},
{
"Name":"Raghav",
"Age":"16",
"Student Id":"1002",
"Sports":{
"Cricket":"false",
"Football":"true",
"Hockey":"false"
}
},
{
"Name":"Rajaram",
"Age":"18",
"Student Id":"1003",
"Sports":{
"Cricket":"true",
"Football":"false",
"Hockey":"true"
}
}
]
The above JSON file has 3 records with the object inside the object. To be specific the key with “Sports” has another object which gives the true and false of different games.
[
{"Name":"Ram","Age":"15","Student Id":"1001","Sports":{"Cricket":"true","Football":"false","Hockey":"true"}}, /Record 1
{"Name":"Raghav","Age":"16","Student Id":"1002","Sports":{"Cricket":"false","Football":"true","Hockey":"false"}}, /Record2
{"Name":"Rajaram","Age":"18","Student Id":"1003","Sports":{"Cricket":"true","Football":"false","Hockey":"true"}} /Records3
]
Suppose we want to add records then it is a complex task to add the data.
I googled it for many days but couldn’t find a generic solution for every problem. I found some VBA code for generating JSON format files here (Please do check it out if interested) Then I thought why can’t I try for a solution in EXCEL? As Excel is the best tool for handling data.
First, I thought of writing a macro that will read data from the excel cell and write it on a text file in JSON format . I was able to do it but it was a difficult task.
Generating JSON with Excel Fromula
Then I thought why can’t I generate the records using the excel formula
For generating the JSON records the first requirement which I felt is that the key and value both should be in double quotes.Here key means the field name and value is the data.
{“Name”: “Ram”, “Age”: “15”, “Student Id”: “1001”}
Example : Name , Age and Student Id are Key’s and Ram ,15 and 1001 are Value’s
This can be achieved either by adding double quotes manually only as adding double quotes using a concatenate formula will not work. However, without these, we cant proceed. Thanks to ASCII code values which will do the job perfectly using the Excel formula CHAR function
The list of ASCII codes that are useful in creating the JSON records are
Important Characters for creating JSON
| CHAR(123) = | { |
| CHAR(125) = | } |
| CHAR(91) = | [ |
| CHAR(93) = | ] |
| CHAR(44) = | , |
| CHAR(58) = | : |
| CHAR(34) = | “ |
I added the double quotes manually and then wrote the formula to avoid adding another ASCII character to the formula to generate this. To understand it better have a look at the downloadable excel file after reading the post completely .
Example of Simple JSON
Example of JSON format with simple object repeating multiple times.
{ "Name":"Rajaram",
"Age":"18",
"Student Id":"1003",
"Sports":{ "Cricket":"true", "Football":"false", "Hockey":"true" }
}
Formula of Simple JSON
The above is for generating a simple JSON object
I have even tried to generate a JSON record that has a list inside the object like this
Example of Complex JSON
From this
Formula of Complex JSON
I agree that it is a bit confusing while writing the formula and that too for the first time. If you have written the formula for one record, the rest is dragging the formula.
Downloadable Files
Be careful to avoid adding the comma at the end of the last record. You can check the generated for missing characters using JSON validators online here. Be careful with the line breaks as JSON won’t accept line breaks and use “\r\n”.
The excel workbook where I did this experimentation is THIS.
I know that this is a little annoying while doing, however, all the things will be under our control, without depending on any program or any external tool to generate JSON with a complex structure.
I hope this would help a lot of people who are good at excel and want to work with JSON files.