How to convert Excel to JSON?
The users have different requirements, e.g., convert the excel data to JSON format. It needs to do the mapping of Excel worksheet columns to object keys. An Excel to JSON converter helps to convert the .xls file data to .JSON file format.
These Excel files can contain text, charts, graphs, formatting, functions, and more data.
There is no predefined method in Excel to convert the Excel data to JSON. You can either use online Excel to JSON conversion software or download an add-in from the Microsoft store for this task to get done.
Why Excel to JSON?
Excel files are heavy weighted files that may contain text, charts, graphs, formatting, functions, and more data.
While the JSON files are light-weighted text-based documents. It stores the data in a simple data structure. Hence, JSON files can be opened on any text software like Notepad or Wordpad.
There are several tools available to convert the Excel data to JSON.
- Altova MapForce
- White Town XLS to JSON Converter
- Oxygen JSON Editor
- WTSolution Excel to JSON Converter
Here, we will brief about these tools with their download links.
Tip: WTSolution Excel to JSON Converter is also known as Excel to JSON add-in. Microsoft offers this tool.
For the last tool – Excel to JSON, we will also show you the steps to download and use this software.
Altova MapForce
Altova MapForce is a data mapping and conversion tool as well. It is a useful software to convert various sets of data. Using this tool, you can convert the Excel data to JSON, XML to JSON, and vice versa.
IMAGE
Additionally, this tool also allows the users to convert the JSON to CSV, EDI, Google protocol buffer, and more. This tool is easy to use as it provides a drag and drop interface. It also supports advanced data processing.
Altova MapForce is not free software but comes with 30 days of a free trial. Download the Altova MapForce tool from the following link.
White Town xls to JSON converter
This software is an xls to JSON converter. As the name implies, it allows the users to convert the Excel data into JSON format. However, it only allows the Excel data to be changed into JSON format. This software supports both formats (.xls and .xlsx formats) of Excel.
IMAGE
One of the biggest advantages of using this software, it is free of cost. Although you can also buy the personal or business license of it. It is very easy to use that is why it is very popular, mostly for those who transfer their data online.
You can download the White Town xls to JSON converter tool from the following link.
Oxygen JSON Editor
Oxygen JSON Editor is software used to convert the Excel file data to JSON format by mapping the Excel columns to JSON object key. This software is complex software than the other ones we have described above.
As Oxygen JSON Editor is a complex software, it has complex features too. Complex features, such as – structure validation, indent, format, and content completion. Additionally, this software also allows us to convert the XML document to JSON format.
IMAGE
Oxygen JSON Editor works differently than the other Excel to JSON converters. It uses XSLT stylesheet template for the conversion. Most professionals prefer this software to convert their data. It is a paid software whose personal, business, and academic versions are available on its official website.
Check out its official site to download the Oxygen JSON Editor.
WTSolution Excel to JSON converter
This one is the last Excel to JSON converter of this chapter by using which one can perform Excel to JSON conversion on their data. It is a simple application for this task to perform. This software supports two types of conversions on Excel data: Row and Nested.
You have also listened its name as Excel to JSON add-in. Microsoft offers this add-in named Excel to JSON that helps the users to convert their Excel data into JSON format. Now, let’s know something about Excel to JSON add-in and also see the downloading steps for it.
Excel to JSON add-in
Excel to JSON is an add-in offered by Microsoft to convert the Excel sheet data to JSON format. When the data is converted to the JSON format, Excel sheet columns are changed to object keys in JSON. It costs no charge from the user to download and use it.
You can download this add-in from the Office store.
Requirement
Excel to JSON add-in will work only on Excel 2013 or higher versions. Hence, your Excel version must be 2013 or higher.
Limitation
This add-in has a limitation.
It can convert the selected Excel data to JSON format.
Currently, two types of conversions are supported by this add-in.
- Row
- Nested
Row conversion
In row type conversion, the first row is treated as header, and rest of the rows are interpreted as data.
Nested conversion
In nested type conversion, you can define a JSON schema. This add-in will do the conversion accordingly, the schema you have provided.
Download add-in
Excel to JSON add-in is available in Office store; you can download it from there. For step by steps learning, follow this chapter below –
Step 1: Download the Excel to JSON add-in from the following link.
Step 2: A webpage of the Office store will open, where click on the Get it now button.
Step 3: Before start downloading, it will ask you to log in to your Microsoft account with valid credentials.
If you do not have a Microsoft account, signup for free to create new account.
Step 4: When you login to your Microsoft account. It will ask for some basic information required by this app before download. Here, provide these details and click the Continue button.
Tip: The fields that are marked with * sign are required fields and others are optional.
Step 5: Now, click the Open in Excel button here.
If you don’t have any MS Excel 2013 or above version downloaded in your system, you can go for office online. By clicking on this link.
We have clicked on the Open in Excel.
Step 6: On this popup, leave the checkbox unmarked and click on Open Excel here.
Step 7: Your Excel will be opened on your system, but it requires an internet connection. If it is not available, you may get this type of warning error.
Step 8: When you click on OK, it will show you one more panel that shows you some reason why this error can occur. This error can be because of the following reasons.
So, connect your system with the internet and close the currently opened Excel workbook.
Step 9: Follow Step 5 and step 6 again, where click on the Open the Excel button.
This time your system is connected to the internet. So, it will not show any error and interruption while installing the add-in.
Step 10: An interface on the Excel file will open like this with Excel to JSON add-in. Now, add this add-in to your Excel tab by clicking on Trust this add-in button present on the right panel.
Step 11: Excel to JSON add-in is successfully added to the Excel ribbon as a tab in the end. See here –
Step 12: Your Excel to JSON add-in is ready to launch. It is available inside the Excel-to-JSON tab by the name Launch Excel-to-JSON button.
Note: The Excel file, which is currently opened, is read-only that contains a few instructions about this tool. Hence, open the targeted file first, which you want to convert Excel to JSON.
Step 13: Prepare a new Excel sheet or open that one whose data you want to change in format. We have opened an existing file of person details and click on the Launch Excel-to-JSON button.
Step 14: Once you click on it, you may get this error. Ignore it and click OK to move forward.
Tip: While using this add-in to convert your data, your system must be connected to the internet. Otherwise, you may face problems.
Step 15: Now, select at least first two row’s data or all where first one is considered as header and the other as data. Then click on GO in the right panel.
Step 16: You will see that the selected data is converted into JSON format and available below the Go button.
Step 17: Copy this data from here, paste this JSON data into a file and save it. Hence, click on the Copy to clipboard button here.
Step 18: JSON data is successfully copied to the clipboard.
Step 19: Now, paste this copied clipboard data to a Word file and save it. The copied data will look like as shown below –
Excel to JSON using VBA code editor
Besides using all these tools, you can use the in-built feature of Excel (VBA code editor) to convert the Excel data to JSON format. Make a code for it and execute the code; it will do the mapping of Excel columns to JSON object keys and convert the data into JSON.
If you are comfortable in doing coding, then it’s a good way for you. Thus, you do not need to explicitly download the above discussed tools to convert your Excel data to JSON format. While you can do it without downloading any software on your system using Excel VBA code Editor.
Tip: Your Excel data must have column names because the first row is always considered as a header.
VBA Code
Following is the complete code for the conversion of the Excel file data to JSON format. Copy this following code and paste this to your respective VBA code editor.
In this VBA code, we have defined a function named ExcelToJSON that will later be used in an Excel file for converting Excel data to JSON format. Let’s see how it will execute.
Syntax
Now, go back to the Excel file and write the following ROUND() formula in an Excel cell where you want to place the converted JSON result. For example,
=ExcelToJSON(A1:F3)
Currently, we are converting the first two Excel rows data into JSON format only.
Press the Enter key and get the result and see that the data is successfully converted into JSON form. Similarly, you can use this function for more conversion.
For the detailed description of this code, see our next tutorial, where we have explained this code and variables created and used in it.
